A plan is a tree. Read it bottom-up.
Leaves scan tables; parents consume their children’s rows. In a revenue report, 196,412 orders shrink to 10 rows: the work sits at the bottom.
Does EXPLAIN show what happened?
Bad: Yes, it prints rows
Estimates only: EXPLAIN never executes.
Good: Only EXPLAIN ANALYZE runs it
Actual rows and time per node. An UPDATE really updates: wrap it in BEGIN … ROLLBACK.
Planned for 480 rows. Got 48,000.
Zone 412 is all Mysuru, but the planner multiplied 1% by 0.27% as if independent. Expecting 480 rows, it chose a nested loop that probed 48,000 times.
The nested loop looks like the planner’s mistake.
The twist
For 480 rows it was the cheapest plan. Teach it zone → city with CREATE STATISTICS; it estimates 47,600 and picks a hash join itself.
Three ways to join, picked from the row estimates
Nested loops break when the outer side is far bigger than planned; hash joins when the build side is.
250 GB
worst case with work_mem at 256 MB
work_mem limits each sort or hash, not each query. 200 connections × 5 operations × 256 MB adds up. Raise it for the report’s session only.
Fast in psql, slow as the app’s prepared statement.
The catch
After five runs, PostgreSQL may switch to a generic plan that ignores the value. 'pending' (0.02%) wants an index; 'delivered' (91%) a scan.
Remember
Read the plan bottom-up. Find the lowest node where estimated and actual rows split; fix that estimate, not the join.