Phase 2 · Chapter 4

Query Optimization: EXPLAIN, Indexes & the Query Planner

How Postgres decides to execute your SQL — and how to fix it when it decides wrong

Every SQL statement you write is a declaration of what you want, not how to get it. Between your query and the rows it returns sits the query planner — a piece of software that examines your query, looks at statistics about your data, and constructs an execution plan it believes is optimal. Most of the time it is right. When it is wrong, queries that should take milliseconds take seconds, and the database becomes the bottleneck for your entire product. After this chapter you will be able to read EXPLAIN ANALYZE output, understand why the planner chooses each operation it chooses, identify the specific conditions that cause bad plans, and fix slow queries by giving the planner better information or restructuring the query so a good plan becomes possible. This is the skill that separates engineers who blame the database from engineers who fix it.

Part 1: What the Query Planner Actually Does

When Postgres receives a SQL query, it goes through four stages before returning rows:

Parse: Convert SQL text into an abstract syntax tree. Syntax errors are caught here.

Rewrite: Apply rule-based transformations. Views are expanded into their underlying queries here.

Plan: This is the expensive stage. The planner generates multiple possible execution strategies for the query and estimates the cost of each one. It chooses the plan with the lowest estimated cost. This is where almost everything interesting happens.

Execute: Run the chosen plan against the actual data pages.

The planner's cost model is built on statistics about your data — not the actual data, but summaries of it. Postgres maintains these statistics in the pg_statistic system table and refreshes them during ANALYZE (which autovacuum runs automatically). The statistics include: how many rows each table has, how many distinct values each column has, the most common values and their frequencies, and histograms of value distributions.

The planner uses these statistics to estimate how many rows each operation will produce — its row estimate. The row estimate drives everything else: which index to use, whether a hash join or a nested loop join is faster, whether to sort in memory or on disk. A bad row estimate produces a bad plan.

This is the single most important thing to understand about query optimization: the planner is only as good as its statistics, and statistics are estimates, not counts. When statistics are stale or when data distributions are unusual, the planner makes wrong estimates and chooses wrong plans. Your job is to understand when this is happening and what to do about it.

Part 2: Reading EXPLAIN ANALYZE

EXPLAIN shows you the plan the planner chose. EXPLAIN ANALYZE executes the query and shows you the plan plus actual runtime measurements. Always use EXPLAIN ANALYZE for real optimization work — EXPLAIN without ANALYZE shows estimates only, which can be misleading.

EXPLAIN ANALYZE
SELECT u.name, COUNT(a.id) as attempt_count
FROM users u
JOIN test_attempts a ON a.user_id = u.id
WHERE u.subscription_tier = 'pro'
GROUP BY u.id, u.name;

Output (simplified):

HashAggregate  (cost=4821.50..4923.50 rows=10200 width=45)
               (actual time=89.234..91.102 rows=8934 loops=1)
  ->  Hash Join  (cost=312.00..4519.00 rows=60500 width=37)
                 (actual time=12.451..78.334 rows=89340 loops=1)
        Hash Cond: (a.user_id = u.id)
        ->  Seq Scan on test_attempts a
              (cost=0.00..3201.00 rows=210000 width=8)
              (actual time=0.021..41.234 rows=210000 loops=1)
        ->  Hash  (cost=274.00..274.00 rows=3040 width=37)
                  (actual time=12.112..12.112 rows=3040 loops=1)
              ->  Seq Scan on users u
                    (cost=0.00..274.00 rows=3040 width=37)
                    Filter: (subscription_tier = 'pro')
                    Rows Removed by Filter: 8960
                    (actual time=0.018..11.987 rows=3040 loops=1)

Read this bottom-up. The innermost nodes execute first. Each node feeds rows up to its parent.

The anatomy of each node:

cost=0.00..274.00 — estimated cost. First number: startup cost (cost before first row is returned). Second number: total cost. These are in arbitrary planner units, not milliseconds. They are useful for comparison between plans, not for predicting actual duration.

rows=3040 — estimated number of rows this node will return.

width=37 — estimated average row size in bytes.

actual time=0.018..11.987 — actual wall-clock time in milliseconds. First number: time to first row. Second number: total time.

rows=3040 in the actual section — actual rows returned. When estimated rows and actual rows differ significantly, the planner had bad statistics and may have chosen a suboptimal plan.

loops=1 — how many times this node executed. In a nested loop join, an inner node may execute once per outer row. If loops=1000 and actual time=0.1, total time for this node is 100ms.

The most important diagnostic: rows estimate vs actual

The single most useful comparison in any EXPLAIN ANALYZE output:

estimated rows=3040   actual rows=3040    ← planner had good information, plan is likely optimal
estimated rows=100    actual rows=89340   ← planner underestimated by 893x, plan may be wrong
estimated rows=50000  actual rows=12      ← planner overestimated, chose wrong join strategy

A large discrepancy between estimated and actual rows is always a red flag. It means the planner built its plan on wrong assumptions. Even if the query is fast today, a change in data distribution could make the bad plan catastrophically worse.

Part 3: The Three Scan Types

Postgres has three ways to read rows from a table. The planner chooses between them based on estimated selectivity — what fraction of the table's rows does this query need?

Sequential scan (Seq Scan)

Read every 8KB page of the table from disk, in order. Return rows that match the filter condition. Discard rows that do not match.

Seq Scan on users  (cost=0.00..274.00 rows=3040 width=37)
  Filter: (subscription_tier = 'pro')
  Rows Removed by Filter: 8960

Sequential scan is not always bad. For small tables (fits in shared_buffers) or queries that need most of the table's rows, a sequential scan is faster than an index scan — the overhead of index lookups exceeds the cost of just reading all the pages. The planner chooses sequential scan when it estimates that the query needs more than roughly 5-15% of the table (the exact threshold depends on costs and page cache state).

A sequential scan on a table with 10 million rows for a query that needs 10 rows is catastrophic. This is the signal to add an index.

Index scan

Use a B-tree (or other index) to find the exact page and offset of rows matching the condition, then fetch only those rows.

Index Scan using idx_users_email on users
  (cost=0.42..8.44 rows=1 width=164)
  Index Cond: (email = 'user@example.com')

For highly selective queries (email lookup, ID lookup, any condition that matches a small fraction of rows), the index scan reads only the pages containing matching rows. The cost is: traverse the B-tree (O(log n) pages) plus one page fetch per matching row.

The important detail: an index scan reads the index first, gets the heap tuple location, then fetches the heap page. This is two I/O operations per row. For queries returning many rows scattered across many pages, this becomes expensive — the planner may prefer a sequential scan.

Bitmap index scan

A middle path between sequential scan and index scan. Postgres reads the entire index to build a bitmap of which heap pages contain matching rows, then reads those heap pages in order (sorted by page number, avoiding random I/O).

Bitmap Heap Scan on test_attempts
  Recheck Cond: (user_id = 12345)
  ->  Bitmap Index Scan on idx_attempts_user_id
        Index Cond: (user_id = 12345)

The bitmap scan is chosen when a query matches enough rows that random-access index scans would be slow, but not so many rows that a sequential scan is better. It also enables combining multiple indexes: WHERE status = 'active' AND created_at > '2025-01-01' can use a bitmap AND of two separate indexes, which is often faster than a single composite index for certain data distributions.

Part 4: Join Strategies

When a query joins two tables, Postgres has three strategies. The right choice depends on the sizes of the tables, the selectivity of the join condition, and available indexes.

Nested loop join

For each row in the outer table, scan the inner table for matching rows. If the inner table has an index on the join key, this is very efficient for small outer tables.

cost = outer_rows × inner_lookup_cost

With 10 outer rows and an indexed inner table: 10 index lookups — fast. With 10,000 outer rows and an indexed inner table: 10,000 index lookups — still manageable. With 10,000 outer rows and no index on the inner table: 10,000 sequential scans — catastrophic.

The nested loop is optimal for join conditions that are highly selective and where one side is small. The planner chooses it when the outer table has very few rows.

Hash join

Build a hash table from the smaller table (the "inner" side). Scan the larger table (the "outer" side) and probe the hash table for matching rows.

cost = build_hash(inner_table) + scan(outer_table) + N_matches × hash_probe_cost

The hash join requires loading the entire inner table into memory to build the hash table. If the inner table does not fit in work_mem, Postgres spills it to disk — this is called a hash batch and dramatically increases cost.

Hash Join  (cost=312.00..4519.00)
  Hash Cond: (a.user_id = u.id)
  ->  Seq Scan on test_attempts a    ← outer (larger table)
  ->  Hash                           ← building hash table
        ->  Seq Scan on users u      ← inner (smaller table, hashed)

Hash join is optimal for joining two large tables when neither has a useful index. It requires no sorted data and scales linearly with table size. The planner chooses it when estimated join output is large and no index makes nested loop viable.

Merge join

Sort both tables on the join key, then merge them like merge sort — advance through both sorted sequences simultaneously, emitting matching pairs.

cost = sort(outer) + sort(inner) + scan(both)

Merge join is optimal when both inputs are already sorted (from an index scan on an ordered index) or when the sort cost is amortised by the query doing multiple operations that benefit from sorted order. It is rarely chosen for ad-hoc OLTP queries but common in analytical queries with ORDER BY that matches the join key.

Part 5: Index Types and When to Use Them

Postgres supports several index types. B-tree is the default and handles most cases. Knowing when to use the others is part of operating Postgres correctly.

B-tree (default)

Balanced tree. Supports equality (=), range (<, >, BETWEEN), sorting (ORDER BY), and IS NULL / IS NOT NULL. Correct choice for the vast majority of indexes.

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_attempts_created_at ON test_attempts(created_at);

The B-tree stores values in sorted order. A query with ORDER BY created_at LIMIT 10 can use the index to return the first 10 rows without sorting the entire table — a critical optimisation for paginated queries.

Partial index

An index over a subset of rows — only rows satisfying a condition are indexed.

-- Only index active users (not archived)
CREATE INDEX idx_active_users ON users(email) WHERE deleted_at IS NULL;

-- Only index unpaid invoices
CREATE INDEX idx_unpaid_invoices ON invoices(user_id) WHERE status = 'unpaid';

Partial indexes are smaller (faster to traverse, fits in memory more easily) and more selective. If 95% of your users have deleted_at IS NULL, a partial index on active users is 1/20th the size of a full index and dramatically faster for the queries that matter.

Composite index (multi-column)

An index on multiple columns. The column order matters enormously.

CREATE INDEX idx_attempts_user_status ON test_attempts(user_id, status);

This index can support:

• WHERE user_id = X — uses the index (leftmost prefix rule)
• WHERE user_id = X AND status = 'completed' — uses both columns
• ORDER BY user_id, status — index scan returns rows in this order without sorting

This index cannot support:

• WHERE status = 'completed' alone — the index is sorted by user_id first; without a user_id constraint, the planner cannot use it efficiently

The leftmost prefix rule: a composite index (a, b, c) supports queries filtering on a, a, b, or a, b, c — but not b, c, or b, c alone. Design composite indexes around your most common query patterns, with the most selective column first if the queries use equality on it.

GIN index (Generalized Inverted Index)

For indexing composite values where you query for containment: arrays, JSONB, full-text search.

-- Index a JSONB column for containment queries
CREATE INDEX idx_user_metadata ON users USING gin(metadata);
-- Query: WHERE metadata @> '{"plan": "pro"}'

-- Full-text search
CREATE INDEX idx_questions_fts ON questions USING gin(to_tsvector('english', body));
-- Query: WHERE to_tsvector('english', body) @@ plainto_tsquery('english', 'machine learning')

GIN indexes are larger and slower to update than B-trees but enable queries that B-trees cannot handle at all. Use them specifically for JSONB containment, array overlap, and full-text search.

Index on expression

Index the result of a function or expression rather than a raw column value.

-- Queries on lower(email) can use this index
CREATE INDEX idx_users_email_lower ON users(lower(email));
-- Query: WHERE lower(email) = lower('User@Example.com')

-- Index a JSONB field extracted with ->>
CREATE INDEX idx_users_plan ON users((metadata->>'plan'));
-- Query: WHERE metadata->>'plan' = 'pro'

Without an expression index, WHERE lower(email) = $1 forces a sequential scan — Postgres cannot use a B-tree on email to answer a query on lower(email). Expression indexes solve this.

Part 6: The Most Common Query Performance Problems

Missing index (sequential scan on large table with high selectivity)

Seq Scan on users  (cost=0.00..27400.00 rows=1 width=164)
  Filter: (email = 'user@example.com')
  Rows Removed by Filter: 999999

The plan scans 1 million rows to return 1. Rows Removed by Filter: 999999 is the smoking gun. Fix: add CREATE INDEX idx_users_email ON users(email).

Stale statistics causing wrong row estimates

Seq Scan on test_attempts  estimated rows=1000  actual rows=890000

The planner thought the table had 1,000 rows. It actually had 890,000. The table grew rapidly and autovacuum had not run ANALYZE yet. The planner chose a nested loop join appropriate for 1,000 rows; with 890,000 rows the nested loop is catastrophically slow.

Fix: ANALYZE test_attempts to update statistics immediately. Long-term: tune autovacuum to run more frequently on high-growth tables. Alternatively, increase default_statistics_target for columns with unusual distributions.

N+1 query pattern (application-level, not planner)

This does not appear in a single EXPLAIN ANALYZE — it appears in your query logs as the same query running thousands of times in rapid succession.

// N+1: for 100 users, runs 101 queries
const users = await prisma.user.findMany({ take: 100 });
for (const user of users) {
  const attempts = await prisma.attempt.findMany({ where: { userId: user.id } });
}

Each findMany for attempts is individually fast. But 100 sequential round trips to the database — each with network latency, connection overhead, and query parsing — add up. Under load with 1,000 concurrent requests each doing 100 loop iterations, you saturate connection slots and generate 100,000 queries per request batch.

Fix: use Prisma's include to fetch related data in a single JOIN query, or use a batched WHERE user_id IN (...) query.

work_mem exhaustion causing disk spills

Sort  (cost=45821.50..47321.50 rows=600000 width=45)
      (actual time=2341.234..3102.445 rows=600000 loops=1)
  Sort Method: external merge  Disk: 48320kB

Sort Method: external merge means the sort did not fit in work_mem and spilled to disk. A disk sort is 10-100x slower than an in-memory sort. The same symptom appears in hash joins:

Hash  (actual time=1234.5..1234.5 rows=500000 loops=1)
      Batches: 8  Memory Usage: 4096kB

Batches: 8 means the hash table was split across 8 disk batches. Each batch requires re-reading the outer table. 8 batches = 8x the I/O of an in-memory hash join.

Fix: increase work_mem for sessions running heavy analytical queries:

SET work_mem = '256MB';
-- run the expensive query

Do not increase work_mem globally without care — it is allocated per sort/hash operation per query, and multiple concurrent queries each doing multiple sorts can exhaust RAM.

Index not used due to type mismatch or implicit cast

-- users.id is BIGINT
-- This will NOT use the index:
WHERE id = '12345'   -- string literal, implicit cast forces seq scan

-- This WILL use the index:
WHERE id = 12345     -- correct type

Postgres will not use an index when the comparison requires an implicit cast that is not index-compatible. Your application ORM must pass parameters with the correct type. In Prisma, this is handled automatically for schema-typed fields. In raw queries, verify your parameter types match the column types.

Part 7: A Systematic Optimization Workflow

When a query is slow, follow this sequence:

Step 1: Capture the query with EXPLAIN ANALYZE BUFFERS.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT ...;

BUFFERS adds cache hit/miss information — how many pages came from shared_buffers (fast) vs disk (slow). A query with many disk reads on a warm production system suggests the working set exceeds your shared_buffers allocation.

Step 2: Find the slowest node.

Read the actual time on each node. The node with the largest actual time is the bottleneck.

Step 3: Check estimated vs actual rows.

For the slowest node, compare estimated and actual rows. A large discrepancy means the planner had bad statistics. Run ANALYZE on the relevant tables.

Step 4: Identify the scan type on the slowest node.

Sequential scan with high rows-removed: candidate for an index. Index scan with many loops: the outer side of a nested loop has too many rows; check why. Sort with disk spill: increase work_mem or add an index that returns rows pre-sorted. Hash join with many batches: increase work_mem or restructure the query to reduce hash input size.

Step 5: Verify the fix.

After adding an index or changing a query, run EXPLAIN ANALYZE again and confirm the plan changed and actual time decreased. Indexes do not always help — sometimes the planner is right to ignore them. Confirm empirically.

Step 6: Check for index bloat on tables with high update rates.

From Chapter 1 and Chapter 3, you know dead tuples accumulate. Indexes accumulate dead entries too. Run pgstatindex to check index bloat:

SELECT * FROM pgstatindex('idx_users_email');
-- leaf_fragmentation > 50% suggests REINDEX is warranted

Part 8: Tools and Views for Ongoing Monitoring

Rather than waiting for a slow query to surface, monitor proactively.

pg_stat_statements — the single most useful extension for query performance monitoring:

-- Enable in postgresql.conf: shared_preload_libraries = 'pg_stat_statements'

SELECT query, calls, mean_exec_time, total_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

This shows your top-10 slowest queries by cumulative execution time. The query that runs 10,000 times at 5ms each (50 seconds total) is more damaging than one that runs once at 2 seconds. pg_stat_statements surfaces both.

pg_stat_user_tables — table access patterns:

SELECT relname, seq_scan, idx_scan, n_live_tup, n_dead_tup
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;

Tables with high seq_scan and large n_live_tup are candidates for new indexes. High n_dead_tup relative to n_live_tup means autovacuum is falling behind.

pg_stat_user_indexes — index usage:

SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

Indexes with idx_scan = 0 are never used. They consume disk space, slow down writes (every write must update all indexes on the table), and should be dropped.

Vocabulary Map

Practice Problems

Problem 1 — Failure Analysis: AttemptIQ runs this query to show a user their recent attempts:

SELECT a.id, a.score, a.created_at, t.title
FROM test_attempts a
JOIN tests t ON t.id = a.test_id
WHERE a.user_id = 12345
ORDER BY a.created_at DESC
LIMIT 20;

The table has 5 million rows. The query takes 3.2 seconds. EXPLAIN ANALYZE shows:

Seq Scan on test_attempts  estimated rows=5000000  actual rows=5000000
  Filter: (user_id = 12345)
  Rows Removed by Filter: 4999847

Diagnose the problem completely. What index would you add? After adding the index, what scan type do you expect? The query also has ORDER BY and LIMIT — what composite index would make this query return results without any sort step at all? Write the exact CREATE INDEX statement.

Problem 2 — Design From Scratch: Samiksha's billing dashboard runs this query nightly:

SELECT u.id, u.name, u.plan, SUM(e.credits_used) as monthly_usage
FROM users u
JOIN evaluations e ON e.user_id = u.id
WHERE e.created_at >= date_trunc('month', now())
GROUP BY u.id, u.name, u.plan
HAVING SUM(e.credits_used) > 0
ORDER BY monthly_usage DESC;

users has 50,000 rows. evaluations has 8 million rows. The query takes 45 seconds. EXPLAIN ANALYZE shows a hash join with Batches: 12. What does that tell you? Design the complete optimization: which index(es) to add, what work_mem setting to try, and whether this query should run against the primary or a replica. Justify each decision.

Problem 3 — Interpret EXPLAIN Output:

Nested Loop  (actual time=0.045..8934.221 rows=45000 loops=1)
  ->  Seq Scan on users u  (actual time=0.021..12.445 rows=45000 loops=1)
        Filter: (subscription_tier = 'pro')
  ->  Index Scan using idx_attempts_user_id on test_attempts a
        (actual time=0.089..0.183 rows=1 loops=45000)
        Index Cond: (user_id = u.id)

This query runs in 8.9 seconds. Explain what is happening in terms of actual operations performed against the database. How many index scans occur? How many total rows does the index scan return across all iterations? What is the total I/O profile of this query? Propose one change to the query or schema that would eliminate the nested loop and why it would be faster.

Problem 4 — Go Deeper: The planner chooses between a sequential scan and an index scan based on a cost comparison. The cost of a sequential scan scales linearly with table size. The cost of an index scan has a fixed component (B-tree traversal) plus a variable component (heap page fetches per matching row). At some selectivity threshold, the index scan becomes cheaper than the sequential scan.

Without knowing the exact planner cost formulas: reason about what happens to this threshold as the table gets larger. Does adding more rows make the planner more or less likely to use an index for the same selectivity? What does this imply about indexes on tables that start small but grow large? What Postgres parameter controls how aggressively the planner prefers index scans over sequential scans?

Problem 5 — Connect to Previous Chapters: In Chapter 1 you learned about table bloat from MVCC dead tuples. In Chapter 3 you learned that a sharded system running analytics requires scatter-gather across all shards.

AttemptIQ has a sharded test_attempts table (4 shards, sharded by user_id). On each shard, the table has significant bloat — 3 dead tuples per live row due to frequent score updates and slow autovacuum. A query runs on each shard and merges results in the application.

Describe the compounding effect: what is the I/O cost of this query across all shards relative to an unsharded, fully-vacuumed table? If you were diagnosing this in production, name the specific pg_stat_user_tables columns that would show you both problems (bloat and scan type) on each shard, and describe the order of operations to fix both.

What's Next

You have now completed Phase 2. You can design transactions that are safe under concurrency, reason about replication lag and its consequences, decide when sharding is necessary and what it costs, and read the query planner's decisions and override them when they are wrong. These four skills together give you full command of Postgres as an operational system — not just a place to store data but an engine you understand well enough to tune under any condition.

Phase 3 moves from a single data store to distributed systems at large. The concepts in Phase 2 are not left behind — they are the foundation that makes Phase 3 comprehensible. When you study CAP theorem, you will recognise the consistency tradeoffs from replication lag. When you study consistent hashing, you will recognise the resharding problem from Chapter 3. The vocabulary you have built is about to become the lens through which distributed systems make sense.

End of Phase 2, Chapter 4.