SQL EXPLAIN Output Doesn’t Have to Look Like Alien Text

You run EXPLAIN on a slow query. The database hands you back something like this:

Hash Join  (cost=145.00..578.00 rows=3200 width=96)
  Hash Cond: (o.customer_id = c.id)
  ->  Seq Scan on orders  (cost=0.00..248.00 rows=16000 width=64)
  ->  Hash  (cost=132.50..132.50 rows=1000 width=32)
        ->  Seq Scan on customers  (cost=0.00..132.50 rows=1000 width=32)
              Filter: ((country)::text = 'US'::text)
              Rows Removed by Filter: 9000

You stare at it for ten seconds. You add an index at random. You restart the app and hope. That’s not a debugging strategy — that’s superstition with extra steps.

EXPLAIN is actually telling you something specific and actionable. Here’s how to read it.

EXPLAIN vs EXPLAIN ANALYZE: Which one to use

سهل EXPLAIN shows you what the planner intends to do. It never executes the query — it just shows the plan with estimated costs. Fast, but the estimates can be wrong.

EXPLAIN ANALYZE actually runs the query and adds the real timings. You get both the plan and the actuals:

-- PostgreSQL: run the query and show real timings
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders
JOIN customers ON orders.customer_id = customers.id
WHERE customers.country = 'US';

-- MySQL equivalent
EXPLAIN ANALYZE SELECT * FROM orders
JOIN customers ON orders.customer_id = customers.id
WHERE customers.country = 'US';

ال BUFFERS option is PostgreSQL-only and adds buffer hit/miss counts — useful for diagnosing I/O problems, but ignore it for now.

One caveat: EXPLAIN ANALYZE runs the query for real. On a DELETE أو UPDATE, wrap it in a transaction and roll back:

BEGIN;
EXPLAIN ANALYZE DELETE FROM orders WHERE status = 'pending';
ROLLBACK;

The cost numbers: what they mean and what they don’t

Every node in the plan shows (cost=X..Y rows=N width=W). People see these numbers and assume they’re milliseconds. They’re not.

• cost=X..Y — X is the startup cost (work done before the first row is returned), Y is the total cost to process all rows. The unit is arbitrary “page cost” — roughly proportional to disk page reads, but calibrated by PostgreSQL’s cost constants. A cost of 1.0 is one sequential page read.
• rows=N — the planner’s estimated row count. Can be wildly wrong if table statistics are stale.
• width=W — average row size in bytes. Wider rows slow down joins and sorts.

When you run EXPLAIN ANALYZE, each node also gets (actual time=X..Y rows=N loops=L). These are milliseconds. A big gap between estimated and actual rows is the most reliable signal that the planner made a bad decision.

An annotated EXPLAIN ANALYZE walkthrough

Here’s the same query from the intro, this time with ANALYZE and with each line explained:

Hash Join  (cost=145.00..578.00 rows=3200 width=96)
           (actual time=2.451..12.879 rows=3168 loops=1)
-- Top-level node. Joins orders and customers via hashing.
-- cost estimate: 145..578 | actual: 2.4ms startup, 12.9ms total
-- rows estimate: 3200 | actual: 3168 — pretty close here

  Hash Cond: (o.customer_id = c.id)
  -- This is the join key. The planner built a hash table on customers.id
  -- then probed it with every row from orders.

  ->  Seq Scan on orders  (cost=0.00..248.00 rows=16000 width=64)
                          (actual time=0.019..3.125 rows=16000 loops=1)
  -- Full table scan on orders. 16,000 rows scanned.
  -- No filter here — we pull all orders and join them below.
  -- This is expected: we need all orders, so no index would help here.

  ->  Hash  (cost=132.50..132.50 rows=1000 width=32)
            (actual time=2.412..2.413 rows=1000 loops=1)
            Buckets: 1024  Batches: 1  Memory Usage: 64kB
  -- Builds the in-memory hash table from the customers result.
  -- 1 batch = fits in work_mem. Multiple batches = spilling to disk (bad).

        ->  Seq Scan on customers  (cost=0.00..132.50 rows=1000 width=32)
                                   (actual time=0.012..1.345 rows=1000 loops=1)
              Filter: ((country)::text = 'US'::text)
              Rows Removed by Filter: 9000
        -- Scanned all 10,000 customer rows. Kept 1,000 where country='US'.
        -- *** This is the expensive part. An index on customers.country
        --     would let PostgreSQL skip the 9,000 discarded rows entirely. ***

Planning Time: 0.187 ms
Execution Time: 13.451 ms

The action item jumps out immediately: Rows Removed by Filter: 9000 on a full table scan is the most common pattern for “you need an index here.”

The node types you’ll actually encounter

Seq Scan

Reads every row in the table, in heap order. People see this and immediately want to add an index. But a Seq Scan isn’t always wrong — if your filter matches 20%+ of the rows, the planner correctly decides that random index lookups would be slower. A Seq Scan on a 500-row table is fine. A Seq Scan on a 10-million-row table with a highly selective filter is a problem.

Index Scan

Uses a B-tree index to look up matching rows, then fetches each row from the heap. The heap fetch is the expensive part — each row is a random I/O. Good when the filter is selective (i.e., you’re fetching a small fraction of rows).

Index Only Scan

The query can be answered entirely from the index without touching the heap. This requires that every column in your SELECT و WHERE clause is covered by the index. Fastest type of scan — if you see this, the index design is doing its job.

Bitmap Heap Scan

A middle ground. The planner builds a bitmap of which heap pages contain matching rows (via Bitmap Index Scan), then fetches only those pages in order, reducing random I/O. Common when an Index Scan would have too many random reads but a Seq Scan would scan too many unneeded rows.

Hash Join

Builds a hash table from the smaller input, then probes it with every row from the larger input. Good for large joins without a useful sort order. Watch for Batches: > 1 in the Hash node — that means the hash table spilled to disk because it exceeded work_mem. Raising work_mem for that session often fixes it.

Nested Loop

For each row on the outer side, probe the inner side (usually via an index). Excellent when one side is tiny — O(n) instead of O(n log n). Terrible when both sides are large, because it runs the inner scan once per outer row. If you see a Nested Loop with loops=50000, that inner scan is running 50,000 times.

Merge Join

Both inputs arrive sorted on the join key; the planner walks them in parallel. Efficient but requires the sort upfront. You’ll see this when both sides already have an index on the join key, or when the planner decides a Sort node is cheaper than hashing.

Why the planner chooses Seq Scan over Index Scan

This confuses people most. You have an index. The query is slow. EXPLAIN shows a Seq Scan anyway.

The planner uses column statistics — stored in pg_statistic, updated by ANALYZE — to estimate how many rows a filter will return. If it estimates 30% of the table will match, a Seq Scan is genuinely cheaper than 300,000 random index lookups. Random I/O is expensive.

The threshold where the planner prefers a Seq Scan is roughly 10–20% selectivity, depending on your hardware and cost constants. You can check the planner’s estimate vs reality:

-- Check the planner's selectivity estimate
EXPLAIN SELECT * FROM orders WHERE status = 'pending';

-- Compare with actual count
SELECT COUNT(*) FROM orders WHERE status = 'pending';
SELECT COUNT(*) FROM orders;

If the planner estimated 8,000 rows but there are only 80, that’s a stale statistics problem. Run ANALYZE orders; and re-check the plan. This fixes bad plans more often than adding indexes.

Reading EXPLAIN in JSON format

PostgreSQL can output the plan as JSON, which is easier to parse programmatically or explore in a tree view:

EXPLAIN (ANALYZE, FORMAT JSON) SELECT * FROM orders
JOIN customers ON orders.customer_id = customers.id
WHERE customers.country = 'US';

The JSON output is dense. If you paste it into IO Tools’ JSON Formatter, it becomes a navigable tree — useful when dealing with deeply nested plans from complex queries with multiple subqueries or CTEs.

A practical debugging workflow

When a query is slow, follow this sequence rather than guessing:

1. Run EXPLAIN (ANALYZE, BUFFERS) on the exact query with real production-like parameters (not $1 placeholders — use the actual values).
2. Find the most expensive node. Look for the highest actual time, not the highest cost estimate. Those can differ.
3. Compare estimated vs actual rows at that node. A 10x+ discrepancy means the planner was working with bad information. Run ANALYZE <table>; first.
4. Look for Seq Scans on large tables with selective filters. Rows Removed by Filter: <large number> right below a Seq Scan is your index candidate.
5. Check Hash nodes for Batches > 1. If present, the join spilled to disk. Increase work_mem for that session and re-test.
6. Check Nested Loop with high loop counts. A loop count in the thousands means the inner scan is getting hammered. An index on the inner table’s join column usually fixes this.

Before writing any new indexes, also run your queries through IO Tools’ SQL Formatter — readable queries are easier to analyze, and sometimes a rewrite eliminates the problem entirely.

Understanding the plan is the first move, not the index. Once you can read EXPLAIN output, you’ll spend less time guessing and more time making targeted changes that actually work.

قد يعجبك أيضاً