Variable IN Lists and Statement Cache Bloat #

pg_stat_statements holds 5,000 entries, and 4,600 of them are the same query with different numbers of placeholders: WHERE id IN ($1), ($1, $2), ($1, $2, $3), up to ($1, …, $847). Real statements are being evicted to make room, monitoring is useless, and each new list length pays full planning cost the first time it appears.

Reading ORM-generated SQL is covered in ORM-generated EXPLAIN output. This page is about one specific shape ORMs produce and what it costs.

The Condition #

Most ORMs render WHERE id IN (…) by emitting one placeholder per element. The statement text therefore changes with the number of elements, and every distinct length is a distinct statement to the server:

The fix is to send one array parameter instead of N scalars: WHERE id = ANY($1). The statement text is then constant, so there is exactly one entry, one cached plan and one planning cost.

ORM support:

One statement per length, or one statement Left, placeholder lists: statement text changes with the element count, so thousands of entries appear in pg_stat_statements, the plan cache holds one entry per length, and planning is repeated for each new length. Right, an array parameter: one statement text, one entry, one cached plan, planning paid once. WHERE id IN ($1, $2, …, $n) text varies with n thousands of pg_stat_statements rows one cached plan per length planning repeated per new length WHERE id = ANY($1) one statement text one pg_stat_statements row one cached plan planning paid once the plans themselves are nearly identical; the bookkeeping is not

Annotated Evidence #

The bloat:

SELECT count(*) AS variants, sum(calls) AS calls, round(sum(total_exec_time)) AS total_ms
FROM pg_stat_statements
WHERE query LIKE 'SELECT % FROM products WHERE id IN (%';
 variants | calls   | total_ms
----------+---------+----------
     4612 | 1204110 |   184220
-- 4,612 of 5,000 entries are one logical query

Planning cost for a long list:

EXPLAIN (ANALYZE, SUMMARY)
SELECT id, sku FROM products WHERE id IN (1, 2, …, 2000);

Index Scan using products_pkey on products  (actual time=0.05..2.9 rows=2000 loops=1)
  Index Cond: (id = ANY ('{1,2,…}'::bigint[]))
  Index Searches: 2000
Planning Time: 41.2 ms
Execution Time: 3.1 ms
-- planning cost exceeds execution; and it recurs for every unseen length

With an array parameter:

SELECT id, sku FROM products WHERE id = ANY($1);

Index Scan using products_pkey on products  (actual time=0.05..2.9 rows=2000 loops=1)
  Index Cond: (id = ANY ($1))
  Index Searches: 2000
Planning Time: 0.3 ms
Execution Time: 3.0 ms
-- same execution, planning once
Three signs of variant bloat Three items are annotated. Thousands of pg_stat_statements entries differing only in placeholder count. Planning time larger than execution time for a long list. An Index Cond with a bound array parameter after the fix, with planning time back to a fraction of a millisecond. 4,612 variants of one query evicting real statements Planning Time: 41.2 ms > Execution 3.1 ms planning dominates Index Cond: (id = ANY ($1)) one statement, one plan pg_stat_statements normalises values, not structure

Step-by-Step Resolution #

  1. Quantify the bloat with the query above, grouping by the statement prefix.

  2. Identify the generating code path. The list length distribution usually points at a batch size in a job or a dataloader.

  3. Switch to an array parameter in the ORM. In SQLAlchemy:

    from sqlalchemy import bindparam, any_
stmt = select(Product).where(Product.id == any_(bindparam('ids')))
session.execute(stmt, {'ids': ids})

    In ActiveRecord:

    Product.where('id = ANY(?)', ids)

  4. Cap the array length anyway. Very large arrays raise per-statement latency; batch at a few hundred to a few thousand, as discussed in batching lazy loads with dataloaders.

  5. Watch the generic-plan estimate. With an array parameter, a generic plan assumes ten elements; if the result feeds joins, check that the plan is still sensible for real batch sizes.

  6. Raise pg_stat_statements.max as a stopgap if variants cannot be removed quickly, so genuine statements are not evicted.

  7. Re-measure the entry count and total planning time after deploying.

Effect of the change on one job Before, the batch job produced 4,612 statement variants and spent 184 seconds of planning time per day. After switching to an array parameter, it produces one statement variant and spends 4 seconds of planning per day, with execution time unchanged at 1,540 seconds. before: variants 4,612 statements before: planning/day 184 s after: variants 1 statement after: planning/day 4 s execution time is unchanged; only planning and bookkeeping improve

Before and After #

-- BEFORE: WHERE id IN ($1, …, $n) — one statement per length
4,612 pg_stat_statements entries   planning 184 s/day

-- AFTER: WHERE id = ANY($1)
1 entry                            planning 4 s/day

Why this matters beyond tidiness #

An overflowing statement-statistics table is not only an aesthetic problem. Eviction is silent, so the statements you most want to see — a new slow query introduced this morning — can be pushed out by thousands of IN variants before anyone looks. Monitoring built on pg_stat_statements then reports a workload that does not match reality, and comparisons across deploys become meaningless because the set of tracked statements changes.

There is also a plan-cache dimension when the driver prepares statements server-side. Each variant occupies a cached plan in every backend that ran it, multiplying memory use across a large connection pool, and each is subject to its own custom-to-generic transition. Collapsing hundreds of variants into one statement makes that behaviour predictable, which is a precondition for reasoning about the generic-plan issues in prepared statement plan pinning.

Finding every generator of variants #

The IN list is the most common source of statement variants, but not the only one. Dynamically built WHERE clauses — a search endpoint that appends a condition per supplied filter — produce one statement per combination of filters, and an ORM that renders ORDER BY from user input produces one per sort option. Each is individually reasonable and collectively capable of filling the statistics table.

A quick audit groups statements by their first eighty characters and counts distinct entries per group:

SELECT left(query, 80) AS prefix, count(*) AS variants, sum(calls) AS calls
FROM pg_stat_statements
GROUP BY 1 HAVING count(*) > 20
ORDER BY variants DESC LIMIT 10;

Groups with dozens of variants are worth a look. Some are unavoidable — genuinely different queries that share a prefix — but list expansion and filter permutations usually stand out, and both have fixes: array parameters for the first, and a fixed statement with conditionally neutral predicates (($1 IS NULL OR status = $1)) for the second, at the cost of slightly less precise plans.

Common Pitfalls #

Assuming normalisation handles it. Values are normalised; placeholder counts are not. Diagnostic signal: near-identical statements differing only in $n count. Fix: array parameters.

Very large arrays. One statement, but a slow one. Diagnostic signal: occasional multi-second batched lookups. Fix: cap the batch size.

Generic plans on array parameters. The ten-element assumption distorts joins above the scan. Diagnostic signal: rows=10 on an ANY($1) node feeding a nested loop. Fix: force custom plans for that statement or restructure.

Raising the statistics limit and stopping. The variants still cost planning. Diagnostic signal: high planning time with a large pg_stat_statements.max. Fix: fix the generation.

Frequently Asked Questions #

Why do I have thousands of similar entries in pg_stat_statements? #

Because each IN list length produces a different statement text. pg_stat_statements normalises literal values but not the number of placeholders, so every length is tracked separately and can evict other statements.

How do I send a list as one parameter in PostgreSQL? #

Use an array parameter with = ANY($1) and bind the list as an array. All major drivers support array binding, and every ORM has a way to emit that form.

Does using IN with many placeholders slow down queries? #

Execution is similar, but planning is repeated for each new length and is expensive for long lists, because selectivity is estimated element by element. One array parameter plans once.

Up: ORM-Generated EXPLAIN Output