I have a relatively large table (~350k rows and ~50 columns) in an existing GADM database that I’m connecting to with Ecto.

I’ve created indexes on the table with the migration below:

defmodule GADM.Migrations.AddIndexes do
  use Ecto.Migration

  def up do
    create(index(:gadm_410, [:gid_0]))
    create(index(:gadm_410, [:gid_1]))
    create(index(:gadm_410, [:gid_2]))
    create(index(:gadm_410, [:gid_3]))
    create(index(:gadm_410, [:gid_4]))
    create(index(:gadm_410, [:gid_5]))
    create(index(:gadm_410, [:country]))
  end

  def down do
    drop(index(:gadm_410, [:gid_0]))
    drop(index(:gadm_410, [:gid_1]))
    drop(index(:gadm_410, [:gid_2]))
    drop(index(:gadm_410, [:gid_3]))
    drop(index(:gadm_410, [:gid_4]))
    drop(index(:gadm_410, [:gid_5]))
    drop(index(:gadm_410, [:country]))
  end
end

Ecto.Migrator.down(GADMRepo, 0, GADM.Migrations.AddIndexes)
Ecto.Migrator.up(GADMRepo, 0, GADM.Migrations.AddIndexes)

I’m then making a relatively simple query using Ecto:

import Ecto.Query

id_field_name = String.to_atom("gid_3")
child_id_field_name = String.to_atom("gid_4")
name_field_name = String.to_atom("name_3")

GADM.Region
|> where([r], field(r, ^id_field_name) != "")
|> where([r], field(r, ^child_id_field_name) == "")
|> select([r], %{
  gid: field(r, ^id_field_name),
  name: field(r, ^name_field_name)
})
|> distinct(true)
|> GADMRepo.all()

This works, but is very slow:

10:26:39.908 [debug] QUERY OK source="gadm_410" db=2820.3ms queue=0.1ms idle=1446.3ms
SELECT DISTINCT g0."gid_3", g0."name_3" FROM "gadm_410" AS g0 WHERE (g0."gid_3" != '') AND (g0."gid_4" = '') []
↳ :elixir.eval_external_handler/3, at: src/elixir.erl:386

In contrast, when I paste exactly the same SQL as the output shows into SQLIte Studio, it executes in ~1ms, which is what I’d have anticipated with indexes available for all of the columns I’m working with.

To diagnose, firstly I’ve tried to use explain but can’t see any straightforward way to get Ecto to explain a query like this. Is there a function for that?

Secondly, since the indexes are clearly being built and then used when I execute the query elsewhere, why would Ecto not be using them? Even without the explain I can intuit that this is what’s happening as deleting the indexes yields almost identical execution times, so it seems they’re not having any effect.