Read EXPLAIN ANALYZE in 10 Minutes

A query is slow, and you have ten minutes before you have to say something useful. The plan is the fastest way to find out why, and most of the answer sits in four or five lines of it. This page runs one slow query, reads its plan with a short checklist, fixes it, and reads the plan again. The Advanced course lesson on plans goes node by node. This is the quick route.

Step 1: a table with 20,000 invented orders

CREATE TABLE big_orders AS
SELECT g                                AS order_id,
       (g % 8) + 1                      AS customer_id,
       DATE '2026-01-01' + (g % 365)    AS order_date,
       CASE WHEN g % 100 = 0 THEN 'cancelled'
            WHEN g % 100 < 4 THEN 'new'
            WHEN g % 100 < 12 THEN 'paid'
            ELSE 'shipped' END          AS status,
       ((g % 97) + 0.5)::numeric(8,2)   AS total
FROM generate_series(1, 20000) AS g;
ALTER TABLE big_orders ADD PRIMARY KEY (order_id);
ANALYZE big_orders;
SET max_parallel_workers_per_gather = 0;

SELECT 20000
ALTER TABLE
ANALYZE
SET

Step 2: run the query with EXPLAIN ANALYZE

The report wants the five oldest new orders. ANALYZE makes PostgreSQL run the query and report what happened. The other options only hide numbers that change from run to run, so the output below is the same every time. On a real problem, leave TIMING and BUFFERS on.

EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM big_orders WHERE status = 'new' ORDER BY order_date LIMIT 5;

                           QUERY PLAN
-----------------------------------------------------------------
 Limit (actual rows=5.00 loops=1)
   ->  Sort (actual rows=5.00 loops=1)
         Sort Key: order_date
         Sort Method: top-N heapsort  Memory: 25kB
         ->  Seq Scan on big_orders (actual rows=600.00 loops=1)
               Filter: (status = 'new'::text)
               Rows Removed by Filter: 19400
(7 rows)

The checklist

Read the plan from the most indented line upward, and ask five questions.

1. Which node touches the most rows? Here it is the Seq Scan, with 600 rows kept and Rows Removed by Filter: 19400. PostgreSQL read 20,000 rows to hand 5 to the user.
2. A big filter loss next to a small result? A large Rows Removed by Filter is the sign of a scan that wants an index, or of a filter that an index cannot serve.
3. Do the estimate and the actual rows agree? Compare rows= with actual rows=. With COSTS OFF the estimates are hidden, so turn costs on for this check. A gap of ten times or more means the statistics are wrong, and the fix is ANALYZE, not an index.
4. How many loops? actual rows is per loop. A node with loops=5000 is run 5,000 times.
5. Did anything spill to disk? Look for Sort Method: external merge Disk: ... and for Batches: above 1 on hash nodes. Memory is the problem, not the table.

The Sort here is top-N heapsort: because of the LIMIT 5 PostgreSQL only had to keep the best five, in 25 kB. Sorting everything is another story. Run a full sort with a deliberately small work_mem, and then with the default.

SET work_mem = '64kB';
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM big_orders ORDER BY total DESC, order_id;
RESET work_mem;
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM big_orders ORDER BY total DESC, order_id;

SET
                         QUERY PLAN
-------------------------------------------------------------
 Sort (actual rows=20000.00 loops=1)
   Sort Key: total DESC, order_id
   Sort Method: external merge  Disk: 752kB
   ->  Seq Scan on big_orders (actual rows=20000.00 loops=1)
(4 rows)

RESET
                         QUERY PLAN
-------------------------------------------------------------
 Sort (actual rows=20000.00 loops=1)
   Sort Key: total DESC, order_id
   Sort Method: quicksort  Memory: 1689kB
   ->  Seq Scan on big_orders (actual rows=20000.00 loops=1)
(4 rows)

With 64 kB of work_mem the sort wrote 752 kB to disk (external merge). With the default 4 MB it sorted in memory (quicksort, 1689 kB). If you see the first form on a slow query, try the query with a higher work_mem for that session before you touch anything else. Do not raise it globally: each sort and hash node in each connection can use that much.

Step 3: fix it and read the plan again

Only new orders are asked for, and they are 3% of the table. A partial index on the order date holds just those rows, in date order, so the sort disappears.

CREATE INDEX big_orders_new_by_date ON big_orders (order_date) WHERE status = 'new';
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM big_orders WHERE status = 'new' ORDER BY order_date LIMIT 5;

CREATE INDEX
                                       QUERY PLAN
----------------------------------------------------------------------------------------
 Limit (actual rows=5.00 loops=1)
   ->  Index Scan using big_orders_new_by_date on big_orders (actual rows=5.00 loops=1)
         Index Searches: 1
(3 rows)

The plan has no Sort and no Rows Removed by Filter. PostgreSQL walked the index in date order and stopped after 5 rows. The checklist answers changed with it: 5 rows touched instead of 20,000, and no sort.

Related course: SQL Advanced.