Why COUNT(*) got slow, and what I did instead
We have a table of shipment events that grows by a few hundred thousand rows a day. A status page showed the total number of open events, and one morning it started timing out.
The query was as plain as it gets:
SELECT count(*) FROM shipment_events WHERE closed_at IS NULL;
What was going on
PostgreSQL has to check row visibility for every row it counts, so even an index-only scan ends up touching the heap when the visibility map is stale. Our autovacuum was falling behind on this table, which meant almost every page needed a heap fetch.
What helped
Two changes. First, a partial index, so the scan covers only open events:
CREATE INDEX CONCURRENTLY idx_events_open
ON shipment_events (id) WHERE closed_at IS NULL;
Second, more aggressive autovacuum settings for this one table, so the visibility map stays fresh:
ALTER TABLE shipment_events
SET (autovacuum_vacuum_scale_factor = 0.01);
The query is back under 50 ms. For the page that only needs a rough total, I now read reltuples from pg_class and label the number as approximate.