[PSA] Ecto.Query JSON-access API cannot use PostgreSQL GIN indices

Chat & Discussions Discussions
,
1

If you have table like:

create table(:foos) do
  add :data, :json
end

create index(:foos, [:data], using: :gin])

Then queries that use json_extract_path/2, or equivalent Access-like syntax, will not utilise that index. In other words:

from foo in Foo,
  where: foo.data["bar"] == "yay"

Will require full table scan, and can kill performance of your queries.

There are 2 solutions to this problem:

  • If you know the subset of queried fields beforehand, then you can create functional index on these fields, for example create index(:foos, ["(data#>'{bar}'"])
  • If you do not know the subset of fields and want to run queries on arbitrary path, then instead you should use fragment/1 with @> operator, so in this case it would be from foo in Foo, where: fragment("? @> ?", foo.data, ~S({"bar": "yay"}})). Beware that this have different behaviour when working with arrays.

https://github.com/elixir-ecto/ecto/pull/3698

https://github.com/elixir-ecto/ecto_sql/issues/330

7 Likes