EXPLAIN ANALYZE is the most useful command in PostgreSQL, and most people skim its output and guess. The output is a story: each line is a step, the steps nest, and the numbers tell you where the time went. Once you can read it, slow queries stop being a mystery.
Run it the right way
Use these options every time.
EXPLAIN (ANALYZE, BUFFERS)
SELECT c.name, count(*) AS orders
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.created_at >= now() - interval '30 days'
GROUP BY c.name
ORDER BY orders DESC
LIMIT 10;ANALYZE runs the query and reports real times and row counts. BUFFERS reports how many pages were read from memory and from disk. Without them, you see only the planner's guess.
Be careful: ANALYZE really executes the statement. Wrap an UPDATE or DELETE in a transaction and roll it back.
