Switch to using = ANY(SELECT unnest(?)) instead of = ANY(?) for IN operator

[michalmuskala]

The problem of in (?, ?, ...) is that it requires different SQL for different number of elements and in effect prevents caching queries. Because of that we switched to = ANY(?), but that prevents using indices for the comparison.

@talentdeficit suggested using = ANY(SELECT unnest(?)) instead, which supports using indices. The downside is that this relies on support of unnest function. To my knowledge the databases that don't support it (redshift) also don't support use of = ANY(?), so it shouldn't be removing any feature.

[josevalim]

Can we please do actual tests on this? Let's run some explain commands, benchmark on large data and what not. Otherwise, I don't think we should move forward.

[josevalim]

I will close this for now, unless somebody is interested in running some tests and sharing their results. :)

[MateusAquino]

@josevalim I've done some testing similar to this issue but since the table I'm testing is a foreign table w/ > 6M rows accessed by Mongo FDW, it might be a pushdown thing instead of an indexing one:

= ANY('{1212}')                     -- ~22008ms
= ANY(SELECT unnest(array['1212'])) -- ~22727ms
= IN ('1212')                       --  ~1126ms

Just wondering, currently there is no way of using IN instead of ANY in ecto without having to write infinite functions like the one below, right?

...

def where_in(query, field, [val1, val2, val3, val4]) do
  where(query, [q], fragment("? in (?, ?, ?, ?)", field(q, ^field), ^val1, ^val2, ^val3, ^val4))
end

...

I couldn't figure out another way of doing this other than changing the inner behaviour of Ecto to use in instead of any, neither could use raw (non escaped) generated fragment since fragments are compiled and literal/1 macro uses double quotes... the only workaround would be to create infinitely many where_in functions or generating a Raw SQL without Ecto