This article was originally published on AI Study Room. For the full version with working code examples and related articles, visit the original post.

EXPLAIN ANALYZE Deep Dive: Reading Plans, Cost Estimation, and Scan Types

EXPLAIN ANALYZE Deep Dive: Reading Plans, Cost Estimation, and Scan Types

EXPLAIN ANALYZE Deep Dive: Reading Plans, Cost Estimation, and Scan Types

EXPLAIN ANALYZE Deep Dive: Reading Plans, Cost Estimation, and Scan Types

EXPLAIN ANALYZE Deep Dive: Reading Plans, Cost Estimation, and Scan Types

EXPLAIN ANALYZE Deep Dive: Reading Plans, Cost Estimation, and Scan Types

EXPLAIN ANALYZE Deep Dive: Reading Plans, Cost Estimation, and Scan Types

EXPLAIN ANALYZE Deep Dive: Reading Plans, Cost Estimation, and Scan Types

EXPLAIN ANALYZE Deep Dive: Reading Plans, Cost Estimation, and Scan Types

EXPLAIN ANALYZE Deep Dive: Reading Plans, Cost Estimation, and Scan Types

EXPLAIN ANALYZE Deep Dive: Reading Plans, Cost Estimation, and Scan Types

EXPLAIN ANALYZE Deep Dive: Reading Plans, Cost Estimation, and Scan Types

EXPLAIN ANALYZE Deep Dive: Reading Plans, Cost Estimation, and Scan Types

EXPLAIN ANALYZE Deep Dive: Reading Plans, Cost Estimation, and Scan Types

The EXPLAIN command is the single most important tool for understanding query performance. This article teaches you to read execution plans, interpret cost estimates, and identify optimization opportunities in PostgreSQL.

EXPLAIN Basics

EXPLAIN SELECT * FROM users WHERE email = '[e-mail]';

Output:

Seq Scan on users (cost=0.00..1243.12 rows=1 width=84)

Filter: (email = '[e-mail]'::text)

This shows a sequential scan (Seq Scan) scanning the entire table at an estimated cost of 0.00 to 1243.12, producing 1 row.

EXPLAIN ANALYZE

EXPLAIN ANALYZE actually executes the query and shows real metrics:

EXPLAIN (ANALYZE, BUFFERS, TIMING)

SELECT * FROM users WHERE email = '[e-mail]';

Output:

Seq Scan on users (cost=0.00..1243.12 rows=1 width=84) (actual time=0.532..12.843 rows=1 loops=1)

Filter: (email = '[e-mail]'::text)

Rows Removed by Filter: 99999

Buffers: shared hit=840

Planning Time: 0.083 ms

Execution Time: 12.912 ms

Key differences from the estimated plan:

• actual time : The real time (startup..total) for this node.

• rows=1 : The actual number of rows returned.

• Rows Removed by Filter : 99,999 rows were scanned and discarded, a huge waste.

• Buffers: shared hit=840 : 840 pages were found in shared buffers.

• Planning Time vs Execution Time : Total overhead.

Scan Types

Sequential Scan

The database reads every page of the table. Efficient for tables that fit in memory or when most rows are returned:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE total > 0;

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- Seq Scan on orders (cost=0.00..5432.10 rows=500000 width=42)

Ideal when : The query returns more than ~10% of the table. Sequential reads are faster than random reads for large fractions.

Index Scan

The database traverses a B-tree index and fetches matching rows from the heap:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE id = 42;

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- Index Scan using orders_pkey on orders (cost=0.29..8.31 rows=1 width=42)

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- Index Cond: (id = 42)

Ideal when : Highly selective queries returning a small fraction of rows. Each row fetch requires a random I/O to the heap.

Index Only Scan

PostgreSQL fetches the result entirely from the index, avoiding heap access:

EXPLAIN (ANALYZE, BUFFERS) SELECT id, status FROM orders WHERE status = 'paid';

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- Index Only Scan using idx_orders_status on orders (cost=0.29..123.43 rows=5000 width=36)

The Heap Fetches: 0 line confirms no heap visits. Visibility map checks ensure rows are visible without visiting the heap.

Bitmap Scan

Combines multiple index lookups and sorts pages before fetching:

EXPLAIN (ANALYZE, BUFFERS)

SELECT * FROM orders WHERE status = 'pending' AND total > 1000;

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

---

Read the full article on AI Study Room for complete code examples, comparison tables, and related resources.

Found this useful? Check out more developer guides and tool comparisons on AI Study Room.