An array parameter of the wrong width

A query that reads a set of ids out of a large table is about as ordinary as SQL gets. It gets slower as the list grows, which is expected, and it gets slower when the table grows, which is also expected. What it should not do is take ten seconds to read a list that the same statement reads in forty milliseconds when one type is spelled differently.

That is what this one did. These two blocks are the statements that were run — the text is read out of the files the harness executed, and so is the time commenting each one:

-- 9.89 s   (median of 3 interleaved rounds)
-- The id list as it arrives from most application layers.
--
-- Every id in the list is a bigint. The column is int4. PostgreSQL resolves
-- this to the cross-type operator `integer = bigint` (int48eq), which has no
-- hash path, so the membership test is answered by walking the array for
-- every row it is offered.
SELECT count(*)
  FROM readings
 WHERE sensor_id = ANY ($1::bigint[]);

-- 40 ms   (median of 3 interleaved rounds)
-- The same list, cast to the column's own type on the way in.
--
-- `int4 = int4` is int4eq, which does have a hash path: the array is read
-- once, hashed, and every row is one probe against it.
--
-- This is the fix to prefer, because it is a change to the PARAMETER and not
-- to the column. `sensor_id` keeps its four bytes, every index on it keeps
-- working, and the planner still sees the original expression — see
-- cast-the-column.sql for what happens when you widen the column instead.
SELECT count(*)
  FROM readings
 WHERE sensor_id = ANY ($1::bigint[]::int4[]);

sensor_id is int4. In the first statement PostgreSQL resolves the comparison to int48eq — a function that compares an int4 with an int8 — and in the second it resolves to int4eq. Everything else about the two statements is identical: same table, same list, same 64,000 ids, same rows out. The plan prints the same sentence for both, and EXPLAIN reports the same number of buffers.

What it costs

The fixture is a 200,000-row table and a 64,000-element id list. Three arms, run against one throwaway PostgreSQL 16 with the rounds interleaved, so neither arm inherits the other’s warmth.

244.505x, and the buffers column is the part worth looking at: 6,668 buffers either way. The slow arm is not reading more of the database. It is reading the same pages and doing something enormously expensive with what it finds there.

The plan does not say anything

Here is what the plan looks like, printed the same way for both arms:

Aggregate -> Gather -> Aggregate -> Parallel Seq Scan on readings
  Filter: (sensor_id = ANY ('{...}'::bigint[]))

That is the whole reason this survives review. There is no missing index to notice, no nested loop where a hash join should be, no sort spilling to disk. The plan is a parallel sequential scan with a filter, which is exactly what the fast version is too. Reading plans finds the expensive half of this problem and misses this one entirely.

What actually distinguishes them

Two measurements, and the second one is the one that matters.

The first is what happens when you change the length of the list and hold everything else still. If the array is being walked for every row offered to the filter, the cost is the number of rows multiplied by the number of elements, and the time per comparison stays put. If it is being hashed, the list is read once and then each row is one probe against the hash, and the cost per comparison falls as the list grows.

The last column is flat — the same fraction of a nanosecond per comparison across a 32x range of list lengths. That is a walk, and at 200,000 rows against 64,000 ids it is 12.8 billion integer comparisons, which is where the seconds went.

The second measurement is what tells you which type combinations do this, and it is not “cross-type”. A varchar column tested against a text[] is cross-type and it is fine. The thing that decides it is which comparison function the expression resolves to:

Look at the two middle columns. When the expression resolves to the same function the column’s own type would use, the test is fast — varchar = ANY(text[]) resolves to texteq, which is what text = text resolves to, because varchar is binary-coercible to text and never gets an operator of its own. When it resolves to something else — integer = ANY(bigint[]) becoming int48eq — it is roughly two hundred times slower.

The fix

Cast the list, not the column:

SELECT count(*)
  FROM readings
 WHERE sensor_id = ANY ($1::bigint[]::int4[]);

Three reasons to prefer this over the alternative.

It keeps the column’s type, so every index on sensor_id keeps working. The other direction, sensor_id::bigint = ANY($1), is just as fast here — the arm table above shows it at 40 ms — but what the planner is handed is (sensor_id)::bigint = ANY(...), and an index on an int4 column cannot answer a question about that column cast to something else. On a table that is already scanning, the trade is free. On a selective lookup it is a way to lose an index without the query failing or the plan looking wrong.

It is a change in one place. In most application layers the type of a parameter is decided where the list is built, so this is one cast at the boundary rather than a schema migration and a rewrite of every caller.

And it is an assertion about the data as well as the plan: if an id in the list does not fit in int4, the cast fails loudly instead of quietly comparing against a widened column.

In Rails, where a bigint[] is what a scope like this tends to produce, the cast is at the point the list becomes a parameter:

WHERE sensor_id = ANY ($1::int4[]);

What I would check first, next time

The general lesson is not about integers. It is that a mismatch between a parameter’s type and the column’s can move a query onto a different comparison function, and that switch is invisible in the plan. EXPLAIN shows you the filter expression and not the operator behind it, so the two forms print the same thing.

If a = ANY is expensive, EXPLAIN (VERBOSE) is the one that shows the expression in full, and it is worth checking that the operator it resolved to is the one the column uses on its own type. Where the types are not the same, the question is whether they are binary-coercible — varchar/text are, int4/int8 are not — and if they are not, measure it rather than reasoning about it, because the size of the effect depends entirely on how many rows the filter is offered.

Reproducing this

Everything above comes out of one case in this site’s benchmark harness, which boots its own PostgreSQL 16 in Docker, seeds a deterministic fixture, and runs the arms interleaved in transactions it rolls back so each starts from the same state.

npm run bench -- an-array-parameter-of-the-wrong-width

The harness refuses to report a speed-up unless every arm returned the same result set, so a rewrite that is fast because it answers a different question cannot be published from it. The two statements at the top of this article are read out of the files that were executed, not retyped:

-- 9.89 s   (median of 3 interleaved rounds)
-- The id list as it arrives from most application layers.
--
-- Every id in the list is a bigint. The column is int4. PostgreSQL resolves
-- this to the cross-type operator `integer = bigint` (int48eq), which has no
-- hash path, so the membership test is answered by walking the array for
-- every row it is offered.
SELECT count(*)
  FROM readings
 WHERE sensor_id = ANY ($1::bigint[]);

-- 40 ms   (median of 3 interleaved rounds)
-- The same list, cast to the column's own type on the way in.
--
-- `int4 = int4` is int4eq, which does have a hash path: the array is read
-- once, hashed, and every row is one probe against it.
--
-- This is the fix to prefer, because it is a change to the PARAMETER and not
-- to the column. `sensor_id` keeps its four bytes, every index on it keeps
-- working, and the planner still sees the original expression — see
-- cast-the-column.sql for what happens when you widen the column instead.
SELECT count(*)
  FROM readings
 WHERE sensor_id = ANY ($1::bigint[]::int4[]);