IN Lists vs VALUES vs ANY(array) Plans #

An import job looks up 20,000 SKUs at once with WHERE sku IN ('A1', 'A2', …). Planning alone takes 400 ms, the estimate is wrong by a factor of three, and every batch produces a different statement text that fills pg_stat_statements. Passing the same keys as one array parameter, or joining to a VALUES list, gives three very different plans for the same question.

How the planner turns subqueries and lists into joins is covered in CTE and subquery planning. This page compares the three ways applications pass key sets.

The Planner Condition #

col IN (v1, v2, …) with constants is converted to col = ANY('{v1,v2,…}'), a single scalar array operator. The planner estimates its selectivity by summing per-element selectivities from statistics — accurate for short lists, increasingly expensive to compute for long ones — and plans:

col = ANY($1::text[]) with an array parameter produces one statement text for any list length. At plan time with a known value (custom plan) the planner sees the array; in a generic plan it does not, and it assumes a default array length of 10 elements, which underestimates large lists.

JOIN (VALUES (…), (…)) v(sku) ON … creates a Values Scan relation with an exact row count. The planner can then choose any join algorithm — a hash join against the table, a nested loop with index probes, or a merge join — using normal join costing. For thousands of keys, a hash or merge join often beats thousands of individual index searches.

unnest($1::text[]) in FROM behaves like VALUES with a parameter, but its row estimate comes from the function’s default ROWS of 100 for unnest unless the array is a known constant.

Three ways to pass a key set A comparison table. A constant IN list has per-element estimates, one statement text per list, and index searches per element. An ANY array parameter has one statement text for all sizes and estimates the array at 10 elements in generic plans. A VALUES join has an exact row count, one statement text per list, and can use hash, merge or nested loop joins. row estimate statement texts plan options IN (constants) per element one per list index search × n = ANY($1::text[]) 10 if generic one total index search × n JOIN (VALUES …) exact count one per list any join unnest($1) in FROM 100 default one total any join estimate quality and statement reuse pull in opposite directions

Annotated EXPLAIN Evidence #

20,000-element IN list:

Index Scan using products_sku_idx on products  (cost=0.43..88420.10 rows=61204 width=64)
                                                (actual time=0.9..204.6 rows=20000 loops=1)
  Index Cond: (sku = ANY ('{A1,A2,…}'::text[]))
Planning Time: 412.8 ms                          -- summing selectivity for 20,000 elements
Execution Time: 206.1 ms
-- rows=61204 vs 20000: per-element estimates of non-MCV values overshoot
-- planning cost exceeds execution cost

Array parameter under a generic plan:

Index Scan using products_sku_idx on products  (cost=0.43..44.61 rows=10 width=64)
                                                (actual time=0.8..198.4 rows=20000 loops=1)
  Index Cond: (sku = ANY ($1))
-- rows=10: default array length assumption; joins above this node are planned for 10 rows

VALUES join:

EXPLAIN (ANALYZE)
SELECT p.* FROM products p
JOIN (VALUES ('A1'), ('A2') /* … 20,000 rows */) v(sku) ON p.sku = v.sku;
Hash Join  (cost=500.00..212004.10 rows=20000 width=64) (actual time=4.1..1904.2 rows=20000 loops=1)
  Hash Cond: (p.sku = "*VALUES*".column1)
  ->  Seq Scan on products p  (actual rows=12004110 loops=1)
  ->  Hash  (actual rows=20000 loops=1)
        ->  Values Scan on "*VALUES*"  (actual rows=20000 loops=1)
Planning Time: 38.2 ms
Execution Time: 1911.4 ms
-- exact estimate, cheaper planning; here the hash join over a 12M-row scan loses to index searches
Where each form gets its row estimate Three plan lines are annotated. The IN list index scan estimates 61 thousand rows from summed per-element selectivity and plans for 412 milliseconds. The generic ANY plan estimates 10 rows from the default array length. The Values Scan reports exactly 20 thousand rows. Index Cond: (sku = ANY ('{…}')) rows=61204 summed per-element estimate Planning Time: 412.8 ms long constant list costs planning Index Cond: (sku = ANY ($1)) rows=10 generic plan assumes 10 elements Values Scan … rows=20000 exact count, any join algorithm

Step-by-Step Resolution #

  1. Measure planning time, not only execution time, for long constant lists: EXPLAIN (ANALYZE, SUMMARY).

  2. Switch applications to an array parameter for variable-length key sets. One statement text improves plan caching and monitoring — see variable IN lists and statement cache bloat:

    SELECT * FROM products WHERE sku = ANY ($1::text[]);

  3. Check the generic-plan estimate when the result feeds joins. If the default 10-element assumption leads to nested loops over large lists, force custom plans for that statement or restructure.

  4. Use VALUES or unnest joins for large sets feeding further joins, where an accurate row count matters more than a cached plan:

    SELECT p.*, s.stock
FROM unnest($1::text[]) AS k(sku)
JOIN products p ON p.sku = k.sku
JOIN stock s ON s.product_id = p.id;

  5. For very large sets, load a temporary table, ANALYZE it, and join: the planner then has real statistics, including distinct counts.

    CREATE TEMP TABLE import_skus (sku text PRIMARY KEY);
COPY import_skus FROM STDIN;
ANALYZE import_skus;

  6. Compare the index-search plan against a hash join for your list sizes; the crossover depends on table size and index correlation.

Total cost for 20,000 keys The constant IN list takes 619 milliseconds including 413 milliseconds of planning. The ANY array parameter takes 199 milliseconds with negligible planning. The VALUES hash join takes 1.95 seconds because it scans the whole table. A temporary table with statistics and a nested loop takes 230 milliseconds. IN (constants) 619 ms (413 planning) = ANY($1) 199 ms VALUES hash join 1,949 ms temp table + ANALYZE 230 ms the best form depends on list size relative to the table

Before and After #

-- BEFORE: 20,000-element constant IN list per batch
Index Cond: (sku = ANY ('{…}'))   Planning Time: 412.8 ms   Execution Time: 206.1 ms   (new statement text per batch)

-- AFTER: one statement with an array parameter
Index Cond: (sku = ANY ($1))      Planning Time: 0.3 ms     Execution Time: 198.4 ms   (one statement text)

Hashed IN lists in filters #

The executor-side hashing added in PostgreSQL 14 changes an old rule of thumb. Before it, a long constant IN list evaluated as a Filter — for example on a column without an index, or combined with other conditions that led to a sequential scan — was compared element by element for every row, so a 5,000-element list over 10 million rows meant up to 50 billion comparisons. With hashing, each row costs one hash probe. Nothing in the plan text marks the difference: the filter looks identical. If you run a mix of server versions, benchmark long-list filters on each rather than assuming the same cost.

Hashing applies only to constant lists of at least nine elements whose type has a hash function, so a filter over a parameter array does not get it; if a large parameterised list ends up as a row-by-row filter rather than an index condition, move it into an unnest join instead. NOT IN lists and <> ALL over constants are hashed in the same way from PostgreSQL 15.

Common Pitfalls #

Building SQL with inline lists from application code. Every length is a new statement. Diagnostic signal: thousands of near-identical entries in pg_stat_statements. Fix: array parameters.

Trusting the generic plan estimate for arrays. It assumes 10 elements. Diagnostic signal: rows=10 on an ANY($1) node with thousands of actual rows. Fix: custom plans for that statement, or unnest with a temp table for large sets.

Duplicates in a VALUES join. Repeated keys multiply output rows, unlike IN. Diagnostic signal: more rows than distinct keys. Fix: SELECT DISTINCT inside the key set or use EXISTS.

Nulls in the key list. x IN (…, NULL) never matches null, and NOT IN with a null returns nothing. Diagnostic signal: empty results from NOT IN. Fix: strip nulls before sending the list.

Frequently Asked Questions #

Is a long IN list slow in PostgreSQL? #

Long constant lists mainly cost planning time, because the planner estimates selectivity element by element. Execution is usually one index search per element or a hashed filter. Array parameters avoid the planning cost and produce one reusable statement.

What is the difference between IN and = ANY(array)? #

A constant IN list is rewritten to = ANY with a constant array, so plans are similar. The difference is when the array is a parameter: the statement text stays the same for any length, but generic plans estimate the array at 10 elements.

When should I join to VALUES instead of using IN? #

When the key set is large and feeds further joins that need an accurate row count, or when a hash or merge join against the table beats many individual index searches. For very large sets, a temporary table with ANALYZE gives the planner full statistics.

Up: CTE and Subquery Planning