Differences between = ANY and IN?

Maciek Sakrejda <m.sakrejda@gmail.com> writes:

My colleague's patch changes SQL generated from Ruby expressions like
`where(id: [1, 2])` . This is currently translated to roughly `WHERE
id IN (1, 2)` and would be changed to `id = ANY('{1,2}')`.

As far as we know, the expressions are equivalent, but we wanted to
double-check: are there any edge cases to consider here (other than
the pg_stat_statements behavior, of course)?

You would find it profitable to read transformAExprIn() in parse_expr.c.
The most important points are in this comment:

* We try to generate a ScalarArrayOpExpr from IN/NOT IN, but this is only
* possible if there is a suitable array type available. If not, we fall
* back to a boolean condition tree with multiple copies of the lefthand
* expression. Also, any IN-list items that contain Vars are handled as
* separate boolean conditions, because that gives the planner more scope
* for optimization on such clauses.

If all the values in the IN form were being sent to the backend as
constants of the same datatype, I think you're okay to consider it
as exactly equivalent to =ANY. It would likely be a good idea to
provide an explicit cast `id = ANY('{1,2}'::int[])` rather than just
hoping an unadorned literal will be taken as the type you want
(see transformAExprOpAny and thence make_scalar_array_op).

regards, tom lane

Great, thanks for the guidance!