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])) …

[image] cd-slash: Is there a function for that? Ecto.Adapters.SQL — Ecto SQL v3.14.0 [image] cd-slash: Secondly, since the indexes are clearly being built and then used when I execute the query elsewhere, why would Ecto not be using them? Ecto has…

Ecto.Adapters.SQL — Ecto SQL v3.12.0 Thanks, I’d tried that but couldn’t get it working for SQLite, seemed to only be for Postgres / MySQL? Ecto has no influence on the db using an index or not. You’d want to make sure the queries are indeed the same (including using parameters and prepared que…

[image] cd-slash: Thanks, I’d tried that but couldn’t get it working for SQLite, seemed to only be for Postgres / MySQL? Use the Repo.explain variant as documented. This exists for the sqlite driver as well.

Thanks, I was actually able to get it working with the code below: GADMRepo.explain( :all, 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…

To test the indexes, I created some compound indexes with both of the columns that I might be using as below and got some unexpected results. defmodule GADM.Migrations.AddIndexes do use Ecto.Migration def up do create(index(:gadm_410, [:gid_0])) create(index(:gadm_410, [:gid_0, :gid_1]…

:wave: @cd-slash Just a guess: gid_4 might be picked because your query uses the equality operator with it, and on gid_3 it uses != which probably gets ignored. Possible workarounds: running ANALYZE (probably won’t work) compiling with SQLITE_ENABLE_STAT4 (and running ANALYZE) you can use Built-…

Like @ruslandoga said, the != operator is likely the root of your struggles. Some things to consider trying: reverse the order in the multicolumn indexes - so [:gid_4, :gid_3] instead of [:gid_3, :gid_4]. That way the initial columns in the index are being matched with =. create an index on e…

Is everything exactly the same when you query outside of Ecto? For example, the table has the same data inside of it? If the query text is 100% the same in both places the only way they can choose different plans is if the estimated amount of work is different. The most obvious ways this can happen…

It seems like this was accurate - swapping the order of the == and != yielded much better performance. I was able to further improve it by chunking on country which is also indexed, and have got it running ~500x faster at this point, which is good enough for an infrequent task. Thanks all - appreci…

Ecto not using indexes in SQLite causing slow query

Questions & Help Questions

LostKobrakai October 5, 2024, 12:32pm 4

Use the Repo.explain variant as documented. This exists for the sqlite driver as well.