PostgreSQL EXPLAIN ANALYZE Plan Reading for Slow Queries

To read a PostgreSQL query plan, compare estimated rows with actual rows, find the node where most of the time is spent, and check how rows are scanned and joined. EXPLAIN shows the plan the planner chose, while EXPLAIN ANALYZE actually runs the query and adds real timings. Large gaps between estimates and reality are the most common sign that the planner is working from bad information.

What Do EXPLAIN and EXPLAIN ANALYZE Show?

EXPLAIN prints the plan as a tree of nodes, each with a cost estimate and an expected row count, without running the query. EXPLAIN ANALYZE executes the query and adds actual time, actual rows, and the number of loops for every node. Because it really runs, be careful with statements that modify data. Wrap them in a transaction and roll back.

BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at > now() - interval '7 days';
ROLLBACK;

Adding the BUFFERS option reveals how many pages came from cache and how many were read from disk, which often explains why a query is slow. Output format and available options vary a little between versions, so check the PostgreSQL documentation for yours.

How Do You Read the Plan Tree?

Read from the innermost, most indented nodes outward. Each node passes rows up to its parent. The numbers that matter most are the actual time, the actual rows, and the loops. When a node runs several times, the displayed time is per loop, so multiply it by the loop count to see the total.

• Cost: the planner's relative estimate, not a time. It is useful for comparing plans, not for predicting seconds.
• Actual time: milliseconds to produce the first and all rows of the node, per loop.
• Rows and loops: total rows are the displayed rows multiplied by loops.
• Rows removed by filter: a large number means the scan reads far more than it needs, which hints at a missing or unusable index.

Start by locating the node with the largest share of total time, ignoring children that simply feed it. That is where tuning effort pays off.

Why Do Estimates and Actual Rows Differ?

The planner chooses a plan based on statistics about your data. If the estimated row count is far from the actual one, it may pick a poor join order, the wrong join algorithm, or a sequential scan where an index would win. The error often grows through the plan, because each node builds on the previous estimate.

Common causes include stale statistics after a large data change, correlated columns that the planner treats as independent, and conditions on expressions it cannot estimate well. Running ANALYZE refreshes statistics, and extended statistics can describe relationships between columns. Whether a given option helps depends on your version and data, so test it.

A sequential scan is not automatically bad. For small tables, or when a query needs a large fraction of the rows, scanning everything is cheaper than jumping through an index.

Which Plan Patterns Point to Real Problems?

Certain patterns show up repeatedly in slow queries. Recognizing them speeds up diagnosis.

• A nested loop with a large inner side repeated many times, often a sign of underestimated row counts.
• A sort or hash that spills to disk, shown as external merge or multiple batches, which suggests memory settings are too low for the operation.
• A sequential scan with many rows removed by a filter on a selective condition, pointing to a missing index.
• High shared read counts in BUFFERS, meaning the data was not in cache, so a second run may look much faster.
• An index scan that still visits many heap pages, which can indicate poor correlation or bloat.

Make one change at a time and re-run the plan. Test with realistic data volumes and warm caches, because a plan on a tiny development table can differ completely from production. Remember that the first run may be slower due to cold cache.

Frequently Asked Questions

Does EXPLAIN ANALYZE run my query?

Yes, it executes it, including any writes. Use a transaction with rollback when analyzing INSERT, UPDATE, or DELETE statements.

What does cost mean in a plan?

It is an arbitrary unit the planner uses to compare plans. It is not milliseconds, so rely on actual time for performance.

Why is the planner ignoring my index?

It may estimate that a sequential scan is cheaper, the condition may not match the index definition, or statistics may be off. Compare estimates against actual rows.

Should I always avoid sequential scans?

No. For small tables or queries touching most rows, they are often the best choice.

Summary

Read plans from the inside out, find the node that dominates time, and compare estimated rows with actual ones. Use BUFFERS to see I/O, refresh statistics, and change one thing at a time. Check the PostgreSQL documentation for version-specific output.