What it shows
- Every plan node as a tree: scans, joins, sorts, aggregates.
- The relation and index each node touches.
- Estimated rows, and with ANALYZE the actual rows and time.
- A bar for each node's share of the total time, and a highlight on the slowest one.
- A warning where the planner's row estimate is off by 10× or more, the usual cause of a bad plan.
Plain EXPLAIN vs EXPLAIN ANALYZE
EXPLAIN only plans the query. EXPLAIN ANALYZE actually executes it to collect real timings. For INSERT, UPDATE or DELETE that means the change happens, so pgquery asks before analyzing a data-modifying statement. To analyze one safely, wrap it in BEGIN; … ROLLBACK;.
Typical fixes
- A sequential scan on a large table with a selective filter: add an index.
- Estimates far from actuals: run
ANALYZE table_name;or raise the statistics target. - A sort spilling to disk: an index on the ORDER BY columns or more
work_mem.