Analyze PostgreSQL Query Plans with EXPLAIN ANALYZE

EXPLAIN ANALYZE runs your query and returns the plan PostgreSQL actually executed, with real timings and row counts for every node. This guide shows you how to read that output, find the node that costs the most time, and decide whether an index, a query rewrite or fresh statistics is the fix.

The planner's estimates are guesses. EXPLAIN ANALYZE gives you the measured numbers next to the guesses, which is where the useful information lives.

Before you start

You need:

• A Linux VPS running PostgreSQL with a database you can query.
• A psql session as a user with SELECT rights on the tables involved.
• One query that is slower than you want, ideally with a WHERE clause and a join.

Connect to the server:

psql -h 203.0.113.10 -U deploy -d appdb

Run EXPLAIN ANALYZE on a query

Start with the plain form on a read-only query:

EXPLAIN ANALYZE
SELECT c.id, c.email, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.created_at >= now() - interval '7 days'
ORDER BY o.total DESC
LIMIT 20;

Add options when you want more detail:

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT ...

• ANALYZE runs the query and reports actual times and rows.
• BUFFERS shows shared blocks hit and read, which tells you whether the data came from cache or disk.
• VERBOSE lists the output columns of each node.

For a statement that writes, keep it inside a transaction:

BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
UPDATE orders SET status = 'archived' WHERE created_at < now() - interval '2 years';
ROLLBACK;

The ROLLBACK discards the changes. Nothing is committed.

Read PostgreSQL EXPLAIN ANALYZE output

A typical plan looks like this:

Limit  (cost=1842.11..1842.16 rows=20 width=48) (actual time=181.204..181.209 rows=20 loops=1)
  ->  Sort  (cost=1842.11..1842.16 rows=20 width=48) (actual time=181.203..181.207 rows=20 loops=1)
        Sort Key: o.total DESC
        Sort Method: top-N heapsort  Memory: 27kB
        ->  Hash Join  (cost=612.30..1841.60 rows=20 width=48) (actual time=12.884..180.115 rows=20 loops=1)
              Hash Cond: (o.customer_id = c.id)
              ->  Seq Scan on orders o  (cost=0.00..1104.00 rows=20 width=16) (actual time=0.015..168.402 rows=20 loops=1)
                    Filter: (created_at >= (now() - '7 days'::interval))
                    Rows Removed by Filter: 480212
              ->  Hash  (cost=487.30..487.30 rows=10000 width=16) (actual time=12.812..12.812 rows=10000 loops=1)
                    Buckets: 16384  Batches: 1  Memory Usage: 640kB
                    ->  Seq Scan on customers c  (cost=0.00..487.30 rows=10000 width=16) (actual time=0.008..6.201 rows=10000 loops=1)
Planning Time: 0.412 ms
Execution Time: 184.223 ms

Read it inside out. The deepest indented node runs first and feeds its parent. Two numbers matter on every line:

• cost=start..total is the planner's estimate in arbitrary units, not milliseconds.
• actual time=startup..total is real milliseconds, averaged over loops.

Scans

• Seq Scan reads the whole table. Fine for small tables, expensive on large ones.
• Index Scan walks an index and fetches matching rows from the heap.
• Index Only Scan answers from the index alone when the visibility map is current.
• Bitmap Index Scan plus Bitmap Heap Scan collects row locations first, then reads the heap in physical order. Good for medium selectivity.

Joins

• Nested Loop runs the inner side once per outer row. Fast when the outer side is tiny and the inner side is indexed.
• Hash Join builds a hash table from the smaller side, then probes it. The usual choice for large unsorted inputs.
• Merge Join sorts both sides and walks them together. Useful when inputs are already sorted.

Timings and loops

actual time is per loop, so multiply by loops for the real cost. A nested loop with loops=50000 and actual time=0.4..0.9 is spending roughly 45 seconds in that node, even though each iteration looks cheap.

Compare rows= in the cost section with rows= in the actual section. If the estimate is 20 and the reality is 480,000, the planner chose a plan for the wrong shape of data.

Spot planning problems

Look for these signals:

• Estimate off by more than ten times. Statistics are stale or the column has a skewed distribution. Run ANALYZE orders; and consider raising the statistics target.
• Rows Removed by Filter is huge. The scan reads far more rows than it returns, which usually means a missing index on the filter column.
• Sort Method: external merge Disk: 24576kB. The sort spilled to disk. Raise work_mem for the session and re-measure.
• Nested Loop with high loops. The planner underestimated the outer side. Refresh statistics or add the index the inner side needs.
• Buffers: shared read= far above shared hit=. The working set does not fit in cache, so the query is doing disk I/O.

Test a change without touching production data:

SET work_mem = '64MB';
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
RESET work_mem;

To refresh statistics on one table:

ANALYZE VERBOSE orders;

To raise the sample size for a skewed column:

ALTER TABLE orders ALTER COLUMN customer_id SET STATISTICS 500;
ANALYZE orders;

Check it works

Run the query twice with the same options and compare Execution Time and the top node's actual time. After adding an index or refreshing statistics you should see a different node type, for example Index Scan replacing Seq Scan, and a lower total.

Confirm the index is actually used:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM orders WHERE customer_id = 42;

If the plan still shows Seq Scan, the table may be small enough that the planner prefers it, or the index is not selective enough to help. Check pg_stat_user_indexes to see whether an index is being used at all.

SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC
LIMIT 20;

Troubleshooting

• EXPLAIN ANALYZE runs for minutes. It executes the query. Use plain EXPLAIN for estimates only, or wrap writes in BEGIN;... ROLLBACK;.
• ERROR: permission denied for relation orders. Your role lacks SELECT. Grant it or connect as a role that has it.
• Estimates never match reality. Autovacuum may not be keeping up on a busy table. Check the related autovacuum guide below.
• The plan changes between runs. Different parameter values produce different plans. Test with the real values your application sends, and check whether prepared statements are using generic plans.
• A new index is ignored. Run ANALYZE on the table afterwards. The planner needs statistics before it will trust the index.

Next steps

• Diagnose slow database queries with EXPLAIN for the wider workflow around this command.
• Tune PostgreSQL autovacuum and VACUUM settings so statistics stay fresh.
• psql cheat sheet: essential PostgreSQL commands for the commands you will type most often.