Reading EXPLAIN output

You know from Day 1 that an index makes reads faster, and that the planner sometimes chooses not to use it (low selectivity, leading wildcards). But how do you know which one is happening? You don’t guess — you ask the database directly with EXPLAIN.

What EXPLAIN does

EXPLAIN shows you the execution plan the database planner chose for your query — without actually running it. It tells you: will this query do a sequential scan or use an index? How many rows does the planner estimate it will touch? What’s the cost?

Add ANALYZE and the database actually runs the query and gives you real timing data:

-- Without running: just the plan
EXPLAIN SELECT * FROM users WHERE email = 'frank@x.com';

-- With running: real timing
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'frank@x.com';

The three things you’re looking for

When you read EXPLAIN output, you’re looking for three things:

1. Scan type — Seq Scan (bad, reads every row) vs Index Scan / Index Only Scan (good, uses the index). Bitmap Heap Scan is in between — it uses the index to find candidate rows, then fetches them.
2. Rows estimate — how many rows the planner thinks it will process. If this is close to the total row count, a seq scan is probably fine. If it’s a tiny fraction, you want an index.
3. Cost — the planner’s cost estimate (arbitrary units, not milliseconds). The first number is startup cost, the second is total cost. Lower is better, but the absolute numbers don’t matter — comparing before and after adding an index is what matters.

The worked example: before and after an index

Real output from a users table with 1,000,000 rows (median of 30 runs). You search for one email:

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user500000@example.com';

Without an index on email:

Seq Scan on users  (cost=0.00..19023.30 rows=4669 width=36)
                    (actual time=153.581..316.388 rows=1 loops=1)
  Filter: (email = 'user500000@example.com'::text)
  Rows Removed by Filter: 999999
  Buffers: shared hit=7352
Planning Time: 0.507 ms
Execution Time: 316.532 ms

Read this line by line:

• Seq Scan — the database is reading every single row. Bad.
• rows=1 — only one row matches (actual). The database read 1,000,000 rows to find 1.
• Rows Removed by Filter: 999999 — it checked and rejected 999,999 rows. This is wasted work.
• Buffers: shared hit=7352 — it touched 7,352 buffer pages. Every page of the table.
• Execution Time: 316.532 ms — this run took 317ms. Across 30 runs, the median was 244ms.

Now add an index and run the same query:

CREATE INDEX idx_users_email ON users(email);

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user500000@example.com';

Index Scan using idx_users_email on users
                    (cost=0.42..8.44 rows=1 width=26)
                    (actual time=0.104..0.115 rows=1 loops=1)
  Index Cond: (email = 'user500000@example.com'::text)
  Buffers: shared hit=1 read=3
Planning Time: 0.445 ms
Execution Time: 0.184 ms

What changed:

• Index Scan — the database used the index. No more reading every row.
• Index Cond — the index was used to find the matching row directly.
• Buffers: shared hit=1 read=3 — only 4 buffer pages touched, vs 7,352 before.
• No “Rows Removed by Filter” — the index went straight to the right row. Zero wasted work.
• Execution Time: 0.184 ms — this run. Across 30 runs, the median was 0.134 ms. Down from 244 ms median — a 1,822× speedup.

The four scan types you need to know

In order from worst to best for a point lookup (finding a specific row):

1. Seq Scan — reads every row. Fine for small tables or when you need most of the rows anyway. Bad for finding one row in a million.
2. Bitmap Heap Scan — the index finds candidate row locations, then the database fetches those rows in a batch. Middle ground: used when the index returns enough rows that fetching them one-by-one would be inefficient, but not so many that a seq scan is better.
3. Index Scan — the index finds the row, and the database fetches it from the table. Good for selective queries that return few rows.
4. Index Only Scan — the index has all the columns the query needs, so the database never touches the table at all. Fastest possible read. This is why you sometimes add columns to an index (a “covering index”).

The transfer questions

From Day 1 → Day 3: If you add an index and EXPLAIN still shows a Seq Scan, what are three possible reasons?

Answer (click to reveal)

1. Low selectivity — the column has few distinct values (e.g. boolean active=true matches 80% of rows). The planner decides a seq scan is cheaper than using the index.
2. Function on the column — WHERE LOWER(email) = ‘frank’ can’t use a standard B-tree index on email. The index is on the raw value, not the lowercased value. Fix: use an expression index or rewrite the query.
3. Table is too small — for tiny tables (a few hundred rows), a seq scan is genuinely faster than an index lookup. The planner is smart. This is not a problem.

From Day 2 → Day 3: How does EXPLAIN connect to caching?

Answer (click to reveal)

You’d run EXPLAIN ANALYZE to measure a query’s cost and execution time before deciding whether to cache its results. If the query takes 200ms and runs 1,000 times/second, that’s a caching candidate. EXPLAIN tells you why the query is slow (seq scan? no index? function on column?) so you can decide: fix the query with an index, or skip the query entirely with a cache.

There’s also EXPLAIN (ANALYZE, BUFFERS) — it shows buffer hit/miss counts, which tells you how much data came from PostgreSQL’s internal cache vs disk. High buffer hit ratio = the data is already cached in PostgreSQL’s shared buffers. This is the bridge between indexes, caching, and EXPLAIN.

Check your understanding