How I read EXPLAIN ANALYZE
Three numbers I look at first, and the one I used to look at and now ignore.
A query plan is a tree, and most of it is fine. I look for the node where the estimate and reality disagree.
The three numbers
- Estimated rows vs. actual rows. If the planner expected 12 rows and got 480,000, everything above that node was planned for the wrong world. Fix the statistics first.
- Loops.
actual time=0.02..0.03 rows=1 loops=95000looks cheap until you multiply. That is a nested loop doing 95,000 index lookups. - Buffers. Run with
explain (analyze, buffers).shared readmeans disk.shared hitmeans cache. A slow query with only hits is CPU-bound, and an index won’t save it.
The one I ignore
Total cost. It is in planner units, not milliseconds, and comparing it between two different queries tells you nothing.