Postgres IN vs ANY

Imagine you have a list of ids (2, 3, 5, 8, 13) and you want to fetch all database entries for these ids. I immediately come up with the following query in my head:

SELECT * FROM table WHERE id IN (2, 3, 5, 8, 13)

This works fine most of the time. However, the In operator does not support passing an array of ids directly as a parameter. That’s why the query will look like this (this might be generated by an ORM without you knowing):

SELECT * FROM table WHERE id IN ($1, $2, $3, $4, $5)

So for every id you will have a new parameter. This is not an issue for a small number of parameters, but imagine sending a couple thousand ids.

This is where Any makes more sense, because unlike the In operator, Any can accept an array directly.

So you get this instead:

SELECT * FROM table WHERE id = ANY($1)

Where $1 is the array of all the ids.

Execution speed

In most cases the query planner will do the same thing for In and Any. So the only advantage of Any is, that it will not cause problems with large lists.