A query over 300M rows times out — how do you use EXPLAIN ANALYZE to find the culprit?
A dashboard query over a 300M-row events table times out. You captured its plan with EXPLAIN (ANALYZE, BUFFERS):
Hash Join (cost=.. rows=1200 ..) (actual time=.. rows=48250213 ..)
-> Seq Scan on events (cost=.. rows=1200) (actual rows=300000000 ..)
Filter: (country = 'US')
Rows Removed by Filter: 251749787
-> Hash (actual rows=1000 ..)
-> Seq Scan on dim_country (actual rows=1000 ..)Diagnose the cause and name the fix.
Read bottom-up for the node whose actual time and rows dominate. Here a Seq Scan on the 300M-row events estimates 1200 rows but returns 300M — a huge estimate-vs-actual gap from stale statistics, with no index on country. Fix: run ANALYZE events and index the filtered column.
- ✗Raising statement_timeout instead of fixing the plan
- ✗Blaming the hash-join algorithm rather than the estimate and missing index
- ✗Misreading Rows Removed by Filter as a broken filter
- →How does the BUFFERS line confirm the Seq Scan is the bottleneck?
- →Why does a bad row estimate mislead the join algorithm choice?
Read the plan bottom-up and find the node where actual time and rows peak. The inner Seq Scan on events was estimated at rows=1200 but actually returned rows=300000000, and Rows Removed by Filter shows the country = 'US' filter discarded 251M rows AFTER reading the whole table. The 250,000x estimate-vs-actual gap is a stale-statistics signature; the low estimate pushed the planner into a plan that then exploded to 48M rows.
Two fixes:
ANALYZE events; -- refresh statistics
CREATE INDEX ON events (country); -- remove the Seq Scan on the filterAfter ANALYZE the estimate approaches reality, and the index on country turns the Seq Scan into an Index/Bitmap Scan that reads only US rows instead of all 300M. Do not touch the join algorithm or raise the timeout — those treat the symptom, not the cause.
