Mastering SQL Execution Plans: Reading EXPLAIN ANALYZE, Index Scans & Query Optimization

When an API endpoint slows down from 20ms to 4.5s under production traffic, 9 times out of 10 the root cause is an unoptimized SQL query triggering a full table scan or nested loop blowout.

Guessing where to add indexes without analyzing the database execution plan leads to bloated disk usage, slower INSERT/UPDATE operations, and wasted memory buffers.

EXPLAIN ANALYZE is the database query optimizer's diagnostic output. It details the exact plan the database created, the actual execution time, disk reads, and buffer cache hits.

This guide provides a practical, step-by-step developer manual for reading EXPLAIN ANALYZE in PostgreSQL and MySQL, diagnosing bottlenecks, and designing optimal indexes.

---

1. How SQL Query Planners Work

SQL is a declarative language: you describe what data you want, not how to retrieve it.

The database query engine processes queries in four distinct stages:

[SQL Query Text]
       |
       v
[1. Parser / Lexer]   --> Generates Abstract Syntax Tree (AST)
       |
       v
[2. Rewriter / Rules] --> Expands views, flattens subqueries
       |
       v
[3. Query Planner]    --> Estimates costs using table statistics (pg_statistic)
       |
       v
[4. Executor Engine]  --> Fetches disk blocks, scans indexes, filters, returns rows

• EXPLAIN: Runs only stages 1–3. Returns the optimizer's estimated cost and row count without executing the query.
• EXPLAIN ANALYZE: Runs stages 1–4. Actually executes the query on the database, measuring the real elapsed wall-clock time and buffer statistics.

⚠️ Warning: Because EXPLAIN ANALYZE executes the statement, running it on an UPDATE or DELETE query will modify your database data! Wrap mutation queries in a transaction and roll back:

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

---

2. Anatomy of a PostgreSQL Execution Plan

Always run PostgreSQL queries with BUFFERS:

EXPLAIN (ANALYZE, BUFFERS, COSTS, TIMING)
SELECT o.id, o.total_amount, u.email
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'completed' AND o.created_at >= '2026-01-01'
ORDER BY o.total_amount DESC
LIMIT 50;

Reading the Output Tree

Limit  (cost=1204.50..1204.62 rows=50 width=48) (actual time=14.215..14.228 rows=50 loops=1)
  Buffers: shared hit=412 read=18
  ->  Sort  (cost=1204.50..1220.30 rows=6320 width=48) (actual time=14.213..14.221 rows=50 loops=1)
        Sort Key: o.total_amount DESC
        Sort Method: top-N heapsort  Memory: 30kB
        ->  Nested Loop  (cost=0.56..980.40 rows=6320 width=48) (actual time=0.082..12.300 rows=6320 loops=1)
              Buffers: shared hit=390 read=18
              ->  Index Scan using idx_orders_status_created on orders o  (cost=0.28..450.20 rows=6320 width=24) (actual time=0.045..3.210 rows=6320 loops=1)
                    Index Cond: ((status = 'completed'::text) AND (created_at >= '2026-01-01 00:00:00'::timestamptz))
                    Buffers: shared hit=180 read=12
              ->  Index Scan using users_pkey on users u  (cost=0.28..0.08 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=6320)
                    Index Cond: (id = o.user_id)
                    Buffers: shared hit=210 read=6
Planning Time: 0.325 ms
Execution Time: 14.280 ms

Decoding the Plan Metrics

1. cost=1204.50..1204.62:
   • First number (1204.50): Startup cost before returning the first row.
   • Second number (1204.62): Total estimated cost to complete the node (unit is arbitrary disk page read costs).
2. actual time=0.045..3.210: Real elapsed time in milliseconds.
3. loops=6320: How many times this operation repeated. Multiply the node's actual time by loops to get total time spent in that step.
4. Buffers: shared hit=412 read=18:
   • hit: Data pages served from RAM buffer cache (fast).
   • read: Physical disk reads from storage (slow).

---

3. The 4 Main Table Access Methods

Access Method	Description	When It's Good	When It's a Bottleneck
Seq Scan (Full Table Scan)	Scans every table block on disk sequentially.	Small tables (<1,000 rows) or when querying >20% of all rows.	Large tables (>100k rows) with low selective filters.
Index Scan	Traverses B-tree index, then reads the corresponding heap table page.	Highly selective filters (fetching <5% of rows).	Querying columns not covered by the index.
Index Only Scan	Fetches all requested columns directly from the index. Zero heap reads!	Ultimate speed. All SELECT and WHERE columns exist in index.	Visibility map is stale (run VACUUM).
Bitmap Index / Heap Scan	Builds a bitmap of matching pages in memory, then fetches disk blocks in physical sequence.	Queries matching 5%–20% of rows or combining multiple indexes.	Unoptimized work memory (work_mem).

---

4. Designing the Perfect Composite Index: The ESR Rule

When indexing multiple columns (WHERE a = ? AND b = ? AND c > ? ORDER BY d), column order inside the composite index is critical.

Follow the ESR (Equality, Sort, Range) rule:

1. E - Equality (= / IS NULL): Place all columns tested with exact equality first.
2. S - Sort (ORDER BY): Place columns used in ordering next (matching order direction ASC/DESC).
3. R - Range (>, <, BETWEEN, IN): Place inequality or range conditions last.

Example

-- Query:
SELECT * FROM transactions
WHERE user_id = 'usr_100'        -- Equality
  AND status = 'settled'         -- Equality
  AND created_at >= '2026-01-01' -- Range
ORDER BY created_at DESC;        -- Sort

-- ❌ BAD INDEX (Range placed before equality):
CREATE INDEX idx_bad ON transactions(created_at, user_id, status);

-- ✅ PERFECT INDEX (Equality first, then Range/Sort):
CREATE INDEX idx_optimal ON transactions(user_id, status, created_at DESC);

---

5. Covering Indexes with the INCLUDE Clause

If a query only needs 1 or 2 extra columns for the SELECT clause, adding them to the index payload using INCLUDE turns an Index Scan into a blazing-fast Index Only Scan:

-- Instead of indexing all columns in the B-Tree key:
CREATE INDEX idx_orders_user_lookup
ON orders (user_id, status)
INCLUDE (total_amount, currency);

The database indexes user_id and status in the B-tree search tree, while storing total_amount and currency only in the leaf nodes, drastically reducing index size while eliminating all heap lookups.

---

6. Diagnostic Optimization Checklist

• Run EXPLAIN (ANALYZE, BUFFERS) to see actual execution times and buffer reads.
• Check if rows estimated by the planner deviates drastically from actual rows (if yes, run ANALYZE table_name; to update table statistics).
• Eliminate sequential scans on large tables with high-selectivity queries.
• Apply the ESR rule (Equality → Sort → Range) when building multi-column composite indexes.
• Use INCLUDE to achieve zero-heap Index Only Scans for hot analytical queries.

Format and lint your SQL queries with the SQL Formatter and generate schema migrations using JSON to SQL.