When selecting a number of records from a Postgres table based on a list of ids passed as a parameter, what are the benefits and drawbacks of
SELECT id, <more...>
FROM tbl
INNER JOIN UNNEST($1) AS id USING (id)
versus
SELECT id, <more...>
FROM tbl
WHERE id = ANY($1)
and in which cases one should be preferred to the other?
DB: Postgres 14 and above.
Note: This is not a duplicate of Getting by list of ids: `unnest() JOIN` vs `= ANY()` since the list is not constant.
The important thing is that both are not equivalent.
... JOIN UNNEST($1) AS id USING (id) preserves any duplicates in the array and returns duplicate result rows accordingly.
... WHERE id = ANY($1) effectively folds duplicates on the right side (in the array).
(Both keep duplicates on the left side if any.)
Different query plans can result from this different functionality.
Different sort orders (unless sorted explicitly) can result from these different query plans.
Null values are handled the same, though - which gets more tricky with NOT IN or <> ALL () ... See:
You may know that the array contains no duplicates (in which case the main difference is nullified), but Postgres does not know that. unnest() tends to be faster with big arrays, for that reason.
