DBMS

Query Processing And Optimization

Reading and Fixing Query Plans

EXPLAIN SELECT ...; -- the PLAN and ESTIMATES. Does not run the query.

JrCodex·8 min read

Jr Codex DBMS Notes

Level: Advanced Prerequisites: Chapter 3: Cost-Based Optimization Time to complete: ~25 minutes

Table of Contents

  1. EXPLAIN vs EXPLAIN ANALYZE
  2. Reading the Output
  3. The Estimate-vs-Actual Method
  4. The Six Red Flags
  5. A Worked Fix
  6. The Procedure
  7. Summary & Next Steps

1. EXPLAIN vs EXPLAIN ANALYZE

EXPLAIN SELECT ...;          -- the PLAN and ESTIMATES. Does not run the query.
EXPLAIN ANALYZE SELECT ...;  -- RUNS it, and reports actual rows and times.
Use ANALYZE, With One Caution
─────────────────────────────────────────
  EXPLAIN alone shows what the optimiser BELIEVES.
  EXPLAIN ANALYZE shows what actually happened.

  Only the second lets you compare the two, which
  is the entire diagnostic method (Section 3).

  CAUTION: EXPLAIN ANALYZE EXECUTES the statement.
  On an UPDATE or DELETE, wrap it:

    BEGIN;
    EXPLAIN ANALYZE UPDATE ...;
    ROLLBACK;
─────────────────────────────────────────

-- The full-detail form, worth memorising.
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT)
SELECT s.name, c.title
FROM students s
JOIN enrollments e ON s.id = e.student_id
JOIN courses    c ON e.course_id = c.id
WHERE s.city = 'Pune';

BUFFERS is the one people omit and should not — it reports pages hit in cache versus read from disk, which is Module 5, Chapter 5's hit rate for this specific query.

2. Reading the Output

A Real Plan, Annotated
─────────────────────────────────────────
  Hash Join  (cost=1.20..25.40 rows=12 width=64)
             (actual time=0.089..0.213 rows=9 loops=1)
    Hash Cond: (e.course_id = c.id)
    Buffers: shared hit=14
    ->  Hash Join  (cost=0.55..24.10 rows=12 width=40)
                   (actual time=0.041..0.140 rows=9 loops=1)
          Hash Cond: (e.student_id = s.id)
          ->  Seq Scan on enrollments e
                   (cost=0.00..18.00 rows=800 width=12)
                   (actual time=0.008..0.042 rows=12 loops=1)
          ->  Hash  (cost=0.50..0.50 rows=3 width=36)
                    (actual time=0.019..0.019 rows=3 loops=1)
                ->  Index Scan using idx_city on students s
                        (cost=0.15..0.50 rows=3 width=36)
                        (actual time=0.011..0.014 rows=3 loops=1)
                      Index Cond: (city = 'Pune')
    ->  Hash  ...
─────────────────────────────────────────

What Each Number Means
─────────────────────────────────────────
  cost=1.20..25.40
       START-UP cost .. TOTAL cost.
       Start-up is the work before the FIRST row.
       A blocking operator (Chapter 1) has a large
       start-up cost.

  rows=12          ESTIMATED rows out
  width=64         estimated bytes per row

  actual time=0.089..0.213
       time to first row .. time to last row,
       in MILLISECONDS

  rows=9           ACTUAL rows out
  loops=1          how many times this node RAN

  Buffers: shared hit=14   pages found in cache
           read=...        pages read from disk
─────────────────────────────────────────

The loops Trap
─────────────────────────────────────────
  Reported times and rows are PER LOOP, averaged.

  A node showing rows=5 loops=10000 produced
  50,000 rows in total, and took 10,000 × its
  reported time.

  Always multiply by loops before deciding a node
  is cheap. This is the most common misreading of
  a plan.
─────────────────────────────────────────

3. The Estimate-vs-Actual Method

The Method, in Four Steps
─────────────────────────────────────────
  1. Read BOTTOM-UP. The deepest node runs first.

  2. At each node, compare
        estimated rows  vs  actual rows × loops

  3. Find the FIRST node, from the bottom, where
     they diverge by more than ~10x.

  4. THAT node is the problem. Everything above it
     was planned on a wrong number, so fix it first
     and re-check.
─────────────────────────────────────────

Why the FIRST Divergence
─────────────────────────────────────────
  Estimation errors compound upward (Chapter 3).

  A join whose estimate is 100x wrong is often
  merely inheriting a 100x error from a scan
  beneath it. Fixing the join is pointless; fixing
  the scan fixes both.

  Work from the bottom, fix one thing, re-run.
─────────────────────────────────────────

Example of a Divergence
─────────────────────────────────────────
  ->  Seq Scan on orders
        (cost=0.00..18000 rows=50 width=40)
        (actual time=0.02..890 rows=2100000 loops=1)
      Filter: (status = 'pending' AND region = 'APAC')

  Estimated 50. Actual 2,100,000. A 42,000x error.

  Two correlated columns, independence assumed
  (Chapter 3, failure mode 1). Everything above
  this node was planned for 50 rows and is now
  processing two million.
─────────────────────────────────────────

4. The Six Red Flags

1. SEQ SCAN ON A LARGE TABLE WITH A SELECTIVE FILTER
─────────────────────────────────────────
   Missing index, or a non-sargable condition
   (Module 5, Chapter 4).
   NOT a flag when most of the table matches.

2. NESTED LOOP WITH A LARGE OUTER SIDE
─────────────────────────────────────────
   loops=500000 on the inner node.
   Usually an under-estimate that made the
   optimiser think the outer side was tiny
   (Chapter 2).

3. ROWS REMOVED BY FILTER, LARGE
─────────────────────────────────────────
   "Rows Removed by Filter: 4980000"
   The database read five million rows to return
   twenty thousand. The filter should have been an
   index condition, not a post-scan filter.

4. EXTERNAL MERGE / DISK SPILL
─────────────────────────────────────────
   "Sort Method: external merge  Disk: 84320kB"
   A blocking operator exceeded work_mem
   (Chapter 1). Raise work_mem, or reduce the rows
   reaching it.

5. HASH JOIN WITH BATCHES > 1
─────────────────────────────────────────
   "Buckets: 4096  Batches: 16  Memory Usage: ..."
   The hash build spilled to disk (Chapter 2).
   Same two fixes.

6. ESTIMATE-VS-ACTUAL DIVERGENCE > 10x
─────────────────────────────────────────
   The root cause behind flags 2, 4 and 5 far more
   often than not. Fix this and they frequently
   disappear on their own.
─────────────────────────────────────────

5. A Worked Fix

-- The slow query: 8.4 seconds.
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending'
  AND o.region = 'APAC'
ORDER BY o.created_at DESC
LIMIT 50;
The Plan, Diagnosed
─────────────────────────────────────────
  Limit (actual time=8402 rows=50)
    -> Sort (actual time=8402 rows=50)
         Sort Method: external merge  Disk: 84320kB   ← FLAG 4
         -> Nested Loop (actual rows=2100000)         ← FLAG 2
              -> Seq Scan on orders                    ← FLAG 1
                   (cost rows=50)(actual rows=2100000) ← FLAG 6, the ROOT CAUSE
                   Filter: status='pending' AND region='APAC'
                   Rows Removed by Filter: 7900000     ← FLAG 3
              -> Index Scan on customers (loops=2100000)

  Five flags, ONE cause: the estimate of 50 rows
  where 2,100,000 were returned.
─────────────────────────────────────────

-- FIX 1: tell the optimiser these columns are correlated (Chapter 3).
CREATE STATISTICS stat_orders_status_region (dependencies)
    ON status, region FROM orders;
ANALYZE orders;
 
-- FIX 2: give it an index that serves the filter AND the ordering.
CREATE INDEX idx_orders_pending ON orders (region, created_at DESC)
WHERE status = 'pending';                     -- PARTIAL — Module 5, Chapter 3
The New Plan: 3 milliseconds
─────────────────────────────────────────
  Limit (actual time=0.08..2.9 rows=50)
    -> Nested Loop (actual rows=50)
         -> Index Scan using idx_orders_pending on orders
              (actual rows=50 loops=1)
              Index Cond: (region = 'APAC')
         -> Index Scan on customers (loops=50)

  What changed:
    - the partial index made the filter an INDEX
      CONDITION, not a post-scan filter
    - created_at DESC in the index removed the SORT
      entirely
    - LIMIT 50 now stops the scan after 50 rows
      (Chapter 1's streaming), so loops fell from
      2,100,000 to 50
─────────────────────────────────────────

6. The Procedure

Fixing a Slow Query
─────────────────────────────────────────
  1. EXPLAIN (ANALYZE, BUFFERS) the query.

  2. Find the FIRST bottom-up node where
     estimated ≠ actual × loops by >10x.

  3. Ask why that estimate is wrong:
       stale stats?      ──► ANALYZE
       correlated cols?  ──► CREATE STATISTICS
       an expression?    ──► expression index
       skewed values?    ──► raise the statistics
                             target

  4. Re-run EXPLAIN ANALYZE. Often the plan is now
     correct and you are finished.

  5. Still slow? Now look at ACCESS PATHS. Is there
     an index that would turn a filter into an
     index condition, and supply the ORDER BY?

  6. Still slow? Consider rewriting — aggregate
     earlier, select fewer columns, avoid the
     fan-out (Module 3, Chapter 5).

  7. Only then consider denormalising (Module 4,
     Chapter 5).
─────────────────────────────────────────

The Habit Worth Building
─────────────────────────────────────────
  Never add an index because a query is slow.

  Add an index because you read the plan, saw a
  sequential scan with a selective filter or a sort
  that should not exist, and know precisely which
  node the index will change.

  Then re-run the plan and confirm it did.
─────────────────────────────────────────

7. Summary & Next Steps

Key Takeaways

  • EXPLAIN ANALYZE with BUFFERS shows what actually happened, and only comparing it to the estimates gives you a diagnosis.
  • Reported rows and times are per loop, so a node showing rows=5 loops=10000 did 50,000 rows of work — the most common misreading of a plan.
  • Find the first bottom-up node where estimate and actual diverge by more than 10x; errors compound upward, so everything above it is inheriting that mistake.
  • Most of the six red flags are symptoms of one bad estimate, and fixing the estimate often removes several of them at once.

Module 6 Complete — What's Next

You can now read a plan, locate the wrong estimate, and fix it deliberately. Module 7 turns to what happens when many users run these queries simultaneously — and why the answer is harder than it looks.

Concept Check

  1. Why must you multiply a node's reported rows by its loops value before judging it?
  2. In the worked example, five red flags had one cause. What was it, and why did fixing it clear the others?
  3. Why should you diagnose the first divergence bottom-up rather than the largest one?

Next Module

→ Module 7: Transactions and Concurrency

Jr Codex — 1-on-1 Personalized Coaching | Back to Module Index | Back to DBMS Index