Transactions, Performance, and Production SQLLesson 6.2

Reading EXPLAIN output — understanding query execution plans

EXPLAIN, EXPLAIN ANALYZE, sequential scan, index scan, cost estimates, actual vs estimated rows, node types, startup cost vs total cost

EXPLAIN Shows What the Database Plans to Do

EXPLAIN shows the execution plan the query planner chose without running the query. EXPLAIN ANALYZE runs the query and shows both planned and actual costs. This is your primary tool for diagnosing slow queries.

-- Planned cost only
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

-- Actual timing and row counts (runs the query)
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

Reading the Output

Seq Scan on orders (cost=0.00..450.00 rows=12 width=40)
             Filter: (customer_id = 42)

-- After adding an index:
Index Scan using idx_orders_customer_id on orders
             (cost=0.28..8.45 rows=12 width=40)

Seq Scan: reads every row — fine for small tables, bad for large ones with a selective filter.
Index Scan: uses an index to jump to matching rows.
cost=startup..total: estimated compute units before first row and to completion.
rows: estimated matching rows — if wildly off from actual, statistics may be stale (run ANALYZE).