Catalog My learning Blog
PostgreSQL Course: Beginner to Production in 30 Days · Phase 3 · Lesson 22 · 8 min read

PostgreSQL EXPLAIN: How to Read Query Plans

On this page
  1. Symptom: a query got slow and nobody changed it
  2. How to read EXPLAIN ANALYZE output
  3. Diagnose: when estimates go wrong
  4. Fix: refresh statistics with ANALYZE
  5. Fix: teach the planner that columns are related
  6. Scan and join node types
  7. Another common culprit: sorts that spill to disk
  8. Check yourself

Symptom: a query got slow and nobody changed it

A report that ran in a blink yesterday now takes much longer. The code didn't change. What changed is the plan: the step-by-step strategy PostgreSQL's planner chose for the same SQL. EXPLAIN shows you that plan, and EXPLAIN ANALYZE runs the query and shows what really happened. Reading them is the most useful skill for "why is this slow", far better than guessing at indexes.

The plans in this lesson come from a test database with 20,000 customers and 300,000 orders.

How to read EXPLAIN ANALYZE output

SQL
EXPLAIN ANALYZE
SELECT o.id, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'refunded';
Code
Hash Join  (cost=578.00..6541.43 rows=2830 width=18) (actual time=7.533..24.732 rows=3000 loops=1)
  Hash Cond: (o.customer_id = c.id)
  ->  Seq Scan on orders o  (cost=0.00..5956.00 rows=2830 width=8) (actual time=0.029..16.308 rows=3000 loops=1)
        Filter: (status = 'refunded'::text)
        Rows Removed by Filter: 297000
  ->  Hash  (cost=328.00..328.00 rows=20000 width=18) (actual time=7.497..7.497 rows=20000 loops=1)
        ->  Seq Scan on customers c  (cost=0.00..328.00 rows=20000 width=18) (actual ...)
Execution Time: 25.067 ms

A plan is a tree. Each -> is a child node that feeds rows to the node above it, so data flows from the most indented lines upward. Here PostgreSQL scans customers into an in-memory hash table, scans orders keeping only refunded ones, and matches the two.

Each node has two sets of numbers:

  • Estimates, in the first brackets. cost=578.00..6541.43 is the cost to return the first row and all rows, in abstract planner units, not milliseconds. rows is how many rows the planner expected. width is the average row size in bytes.
  • Actuals, in the second brackets, from ANALYZE. actual time is in milliseconds, rows is what really came out, and loops is how many times the node ran.

The single most useful habit: compare estimated rows with actual rows on the same node. Here it expected 2,830 and got 3,000. That's close, so the planner had a good picture and its choices are probably sensible. When the two numbers differ by 10 times or more, you've usually found your problem.

On PostgreSQL 18, actual row counts print with two decimals, as in rows=3000.00. They're averages per loop, and the decimals stop small averages from rounding down to 0. The plans in this lesson are trimmed to whole numbers to keep them short.

Two warnings before you start. EXPLAIN ANALYZE really runs the statement, so for an UPDATE or DELETE wrap it in BEGIN; ... ROLLBACK;. And when loops is above 1, the time and rows shown are per loop, so multiply to get the total.

Diagnose: when estimates go wrong

Here's a real case. A batch job loads 50,000 orders with a new status, disputed. Then this report runs:

SQL
EXPLAIN ANALYZE
SELECT c.country, count(*)
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'disputed'
GROUP BY c.country;
Code
GroupAggregate  (cost=14.04..14.06 rows=1) (actual time=79.441..89.050 rows=5 loops=1)
  ->  Sort  (rows=1) (actual rows=50000 loops=1)
        ->  Nested Loop  (cost=0.71..14.03 rows=1) (actual time=0.046..69.119 rows=50000 loops=1)
              ->  Index Scan using orders_status_idx on orders o  (rows=1) (actual rows=50000 loops=1)
              ->  Index Scan using customers_pkey on customers c  (rows=1) (actual rows=1 loops=50000)
Execution Time: 89.099 ms

The planner expected 1 disputed order and got 50,000. Based on that estimate, a nested loop looked perfect: find the one order, look up its customer. In reality, the inner index lookup ran 50,000 times (loops=50000).

Why the bad estimate? The planner works from statistics: samples of each column's common values and distribution, gathered by ANALYZE. The statistics were collected before the batch load, so as far as the planner knew, disputed didn't exist. Autovacuum would refresh them eventually, but the report ran first.

Fix: refresh statistics with ANALYZE

Code
ANALYZE orders;

Run the same EXPLAIN ANALYZE:

Code
HashAggregate  (rows=5) (actual time=38.918..38.921 rows=5 loops=1)
  ->  Hash Join  (rows=51158) (actual rows=50000 loops=1)
        ->  Bitmap Heap Scan on orders o  (rows=51158) (actual rows=50000 loops=1)
        ->  Hash  (rows=20000) (actual rows=20000 loops=1)
              ->  Seq Scan on customers c
Execution Time: 38.951 ms

The estimate is now 51,158 against 50,000 actual. With the right numbers, the planner switched to a hash join and the query ran more than twice as fast. On production-sized tables the gap is often seconds against minutes.

Stale statistics are the most common cause of misestimates, but not the only one. Others to know:

  • Skewed columns. By default PostgreSQL keeps up to 100 most-common values and 100 histogram buckets per column. If that doesn't capture a column's distribution, raise the target with ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500; ANALYZE orders;.
  • Related columns. The planner assumes conditions are independent. WHERE city = 'Paris' AND country = 'FR' gets underestimated, because it multiplies two probabilities. CREATE STATISTICS teaches it that columns are related, as the next section shows.
  • Functions on columns. The planner can't estimate lower(email) = ... well without an expression index, which comes with its own statistics.

Here's the city and country case, tested on PostgreSQL 18 with 100,000 addresses spread over ten cities in five countries:

SQL
CREATE TABLE addresses (
  id       int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  city     text NOT NULL,
  country  text NOT NULL
);
INSERT INTO addresses (city, country)
SELECT p.city, p.country
FROM generate_series(1, 10000),
     (VALUES ('Paris', 'FR'), ('Lyon', 'FR'), ('Berlin', 'DE'), ('Munich', 'DE'), ('Madrid', 'ES'),
             ('Seville', 'ES'), ('Rome', 'IT'), ('Milan', 'IT'), ('Lisbon', 'PT'), ('Porto', 'PT')) AS p(city, country);
ANALYZE addresses;

EXPLAIN ANALYZE SELECT * FROM addresses WHERE city = 'Paris' AND country = 'FR';
-- Seq Scan on addresses  (rows=2018) (actual rows=10000.00 loops=1)

The planner knows a tenth of rows are in Paris and a fifth are in France, so it multiplies: 100,000 × 0.1 × 0.2 = 2,000. But every Paris row is in France, so the true answer is 10,000. Tell it so:

SQL
CREATE STATISTICS addresses_city_country (dependencies) ON city, country FROM addresses;
ANALYZE addresses;

EXPLAIN ANALYZE SELECT * FROM addresses WHERE city = 'Paris' AND country = 'FR';
-- Seq Scan on addresses  (rows=10043) (actual rows=10000.00 loops=1)

The estimate went from five times too low to almost exact. Here it doesn't change the plan, but in a join the same fivefold error is what pushes the planner into the 50,000-loop nested loop from earlier. dependencies covers filters like this one. ndistinct fixes estimates for GROUP BY on several columns, and mcv keeps the most common value combinations. Extended statistics are only gathered by ANALYZE, so create them, then analyze.

Scan and join node types

You'll see the same handful of node types over and over:

NodeWhat it doesPlanner picks it when
Seq ScanReads the whole tableA large share of rows match, or the table is small
Index ScanWalks an index, then fetches each rowFew rows match
Index Only ScanAnswers from the index alone (Day 11)The index covers every needed column
Bitmap Heap ScanCollects matches from an index, then reads those pages in orderA moderate number of rows match
Nested LoopFor each outer row, looks up matchesThe outer side is small and the inner side is indexed
Hash JoinBuilds a hash table from one side, probes it with the otherLarge inputs joined on equality
Merge JoinWalks two sorted inputs togetherBoth inputs are already sorted on the join key

None of these is good or bad on its own. A nested loop is ideal for 10 rows and terrible for 50,000. What matters is whether the choice fits the real row counts, which is why estimate against actual is the first thing to check.

Another common culprit: sorts that spill to disk

Look for this line under Sort nodes and hash aggregates:

Code
Sort Method: external merge  Disk: 12848kB

The sort needed more memory than work_mem allowed (Day 2), so it wrote temporary files. The in-memory version says quicksort or top-N heapsort with a Memory figure. Raising work_mem for that session, with SET work_mem = '64MB', usually fixes it. Raise it per session or per role rather than globally, since every sort in every connection can use that much.

Buffer counts show where time goes on disk. Since PostgreSQL 18, EXPLAIN ANALYZE includes them automatically (on older versions, write EXPLAIN (ANALYZE, BUFFERS)). shared hit counts pages already in memory, and read counts pages fetched from disk. A slow node with large read numbers is I/O-bound, and a cold cache may be why it ran slower the first time.

Check yourself

Stale or inadequate statistics. Run ANALYZE on the table and recheck. If the estimate is still far off, raise the column's statistics target or add extended statistics.

Each loop is fast, but it ran 200,000 times, so the node's total time is 200,000 times what's shown. A hash join is usually the better plan at that scale, so check why the planner expected so few outer rows.

The DELETE really ran and committed. To inspect the plan of a data-changing statement safely, use plain EXPLAIN, or wrap EXPLAIN ANALYZE in BEGIN and ROLLBACK.

The columns are related, but the planner multiplies their probabilities as if they were independent. Create extended statistics, CREATE STATISTICS ... (dependencies) ON zip_code, city FROM ..., then run ANALYZE.

EXPLAIN shows the plan and the planner's estimates without running the query. EXPLAIN ANALYZE runs it and adds the actual row counts and times for each node, which is how you spot bad estimates. Add BUFFERS to see how much data came from cache versus disk.