Indexes and Query PerformanceLesson 4.2

How to read PostgreSQL EXPLAIN ANALYZE output

EXPLAIN vs EXPLAIN ANALYZE, cost estimation, actual time rows loops, Seq Scan vs Index Scan vs Bitmap Scan, node types, width, planning time vs execution time

EXPLAIN ANALYZE

EXPLAIN ANALYZE is the primary tool for diagnosing slow queries. It shows PostgreSQL's execution plan and actual measured performance.

Basic usage

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 42 AND status = 'pending';

Reading the output

Seq Scan on orders  (cost=0.00..4821.00 rows=3 width=80)
                   (actual time=0.032..48.7 rows=3 loops=1)
  Filter: (customer_id = 42 AND status = 'pending')
  Rows Removed by Filter: 99997

Key numbers to check: cost=startup..total (planner estimate), actual time=first_row..last_row (real milliseconds), rows (actual row count). A large gap between estimated rows and actual rows means outdated statistics — run ANALYZE orders;.

Scan types

Seq Scan        -- reads entire table
Index Scan      -- uses index, fetches heap rows one by one
Index Only Scan -- stays in index (covering index)
Bitmap Heap Scan-- batches heap fetches for range queries

A Seq Scan on a large table with few matching rows usually means a missing index. Use EXPLAIN (ANALYZE, BUFFERS) to also see cache hit ratios.