Reading Query Plans with EXPLAIN in PostgreSQL

EXPLAIN shows you the execution plan PostgreSQL's query planner has chosen for a statement — which scan methods, join strategies, and orderings it will use — without actually running the query. Adding ANALYZE actually executes the statement and reports real timing and row counts alongside the planner's estimates, making it the single most important tool for diagnosing why a query is slow.

Why it matters

When a query is slow, guessing at the cause wastes hours. EXPLAIN shows exactly what PostgreSQL does: which tables it scans, which indexes it uses and where the time goes. Reading a plan is the core skill of database performance work.

EXPLAIN vs. EXPLAIN ANALYZE

Plain EXPLAIN SELECT ...; only estimates cost and row counts based on table statistics — it never actually executes the query, which makes it safe to run even on statements with side effects like UPDATE or DELETE. EXPLAIN ANALYZE SELECT ...; actually runs the query and reports real elapsed time and actual row counts per plan node, letting you compare "estimated rows" against "actual rows" — a large mismatch there is one of the most common signals of stale table statistics or a poor plan choice.

Adding BUFFERS for I/O Visibility

EXPLAIN (ANALYZE, BUFFERS) SELECT ...; additionally reports how many disk buffers were hit in cache versus read from disk for each plan node. A high number of "read" (as opposed to "hit") buffers on a hot, frequently-run query can point to a table that doesn't fit comfortably in memory or an index that isn't being leveraged effectively.

Reading the Plan Tree

A plan is a tree of nodes, read from the innermost (bottom) node outward. Common node types include Seq Scan (reads the whole table — fine for small tables, a red flag on large ones without a matching WHERE-friendly index), Index Scan / Index Only Scan (uses an index to jump directly to matching rows), and join nodes like Nested Loop, Hash Join, and Merge Join, each suited to different table sizes and join conditions. Each node reports estimated cost (arbitrary planner units), estimated rows, and — with ANALYZE — actual time and actual rows.

Example

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM products WHERE category_id = 1 ORDER BY price DESC;

                                                    QUERY PLAN
--------------------------------------------------------------------------------------------------------------
 Sort  (cost=8.32..8.35 rows=12 width=64) (actual time=0.041..0.042 rows=2 loops=1)
   Sort Key: price DESC
   Buffers: shared hit=3
   ->  Index Scan using idx_products_category on products (cost=0.15..8.10 rows=12 width=64)
                                                  (actual time=0.020..0.022 rows=2 loops=1)
         Index Cond: (category_id = 1)
         Buffers: shared hit=3
 Planning Time: 0.112 ms
 Execution Time: 0.061 ms

Exercise

This exercise uses PostgreSQL's EXPLAIN output, so it needs a PostgreSQL server. For a large orders table, write the command that shows the actual execution plan, with timings and buffer usage, of the query that finds the orders of customer 42. Then create the index that the plan will probably show to be missing, and run the same command again.

Expected result: the first plan shows a sequential scan over the whole table; after the index is created, the plan shows an index scan or bitmap index scan and a much lower execution time.

-- TODO: show the real plan, with timing and buffers, for:
--       SELECT * FROM orders WHERE customer_id = 42;

-- TODO: create an index on orders.customer_id

-- TODO: show the plan again

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

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

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

Quick quiz

Interview questions