Reading EXPLAIN and EXPLAIN ANALYZE

conceptThe concept

A plan is a tree. Each node pulls rows from its children; the top node returns them to you. Read it from the innermost, most indented node outward. Every node shows cost=startup..total in arbitrary planner units, the estimated rows and the average row width in bytes.

EXPLAIN ANALYZE executes the query and adds actual time=first..last in milliseconds, actual rows and loops. Times and rows are per loop: a node with rows=3 loops=1000 produced 3,000 rows. BUFFERS adds pages found in shared memory (hit) and read from the operating system (read). Wrap data-changing statements in BEGIN and ROLLBACK, because ANALYZE really executes them.

Scan types: a Seq Scan reads every page and is the right choice when many rows qualify. An Index Scan walks the index and visits the heap per match. A Bitmap Heap Scan first collects matching row locations from one or more indexes, sorts them by page, then reads each page once, which is ideal for "a few hundred rows scattered around". An Index Only Scan answers from the index alone when every needed column is in it and the pages are marked all-visible.

whyWhy it matters

Guessing at performance is how indexes pile up that nobody uses. The plan tells you exactly where the time went, whether the optimizer's estimate was wrong, and whether a change helped.

Use caseReal-world scenario

The customer page loads slowly. EXPLAIN ANALYZE shows a Seq Scan on orders discarding 9,983 of 10,000 rows to find 17. One index later, the same query reads two index pages and 16 heap pages.

Try itExamples

Run and edit these in the interactive lesson.

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

BEGIN;
CREATE INDEX orders_customer_id_idx ON orders (customer_id);
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42;
ROLLBACK;

About 45% of orders belong to customers 1 to 399 (ordering is skewed toward low ids). Visiting the index and then most of the table costs more than reading the table once.

BEGIN;
CREATE INDEX orders_customer_id_idx ON orders (customer_id);
EXPLAIN SELECT * FROM orders WHERE customer_id < 400;
EXPLAIN SELECT * FROM orders WHERE customer_id < 5;
ROLLBACK;

VACUUM orders;
BEGIN;
CREATE INDEX orders_customer_id_idx ON orders (customer_id);
EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id FROM orders WHERE customer_id BETWEEN 10 AND 20;
ROLLBACK;

BEGIN;
EXPLAIN (ANALYZE, COSTS OFF)
UPDATE products SET price = price * 1.1 WHERE category = 'toys';
ROLLBACK;

internalsUnder the hood

Costs come from settings such as seq_page_cost (1.0), random_page_cost (4.0, often lowered to about 1.1 on SSDs) and cpu_tuple_cost, combined with row estimates from pg_statistic. When estimates are wrong, every choice built on them (join order, join algorithm, memory) can be wrong too. In this course the Explain button in the playground draws these plans as a tree and flags the classic warning signs.

Avoid / preferDo this, not that

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

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

-- "cost=0.00..1234.00, too high"

-- compare estimated rows with actual rows on each node,
-- and look at where the actual time goes

PitfallsCommon mistakes

• Reading only the top-line cost. Find the node where actual time jumps, and compare estimated to actual rows there.
• Forgetting that actual rows and time are per loop. Multiply by loops before deciding a node is cheap.
• Running EXPLAIN ANALYZE on an UPDATE or DELETE outside a transaction you roll back. It changes the data.

LimitsWhen not to use it

Do not tune from plans on a tiny development database. With little data the planner rightly prefers sequential scans; test against production-like volumes and statistics.

SecuritySecurity notes

EXPLAIN ANALYZE runs functions with side effects, and auto_explain writes plans, sometimes with parameter values, to the server log. Treat logs as sensitive and restrict who can read them.

ChallengeTry it yourself

BEGIN;
CREATE INDEX ON orders (customer_id);
SELECT pgm.uses_index('SELECT * FROM orders WHERE customer_id = 42') AS uses_index;
ROLLBACK;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• Using EXPLAIN
• Planner and optimizer