EXPLAIN & EXPLAIN ANALYZE

When a query is slow, guessing is a waste of time. PostgreSQL will tell you exactly how it runs any query: which tables it reads, in what order, with which indexes and join methods, and how long each step took. That description is called the query plan, and EXPLAIN shows it.

In this lesson you will read plans, understand the main node types, compare the planner's estimates with reality using EXPLAIN ANALYZE, and follow a simple routine for fixing slow queries.

How PostgreSQL plans a query

Every query goes through the planner (also called the optimiser) before it runs. The planner considers different ways to execute the query, estimates the cost of each using statistics about your tables, and picks the cheapest. EXPLAIN prints the chosen plan without running the query:

EXPLAIN
SELECT o.order_id, c.first_name
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
WHERE o.status = 'pending';

QUERY PLAN
--------------------------------------------------------------------
Hash Join  (cost=20.43..32.24 rows=4 width=102)
  Hash Cond: (c.customer_id = o.customer_id)
  ->  Seq Scan on customers c  (cost=0.00..11.30 rows=130 width=102)
  ->  Hash  (cost=20.38..20.38 rows=4 width=8)
        ->  Seq Scan on orders o  (cost=0.00..20.38 rows=4 width=8)
              Filter: ((status)::text = 'pending'::text)
(6 rows)

A plan is a tree. Read it from the most indented lines (the leaves) upwards: each node produces rows for the node above it. Each line shows:

• cost=start..total: estimated cost in arbitrary units. The first number is the cost before the first row comes out, the second for all rows. Use them to compare plans, not as milliseconds.
• rows: estimated number of rows this node returns.
• width: estimated average row size in bytes.

The practice tables are so small that the planner happily scans them; on real data the same query would look different.

Common plan nodes

EXPLAIN ANALYZE: what really happened

EXPLAIN ANALYZE actually runs the query and adds the real numbers next to the estimates. Because it runs the query, wrap an UPDATE or DELETE in BEGIN ... ROLLBACK when you analyse it. Timing values change on every run, so this example turns them off to keep the output stable:

CREATE TABLE visits AS
SELECT g AS visit_id, 1 + g % 5000 AS customer_id,
       date '2025-01-01' + (g % 365) AS visit_date
FROM generate_series(1, 100000) AS g;
CREATE INDEX visits_customer_idx ON visits (customer_id);
ANALYZE visits;

EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF)
SELECT visit_date, count(*)
FROM visits
WHERE customer_id BETWEEN 100 AND 120
GROUP BY visit_date
ORDER BY visit_date
LIMIT 3;

Query OK
Query OK
Query OK
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Limit  (cost=574.28..574.29 rows=3 width=12) (actual rows=3.00 loops=1)
  Buffers: shared hit=24 read=3
  ->  Sort  (cost=574.28..574.89 rows=246 width=12) (actual rows=3.00 loops=1)
        Sort Key: visit_date
        Sort Method: top-N heapsort  Memory: 17kB
        Buffers: shared hit=24 read=3
        ->  HashAggregate  (cost=568.64..571.10 rows=246 width=12) (actual rows=260.00 loops=1)
              Group Key: visit_date
              Batches: 1  Memory Usage: 56kB
              Buffers: shared hit=24 read=3
              ->  Bitmap Heap Scan on visits  (cost=8.48..566.59 rows=409 width=4) (actual rows=420.00 loops=1)
                    Recheck Cond: ((customer_id >= 100) AND (customer_id <= 120))
                    Heap Blocks: exact=24
                    Buffers: shared hit=24 read=3
                    ->  Bitmap Index Scan on visits_customer_idx  (cost=0.00..8.38 rows=409 width=0) (actual rows=420.00 loops=1)
                          Index Cond: ((customer_id >= 100) AND (customer_id <= 120))
                          Index Searches: 1
                          Buffers: shared read=3
Planning:
  Buffers: shared hit=24 read=1
(20 rows)

Your cost and estimated rows numbers may differ a little from these. ANALYZE reads a random sample of the table, so the estimates change slightly between runs. The shape of the plan and the actual rows stay the same, and those are what matter.

Each node now shows actual rows and loops. The most useful check in all of performance tuning is to compare estimated rows with actual rows. If the planner expected 10 rows and got 100,000, it probably chose the wrong plan, often because statistics are out of date (run ANALYZE) or because the condition is hard to estimate. Buffers: shared hit counts 8 kB pages found in PostgreSQL's memory cache, and read counts pages that had to be fetched from disk (or the operating system's cache). Many reads on a repeated query mean the data does not fit in memory. PostgreSQL 18 shows buffers automatically with ANALYZE.

On your own server, plain EXPLAIN ANALYZE also prints the time of each node and a summary like this:

Planning Time: 0.214 ms
Execution Time: 1.873 ms

Useful EXPLAIN options

pgAdmin's Query Tool can draw the plan as a diagram (the Explain and Explain Analyze buttons), which helps with large plans.

A routine for slow queries

1. Run EXPLAIN (ANALYZE, BUFFERS) on the slow query with realistic data.
2. Find the node where most of the time goes, usually the one with the largest actual time or the most rows.
3. Compare estimated and actual rows there. A big mismatch points to statistics: run ANALYZE table.
4. Look for a Seq Scan on a large table that returns few rows. That is a candidate for an index on the filtered or joined columns.
5. Look for Rows Removed by Filter with huge numbers, or a Sort that spills to disk (Sort Method: external merge).
6. Change one thing, then run EXPLAIN ANALYZE again and compare.

Here is step 4 in action. The query filters on visit_date, which has no index yet:

CREATE TABLE visits AS
SELECT g AS visit_id, 1 + g % 5000 AS customer_id,
       date '2025-01-01' + (g % 365) AS visit_date
FROM generate_series(1, 100000) AS g;
ANALYZE visits;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF)
SELECT count(*) FROM visits WHERE visit_date = '2025-03-15';

CREATE INDEX visits_date_idx ON visits (visit_date);

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF)
SELECT count(*) FROM visits WHERE visit_date = '2025-03-15';

Query OK
Query OK
QUERY PLAN
-----------------------------------------------------
Aggregate (actual rows=1.00 loops=1)
  ->  Seq Scan on visits (actual rows=274.00 loops=1)
        Filter: (visit_date = '2025-03-15'::date)
        Rows Removed by Filter: 99726
(4 rows)

Query OK
QUERY PLAN
-----------------------------------------------------------------------------
Aggregate (actual rows=1.00 loops=1)
  ->  Bitmap Heap Scan on visits (actual rows=274.00 loops=1)
        Recheck Cond: (visit_date = '2025-03-15'::date)
        Heap Blocks: exact=274
        ->  Bitmap Index Scan on visits_date_idx (actual rows=274.00 loops=1)
              Index Cond: (visit_date = '2025-03-15'::date)
              Index Searches: 1
(7 rows)

Before the index, PostgreSQL removed almost every row by filter. After it, the index finds the matching rows directly. On a table with millions of rows, that is the difference between a slow page and an instant one.

Common mistakes

• Running EXPLAIN ANALYZE on a data-changing query without a rollback. It really executes.
• Tuning on a tiny test table. Plans on 15 rows tell you nothing about 15 million. Test with realistic sizes.
• Reading costs as milliseconds. Costs are relative units; actual times come from ANALYZE.
• Ignoring estimate errors. A plan built on wrong row estimates is often the real problem; refresh statistics first.

Key points

• EXPLAIN shows the plan the planner chose; read the tree from the innermost nodes upwards.
• Costs are estimates in relative units; rows is the estimated row count.
• EXPLAIN ANALYZE runs the query and shows actual rows, loops, buffers and times.
• Big differences between estimated and actual rows usually mean stale or weak statistics.
• Seq Scans on large tables that return few rows are the classic sign of a missing index.
• Change one thing at a time and measure again.

CREATE TABLE visits AS
SELECT g AS visit_id, 1 + g % 5000 AS customer_id,
       date '2025-01-01' + (g % 365) AS visit_date
FROM generate_series(1, 100000) AS g;
ANALYZE visits;

CREATE INDEX visits_cust_date_idx ON visits (customer_id, visit_date);

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF)
SELECT * FROM visits WHERE customer_id = 7 AND visit_date > '2025-06-01';