The number that told Postgres it was on a spinning disk

The setting that decides which plan wins

Postgres doesn't use an index just because one exists. It prices every plan it can think of and runs the cheapest, and part of that pricing is a number saying how expensive it is to fetch a page from storage at random instead of in sequence. The setting is random_page_cost and its default is 4, which describes a mechanical disk, a platter with an arm that has to move.

On SSD-backed storage a random page costs very nearly the same as a sequential one. Leaving the default in place tells the planner that every index scan carries a fourfold penalty it'll never actually pay. The planner then does the rational thing with bad information. It declines the index and reads the whole table.

Managed Postgres hosts generally ship that default unchanged. It was sound advice when storage had moving parts, and it's survived onto storage that's never had one, so these days I read the plan before accepting that a query is just slow.

The query was already right

I build and maintain a data platform for a client, and part of it counts how many records in a multi-million-row archive match a set of filters. An earlier note covers moving that count out of the application and into a single aggregate query, which is where it should have been all along.

This time the problem looked different. Warm counts came back in a few seconds, and cold ones ran long enough to blow the transaction's timeout. Same query, same filters, and two very different runtimes depending only on whether the pages happened to be in memory.

That gap tells you something by itself. When warm and cold differ by an order of magnitude, I think it's worth looking at how much data the query has to touch before looking at its logic.

A 4GB table and a 232MB index

The archive table was about 4GB, and most of that was a raw JSON column kept from ingest that nothing reads. Rows are stored whole, so a column nobody queries still costs you on every page of a sequential scan.

Almost every count starts from the same baseline predicate, so once I looked at it that way the index to build was obvious. It's partial over that subset, and covers every column a count reads.

CREATE INDEX CONCURRENTLY records_deliverable_idx ON records (source)
  INCLUDE (row_hash, phone, email, contact_status, revenue, region, area_code)
  WHERE phone_type = 'Mobile' AND email_status = 'Valid' AND excluded IS NOT TRUE;

It's partial so it only holds the rows a count considers, and covering so an index-only scan never has to go back to the heap for a missing value. The result is 232MB against 4GB, so the same answer read from a seventeenth of the bytes.

CONCURRENTLY matters in production, and it has one awkward side effect. It builds without a write lock, so the table stayed available the whole time, but CREATE INDEX CONCURRENTLY can't run inside a transaction, and the migration tool wraps every migration in one. So I built the index by hand against the live database, and the committed migration is an IF NOT EXISTS no-op that makes a fresh environment match. It's slightly unsatisfying, but it's correct.

Postgres declined it

And then the counts kept sequentially scanning 4GB.

The index was valid and it covered the query. The planner had just priced an index-only scan of 232MB above a sequential read of 4GB, because at random_page_cost = 4 every page that index touches is costed as if an arm has to swing across a platter to get to it. It picked the wrong plan, but given the inputs its maths was flawless.

So the fix was to correct the input.

SET LOCAL random_page_cost = '1.1';

I used 1.1 and not 1 because sequential reads do still get real help from readahead, and a sliver of margin keeps the ordering honest without flattening it.

SET LOCAL matters as much as the value. Set inside the transaction, it only lives as long as that one query. A plain SET on a pooled connection outlives the request and silently re-plans whatever gets served on that connection next, which is a really unpleasant thing to debug.

What it did to memory

The direct win is one query picking a better plan. The one I think is worth writing down is what happened to memory.

The count's cold working set went from around 4.5GB to around 800MB. When the host goes to storage cold it reads at roughly 1,400 pages per second, so 4.5GB isn't something you can wait out, but the size mattered more than the read rate. 4.5GB didn't fit in the cache, so every count evicted the pages the previous count had just warmed, and every request started cold. The system had settled into a state where the cache could never help.

800MB fits, and it stays there. So now the count is warm nearly every time, where before it was cold every single time.

When I'm judging an index now, the first number I look at is the size of the data a query has to keep in memory, before the runtime, because the runtime follows from it.

A misleading timeout error

One footnote that really bit. Setting random_page_cost and work_mem per query means running the count inside an interactive transaction, and the ORM's interactive transactions default to a five-second ceiling. The count picked up a limit it had never had as a bare query, and a long cold run aborted with commit cannot be executed on an expired transaction. That error describes the transaction wrapper and says nothing about the query inside it.

The fix is boring. It's a 120-second timeout, plus a 15-second maxWait so a busy pool doesn't fail before the transaction even starts.

Wrapping a statement in a transaction to configure one thing brings the whole transaction's policy with it (timeouts, isolation, pool acquisition), so the first error you see is about the transaction and not the query inside it.