IN vs ANY in PostgreSQL: which is faster and why?

Neither is faster. For a literal value list, PostgreSQL defines IN (a, b, c) as shorthand for a chain of = comparisons, and the parser rewrites it into the array operation = ANY uses — EXPLAIN prints an identical node for both. What matters is a value list versus an array parameter, and NOT IN versus NOT EXISTS.

Key facts

| Fact | Value | |---|---| | IN (value list) definition | "a shorthand notation for expression = value1 OR expression = value2 OR ..." (docs §9.25.1) | | IN (subquery) definition | "IN is equivalent to = ANY" — a subquery expression, not a value list (docs §9.24.4) | | NOT IN with a null on the right | Result is null, "not true as one might naively expect" (docs §9.25.2) | | Max bind parameters in one statement | 65535 — the wire protocol counts them in an Int16 (protocol message formats) | | Prepared-statement plan reuse | First five executions use custom plans, then a generic plan is costed against their average (PREPARE) | | pg_stat_statements list squashing | Constant lists collapse to IN ($1 /, ... /), one entry regardless of length (pg_stat_statements) |

Why this happens

Start with the claim you have probably read: "= ANY is faster than IN." For a literal value list that is simply false, and the plans prove it. Against a 2,000,000-row orders table on PostgreSQL 18.4:

EXPLAIN SELECT * FROM orders WHERE customer_id IN (17, 42, 99);
EXPLAIN SELECT * FROM orders WHERE customer_id = ANY (ARRAY[17, 42, 99]);

 Index Scan using orders_customer_id_idx on orders  (cost=0.43..139.71 rows=121 width=16)
   Index Cond: (customer_id = ANY ('{17,42,99}'::integer[]))

 Index Scan using orders_customer_id_idx on orders  (cost=0.43..139.71 rows=121 width=16)
   Index Cond: (customer_id = ANY ('{17,42,99}'::integer[]))

Same node, same index condition, same cost to two decimal places. Note what EXPLAIN printed for the IN query: = ANY ('{17,42,99}'::integer[]). The array form is not an alternative the planner considered — it is what IN becomes before the planner ever sees it. IN is documented as shorthand for a disjunction of equalities (§9.25.1), and the parser folds a list of constants on one side of = into a single array operation.

So the myth is wrong in that direction. It is also wrong in the other direction — "they are the same, stop worrying about it" — because two genuinely different things get filed under the same syntax.

A value list is not a subquery. IN (SELECT ...) is a subquery expression from §9.24, where the docs state "IN is equivalent to = ANY". That form is a semi-join: the planner needs each outer row at most once, no matter how many subquery rows match. It picks a strategy accordingly:

EXPLAIN (COSTS OFF)
SELECT count(*) FROM customers c
WHERE c.customer_id IN (SELECT o.customer_id FROM orders o WHERE o.order_total_amount > 900);

 Finalize Aggregate
   ->  Gather
         Workers Planned: 1
         ->  Partial Aggregate
               ->  Parallel Hash Semi Join
                     Hash Cond: (c.customer_id = o.customer_id)
                     ->  Parallel Index Only Scan using customers_pkey on customers c
                     ->  Parallel Hash
                           ->  Parallel Seq Scan on orders o

Parallel Hash Semi Join, not a filter and not a subplan. Whether you get a hash semi-join, a nested loop, or a merge semi-join depends on estimated row counts, which is a statistics question — see how the planner uses table statistics and join optimisation.

A literal list is not an array parameter. This is where a real, measurable difference lives, and it is not about the operator at all. It is about how many distinct statements your application sends.

WHERE id IN ($1, $2, $3) and WHERE id IN ($1, $2, $3, $4) are two different statement texts. A server-side prepared statement is cached per text, so every distinct list length is a separate parse, a separate plan, and a separate cache entry. WHERE id = ANY($1::int[]) has one text forever. One plan, one cache entry, whether the array holds 3 ids or 3,000.

Two consequences follow. The first is the plan cache. PREPARE documents that "the first five executions are done with custom plans and the average estimated cost of those plans is calculated", after which a generic plan is costed against that average (docs). A list-expanding ORM restarts that five-execution counter for every new list length, so a workload with variable-length lists can spend its life replanning and never settle. The array form reaches the generic plan once.

The second is a hard ceiling. The v3 protocol's Bind message counts parameter values in an Int16 (protocol message formats), so a single statement cannot carry more than 65535 bind parameters. An expanded list of 70,000 ids fails at the protocol layer before PostgreSQL can even plan it. One array parameter carries the same 70,000 values as one value.

How to detect it

The most valuable check in this article is not about speed. It is about correctness.

-- returns are nullable; 200 of 200,000 rows have a NULL order_id
SELECT count(*) FROM orders o WHERE o.order_id NOT IN (SELECT r.order_id FROM returns r);
--  0

SELECT count(*) FROM orders o WHERE NOT EXISTS (SELECT 1 FROM returns r WHERE r.order_id = o.order_id);
--  1800200

Those two queries are widely believed to be equivalent. They are not, and the first one is silently, catastrophically wrong: a single NULL anywhere in the subquery makes NOT IN return no rows at all. The documentation states it plainly — with a null on the right and no equal value, "the result of the NOT IN construct will be null, not true as one might naively expect" (§9.25.2). A row whose predicate evaluates to null is not returned.

The performance cost is the second half of the same trap. Because the planner must preserve that three-valued logic, it cannot convert NOT IN into an anti-join:

-- NOT IN
 Parallel Seq Scan on orders o (actual rows=0.00 loops=4)
   Filter: (NOT (ANY (order_id = (hashed SubPlan 1).col1)))
   Rows Removed by Filter: 500000
   SubPlan 1
     ->  Seq Scan on returns r (actual rows=200000.00 loops=4)
 Execution Time: 148.236 ms

-- NOT EXISTS
 Merge Anti Join (actual rows=300033.33 loops=6)
   Merge Cond: (o.order_id = r.order_id)
   ->  Parallel Index Only Scan using orders_pkey on orders o
   ->  Index Only Scan using returns_order_id_idx on returns r
 Execution Time: 54.554 ms

NOT IN gets a hashed SubPlan evaluated as a filter over every scanned row. NOT EXISTS gets a Merge Anti Join — a join node the planner can cost, parallelise and feed from indexes. Here that is 148 ms against 54 ms, and the faster query is also the only correct one.

Reading those two plans side by side is the whole skill; the EXPLAIN reading guide covers the node types, and NOT IN over a nullable column earns its place on any list of Postgres query anti-patterns.

You need the real plan to settle any of this, and most instances cannot produce one on demand. Across 50 monitored instances in our fleet, 86% fail the auto_explain plan-capture check — meaning when a query misbehaves in production there is no captured plan to look at, only a guess.

How MyDBA shows this

The Queries page groups by normalised query template, the same unit pg_stat_statements uses, and ranks those templates by calls, mean time and total time. That grouping is what makes the list-expansion pattern visible: an application that inlines IN ($1, $2, $3) produces a different statement text for every list length, so it arrives as a column of near-identical entries rather than one row with a meaningful call count — and none of them individually looks expensive enough to investigate. PostgreSQL 18 softened this for statistics by squashing constant lists to IN ($1 /, ... /) (pg_stat_statements), which we see in our own production instance, but squashing only merges the reporting; the backend still parses and plans each length separately. If the near-duplicates in your own list share a shape and differ only in parameter count, that is the symptom. Run a free PostgreSQL health check to see which of your query templates are fragmented this way.

How to fix it

1. Replace every NOT IN (subquery) with NOT EXISTS unless you can prove the subquery column is NOT NULL at the schema level. This is a correctness fix first and a performance fix second. NOT EXISTS has no null-driven surprise and the planner can use an anti-join. 2. Send one array parameter, not N placeholders. WHERE id = ANY($1::int[]) in place of a generated IN (...). Most drivers map a native list to a PostgreSQL array directly; where the ORM insists on expanding, most also allow a raw fragment for that one predicate. 3. Leave IN (a, b, c) alone where it is already a literal list of constants. Rewriting it to = ANY (ARRAY[...]) changes nothing — the plans above are byte-identical. Spend the effort elsewhere. 4. Prefer a VALUES list or a temporary table for very large sets. = ANY on a big array is evaluated per outer row against the whole array; a VALUES list or a temp table is a relation the planner can scan, hash, sort and get statistics for. JOIN (VALUES (17),(42),(99)) AS v(id) ON o.customer_id = v.id plans as a Values Scan feeding an index scan. For tens of thousands of ids, an ANALYZEd temporary table usually wins because the planner finally has a row estimate for the set. 5. Remember = ANY takes any array expression, not just a literal — = ANY (SELECT array_agg(customer_id) ...) from a CTE or unnest() output works, and keeps the predicate as an Index Cond. See subquery and CTE optimisation for when materialising the set first is the better shape.

How to prevent it

Turn on plan capture before you need it. auto_explain with a log_min_duration threshold is the difference between reading the plan your slow query actually got and reconstructing it from memory; 86% of the instances we monitor do not have it. Add a lint or code-review rule for NOT IN (SELECT, which is cheap to grep for and almost always wrong. Watch the Queries page for families of near-identical templates — that is list expansion showing itself. And if your driver expands lists inline, set a guard on maximum list length well below 65535 rather than discovering the protocol limit in production.

FAQ

Is = ANY faster than IN in PostgreSQL?

Not for a literal value list. The parser turns IN (a, b, c) into the same array operation = ANY (ARRAY[a, b, c]) produces, and EXPLAIN prints the identical Index Cond and cost for both. The rewrite is worth doing only when it lets you send one array parameter instead of N placeholders.

Why does my NOT IN query return zero rows?

Almost certainly a NULL in the subquery. PostgreSQL defines NOT IN so that a null on the right-hand side with no equal value yields null rather than true (§9.25.2), and a null predicate filters the row out. One NULL among a million values empties the whole result. Use NOT EXISTS.

Does IN with a subquery behave differently from IN with a list?

Yes — they are different constructs that share a keyword. A list is a value comparison (§9.25.1); a subquery is a semi-join (§9.24.4) that the planner may execute as a hash, merge or nested-loop semi-join depending on its row estimates.

How many values can I safely put in an IN list?

If the values are bind parameters, fewer than 65535 — the protocol's Bind message counts parameters in an Int16 (protocol message formats). Long before that limit, plan-cache churn from variable list lengths becomes the bigger cost. Past a few thousand values, a VALUES list or an analysed temporary table plans better than either form.

Does PostgreSQL 18's pg_stat_statements squashing fix the problem?

It fixes the reporting symptom only. Constant lists collapse to IN ($1 /, ... /) so one entry covers every length (docs), which makes the workload legible again. The backend still parses and plans each distinct statement text separately, so the plan-cache cost is unchanged.

Part of the Reading PostgreSQL EXPLAIN and EXPLAIN ANALYZE Output guide. Last verified against PostgreSQL 18, 2026-09-17.