Implementation of `in` operator for array-type columns in Postgres cannot use indexes

[Julien Smeets]

I really hope I'm not recreating a zombie thread, but here goes. The current implementation of the `in` operator on an array type column results in the following query (in postgres):

WHERE ($1 = ANY(t0."column"))

Querying for membership of an array column can be sped up in Postgres using a GIN-index: https://www.postgresql.org/docs/current/indexes-types.html.

However, such indexes only support certain operators, and ANY() is not one of them: https://www.postgresql.org/docs/current/gin-builtin-opclasses.html#GIN-BUILTIN-OPCLASSES-TABLE

But '&&' (a1 && a2 -> do the two arrays have overlapping elements?) is supported by GIN indexes, which means that the `in` operator could be made to use such an index if it is reimplemented to result in a query like:

WHERE(t0."column" && $1) [["element"]]

(with "element" wrapped in an extra list)

The functionality should be the same, as far as I can tell. I'm willing to implement it myself if somebody can point me at the place in the code in either Ecto or Postgrex where the `in` operator is implemented, I couldn't find it by myself.

Julien

[José Valim]

I think the difficulty would be wrapping the parameter in a list. Having a specific intersection operation probably makes more sense.

--
You received this message because you are subscribed to the Google Groups "elixir-ecto" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elixir-ecto...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/89d81da6-b3dc-467b-9b91-4fb88d4ef354n%40googlegroups.com.

[Julien Smeets]

Hm ok, can you elaborate a little on that? I'm sure there's a good reason why you think wrapping the parameter would be difficult, but I don't really see it. This works fine:

where: fragment("? && ?", t.column, ^[val])

[José Valim]

I believe it will be hard to do it *automatically* on top of the in operator. The syntax you presented works because you are the one doing it, from the beginning.

To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/eafdbb80-6937-4226-90f5-d04cf2ecd3fdn%40googlegroups.com.

[luk...@niemier.pl]

I think this should be sent to the PostgreSQL mailing list and should be fixed in the DB itself, as these two syntaxes are functionally equivalent. This would make improvements work for everyone.