absolutelearners.com

Reading EXPLAIN — Query Plans from Start to Finish

Reading EXPLAIN — Query Plans from Start to Finish

EXPLAIN is the most powerful debugging tool in PostgreSQL. Every slow query has a reason — wrong index choice, bad row estimate, unneeded sequential scan, inefficient join — and EXPLAIN tells you exactly what the query planner decided and why. Without it, optimisation is guesswork. With it, optimisation is systematic.

The two forms

  • EXPLAIN query — shows the estimated plan without executing
  • EXPLAIN (ANALYZE, BUFFERS) query — executes the query and shows actual timing and row counts alongside estimates

Always use EXPLAIN (ANALYZE, BUFFERS) in development and staging. Use plain EXPLAIN in production when you cannot afford to actually run the query.

The plan tree

Every EXPLAIN output is a tree of nodes. Each node represents one operation. The tree reads bottom-up (innermost nodes execute first). Each node shows:

  • Node type (Seq Scan, Index Scan, Hash Join, etc.)
  • cost=start_cost..total_cost — estimated cost in arbitrary planner units; compare within the same plan, not across different queries
  • rows=N — estimated rows output by this node
  • width=N — estimated average row width in bytes
  • actual time=start..end (with ANALYZE) — milliseconds
  • actual rows=N (with ANALYZE) — the actual row count
  • loops=N (with ANALYZE) — how many times this node was executed

Example 1: Reading a simple plan

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT c.company_name, COUNT(i.invoice_id) AS invoices
FROM clients c
JOIN invoices i ON i.client_id = c.client_id
WHERE c.industry = 'Software'
GROUP BY c.company_name
ORDER BY invoices DESC;

Example output:

Sort  (cost=85.20..85.45 rows=100 width=40) (actual time=2.1..2.1 rows=8 loops=1)
  Sort Key: (count(i.invoice_id)) DESC
  Sort Method: quicksort  Memory: 25kB
  ->  HashAggregate  (cost=80.00..81.00 rows=100 width=40) (actual time=2.0..2.0 rows=8 loops=1)
        Group Key: c.company_name
        ->  Hash Join  (cost=15.00..75.00 rows=200 width=32) (actual time=0.5..1.8 rows=45 loops=1)
              Hash Cond: (i.client_id = c.client_id)
              Buffers: shared hit=12
              ->  Seq Scan on invoices i  (cost=0.00..45.00 rows=800 width=8) (actual time=0.1..0.8 rows=200 loops=1)
              ->  Hash  (cost=12.00..12.00 rows=240 width=28) (actual time=0.3..0.3 rows=8 loops=1)
                    Buckets: 1024  Batches: 1  Memory Usage: 9kB
                    ->  Seq Scan on clients c  (cost=0.00..12.00 rows=8 width=28) (actual time=0.1..0.2 rows=8 loops=1)
                          Filter: (industry = 'Software')
                          Rows Removed by Filter: 22

How to read this:

  • Start at the innermost indented nodes (bottom of tree)
  • Seq Scan on clients: scanned all 30 rows of clients, filtered to 8 rows for 'Software'
  • Hash: loaded those 8 rows into a hash table (9kB)
  • Seq Scan on invoices: scanned all 200 invoices
  • Hash Join: for each invoice row, looked up client_id in the hash table → 45 matching rows
  • HashAggregate: grouped the 45 rows by company_name → 8 groups
  • Sort: sorted the 8 groups by count descending

The cost=80..81 total for HashAggregate and actual time=2.0..2.0 are close — the planner's estimate was accurate.

Example 2: Identifying bad estimates — the root cause of slow queries

The most important thing EXPLAIN tells you: when estimated rows differs dramatically from actual rows. A bad estimate causes the planner to choose the wrong join algorithm, wrong index, or wrong join order.

-- Create an index selectivity problem
EXPLAIN (ANALYZE)
SELECT o.order_id, o.customer_id, o.created_at, o.status,
       SUM(oi.quantity * oi.unit_price) AS order_total
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status = 'completed'
GROUP BY o.order_id, o.customer_id, o.created_at, o.status
HAVING SUM(oi.quantity * oi.unit_price) > 1000;

If the planner estimates 100 rows but actually returns 50,000 rows, it means the statistics are stale. Fix with:

-- Update statistics for the table
ANALYZE orders;

-- Or increase the statistics target for specific columns
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
ANALYZE orders;

After ANALYZE, re-run EXPLAIN and check whether the row estimates improved. Better estimates lead to better plan choices automatically.

Example 3: Understanding join types

Different join implementations suit different situations:

-- Compare three join strategies using enable_* settings

-- Force a Nested Loop join (fastest when outer table is tiny and inner has an index)
SET enable_hashjoin = OFF;
SET enable_mergejoin = OFF;
EXPLAIN SELECT * FROM clients c JOIN invoices i ON i.client_id = c.client_id
WHERE c.client_id = 1;
-- With an index on invoices.client_id, this is ideal: probe index once per outer row

-- Force Hash Join (best for large tables, no usable index on join key)
SET enable_hashjoin = ON;
SET enable_nestloop = OFF;
EXPLAIN SELECT * FROM clients c JOIN invoices i ON i.client_id = c.client_id;
-- Loads smaller table into hash, scans larger table

-- Merge Join (best when both sides are already sorted on the join key)
SET enable_nestloop = OFF;
SET enable_hashjoin = OFF;
EXPLAIN SELECT * FROM invoices i JOIN payments p ON p.invoice_id = i.invoice_id ORDER BY i.invoice_id;

-- Always reset after testing
RESET enable_hashjoin;
RESET enable_mergejoin;
RESET enable_nestloop;

When to expect each join type:

  • Nested Loop: small outer relation OR indexed inner lookup
  • Hash Join: large unindexed join, both sides fit in work_mem
  • Merge Join: both sides already sorted or can be sorted cheaply

Example 4: EXPLAIN FORMAT JSON for programmatic analysis

-- Get plan as JSON for tooling (pgBadger, explain.depesz.com, etc.)
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT o.order_id, c.country, SUM(oi.quantity * oi.unit_price) AS total
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY o.order_id, c.country;

Paste the JSON output to explain.depesz.com or explain.dalibo.com for a visual representation of the plan tree with colour-coded performance indicators.

Key Takeaway

Read EXPLAIN bottom-up (leaf nodes execute first). The critical comparison is estimated rows vs actual rows — large discrepancies cause bad plan choices. ANALYZE updates statistics and often fixes bad estimates without any schema changes. Join type (Nested Loop / Hash / Merge) depends on table size, available indexes, and sort order. Always use EXPLAIN (ANALYZE, BUFFERS) — plain EXPLAIN only estimates, it doesn't show you what actually happened.

← Previous Next →