Speed up COUNT(*) on a large table
SELECT count(*) on a big table scans every row, every time. When an estimate is good enough, read it from the catalog instead, 34.8 ms of scanning becomes an instant lookup.
Problem
What you're actually looking at
The symptom as it shows up on a real server.
PostgreSQL's MVCC means count(*) has to visit rows to know which are visible to you, so an exact count on a large table is always a full scan. Dashboards that poll count(*) put steady, pointless load on the server.
A Meridian ops dashboard shows the total parcel_scans row count. An exact count scans all 700,000 rows on every refresh, even though the number only needs to be approximately right.
Simple terms
Because every transaction can see a slightly different set of rows, PostgreSQL cannot keep one true row count lying around, it has to actually visit the rows to know which ones count for you. That makes an exact count(*) a full scan of the table every single time. When an approximate number is good enough, like a total on a dashboard, you can read the planner's own row estimate from the catalog instantly instead of counting for real.
Full runbook for this incident
- The full identify checklist, the exact signals that tell you it's this incident
- Every diagnostic query; lab output is attached only to the steps we actually captured
- The resolution path and the pitfalls that make it worse
- Mitigation steps to stop it recurring, plus a verify-you're-done query
Card required. Cancel before day 7 and you are not charged.
More in this category
Other Query performance runbooks
Neighbouring incidents that share the same diagnostic surface.
Connected
How this connects to the rest of the library
A live view of this page's real cross-references, what explains it, what fixes it, what to tune, and where to go next. Every link is an authored relationship, not a guess.
Fixes these errors
Need the full procedure?
Pro runbooks finish the incident path
Free runbooks teach the shape. Pro opens the full step transcript, edge cases, and prevention depth.