Query Plans

A query plan is the tree of steps the database chooses to run a query. EXPLAIN shows the plan. The optimizer picks the plan with the lowest estimated cost, using table statistics. Reading plans is the first step in fixing a slow query, which Fixing Slow Queries covers. Index basics are in How Indexes Work.

This lesson uses two generated tables: 20,000 customers and 200,000 orders. Parallel plans are switched off so the output is stable.

SQL

SET max_parallel_workers_per_gather = 0;

CREATE TABLE big_customers AS
SELECT i AS id,
       'customer' || i AS name,
       (ARRAY['Bengaluru','Mumbai','Delhi','Pune','Chennai'])[i % 5 + 1] AS city
FROM generate_series(1, 20000) AS i;

CREATE TABLE big_orders AS
SELECT i AS id,
       (i * 7) % 20000 + 1 AS customer_id,
       DATE '2024-01-01' + i / 600 AS order_date,
       (ARRAY['placed','shipped','delivered','cancelled'])[i % 4 + 1] AS status,
       (i * 13) % 1000 AS amount
FROM generate_series(1, 200000) AS i;

ALTER TABLE big_customers ADD PRIMARY KEY (id);
ALTER TABLE big_orders ADD PRIMARY KEY (id);
CREATE INDEX idx_big_orders_customer ON big_orders (customer_id);
VACUUM ANALYZE big_customers;
VACUUM ANALYZE big_orders;

SELECT (SELECT count(*) FROM big_customers) AS customers, (SELECT count(*) FROM big_orders) AS orders;

Output

 customers | orders 
-----------+--------
     20000 | 200000
(1 row)

Example 1: EXPLAIN vs EXPLAIN ANALYZE

Command	Runs the query	Shows
`EXPLAIN`	No	The chosen plan and the estimated cost and rows
`EXPLAIN ANALYZE`	Yes	The plan plus actual rows, loops, and time per node

EXPLAIN ANALYZE executes the statement. For INSERT, UPDATE, or DELETE, wrap it in a transaction and roll back.

COSTS OFF hides cost numbers. TIMING OFF and SUMMARY OFF hide times, so the output below shows only the plan shape and row counts.

SQL

EXPLAIN (COSTS OFF)
SELECT * FROM big_orders WHERE customer_id = 500;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM big_orders WHERE customer_id = 500;

BEGIN;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
UPDATE big_orders SET amount = 0 WHERE customer_id = 500;
ROLLBACK;

Output

                     QUERY PLAN                     
----------------------------------------------------
 Bitmap Heap Scan on big_orders
   Recheck Cond: (customer_id = 500)
   ->  Bitmap Index Scan on idx_big_orders_customer
         Index Cond: (customer_id = 500)
(4 rows)

                                 QUERY PLAN                                  
-----------------------------------------------------------------------------
 Bitmap Heap Scan on big_orders (actual rows=10 loops=1)
   Recheck Cond: (customer_id = 500)
   Heap Blocks: exact=10
   ->  Bitmap Index Scan on idx_big_orders_customer (actual rows=10 loops=1)
         Index Cond: (customer_id = 500)
(5 rows)

                                    QUERY PLAN                                     
-----------------------------------------------------------------------------------
 Update on big_orders (actual rows=0 loops=1)
   ->  Bitmap Heap Scan on big_orders (actual rows=10 loops=1)
         Recheck Cond: (customer_id = 500)
         Heap Blocks: exact=10
         ->  Bitmap Index Scan on idx_big_orders_customer (actual rows=10 loops=1)
               Index Cond: (customer_id = 500)
(6 rows)

rows=10 in the second plan is the actual row count. loops=1 means the node ran once.

Example 2: reading a plan tree

A plan is a tree. Each line is a node. Child nodes are indented under their parent and marked with ->. Execution starts at the leaves, and each node passes rows up to its parent.

SQL

EXPLAIN (COSTS OFF)
SELECT c.name, o.amount
FROM big_orders o
JOIN big_customers c ON c.id = o.customer_id
WHERE o.id = 1000;

Output

                          QUERY PLAN                          
--------------------------------------------------------------
 Nested Loop
   ->  Index Scan using big_orders_pkey on big_orders o
         Index Cond: (id = 1000)
   ->  Index Scan using big_customers_pkey on big_customers c
         Index Cond: (id = o.customer_id)
(5 rows)

Reading order: the deepest, most indented nodes run first.

flowchart TD
    J["Nested Loop (join)"] --> A["Index Scan on big_orders (o.id = 1000)"]
    J --> B["Index Scan on big_customers (id = o.customer_id)"]

The top node is the last to finish and returns the result. Lines such as Index Cond, Filter, and Join Filter belong to the node above them.

Example 3: scan nodes

Node	Meaning
`Seq Scan`	Reads every page of the table
`Index Scan`	Walks an index, then fetches rows from the table
`Index Only Scan`	Reads only the index
`Bitmap Index Scan` + `Bitmap Heap Scan`	Builds a page bitmap from one or more indexes, then reads those pages
`Tid Scan`	Fetches rows by physical location (`ctid`)

Two condition lines matter:

Index Cond is applied inside the index and limits the entries read.
Filter is applied after the row is read. A row read and then rejected is wasted work.

SQL

EXPLAIN (COSTS OFF) SELECT * FROM big_orders WHERE id = 42;

EXPLAIN (COSTS OFF) SELECT customer_id FROM big_orders WHERE customer_id = 42;

EXPLAIN (COSTS OFF) SELECT * FROM big_orders WHERE customer_id BETWEEN 1 AND 300;

EXPLAIN (COSTS OFF) SELECT * FROM big_orders WHERE customer_id = 42 AND amount > 500;

EXPLAIN (COSTS OFF) SELECT * FROM big_orders WHERE status = 'placed';

Output

                   QUERY PLAN                   
------------------------------------------------
 Index Scan using big_orders_pkey on big_orders
   Index Cond: (id = 42)
(2 rows)

                         QUERY PLAN                          
-------------------------------------------------------------
 Index Only Scan using idx_big_orders_customer on big_orders
   Index Cond: (customer_id = 42)
(2 rows)

                            QUERY PLAN                             
-------------------------------------------------------------------
 Bitmap Heap Scan on big_orders
   Recheck Cond: ((customer_id >= 1) AND (customer_id <= 300))
   ->  Bitmap Index Scan on idx_big_orders_customer
         Index Cond: ((customer_id >= 1) AND (customer_id <= 300))
(4 rows)

                     QUERY PLAN                     
----------------------------------------------------
 Bitmap Heap Scan on big_orders
   Recheck Cond: (customer_id = 42)
   Filter: (amount > 500)
   ->  Bitmap Index Scan on idx_big_orders_customer
         Index Cond: (customer_id = 42)
(5 rows)

             QUERY PLAN              
-------------------------------------
 Seq Scan on big_orders
   Filter: (status = 'placed'::text)
(2 rows)

The fourth plan uses the index for customer_id and Filter for amount. The last one reads the whole table because a quarter of the rows match.

Example 4: nested loop join

A Nested Loop takes each row from the outer input and looks up matches in the inner input. It is the best choice when the outer side has few rows and the inner side has an index on the join column.

SQL

EXPLAIN (COSTS OFF)
SELECT o.id, c.name
FROM big_orders o
JOIN big_customers c ON c.id = o.customer_id
WHERE o.customer_id = 77;

Output

                          QUERY PLAN                          
--------------------------------------------------------------
 Nested Loop
   ->  Index Scan using big_customers_pkey on big_customers c
         Index Cond: (id = 77)
   ->  Bitmap Heap Scan on big_orders o
         Recheck Cond: (customer_id = 77)
         ->  Bitmap Index Scan on idx_big_orders_customer
               Index Cond: (customer_id = 77)
(7 rows)

The condition customer_id = 77 also applies to c.id, so the outer input is one customer row and the inner side is one index lookup on big_orders. Each extra outer row adds one more inner lookup. With a million outer rows that is a million lookups, which is why the planner switches to another join type for large inputs.

Example 5: hash join

A Hash Join builds an in-memory hash table from one input (the smaller one), then scans the other input and probes the hash table. It works for equality joins on large, unsorted inputs and needs no index.

SQL

EXPLAIN (COSTS OFF)
SELECT c.city, count(*)
FROM big_orders o
JOIN big_customers c ON c.id = o.customer_id
GROUP BY c.city;

Output

                  QUERY PLAN                   
-----------------------------------------------
 HashAggregate
   Group Key: c.city
   ->  Hash Join
         Hash Cond: (o.customer_id = c.id)
         ->  Seq Scan on big_orders o
         ->  Hash
               ->  Seq Scan on big_customers c
(7 rows)

The Hash node is the build side. If the hash table exceeds work_mem, it is split into batches on disk.

Example 6: merge join

A Merge Join walks two inputs that are both sorted on the join key and matches them like a zipper. It is chosen when inputs are already sorted (from an index) or when the result must be sorted on the join key. It supports equality and also works for large inputs without a hash table.

SQL

EXPLAIN (COSTS OFF)
SELECT o.id, c.name
FROM big_orders o
JOIN big_customers c ON c.id = o.customer_id
ORDER BY o.customer_id;

Output

                           QUERY PLAN                           
----------------------------------------------------------------
 Merge Join
   Merge Cond: (o.customer_id = c.id)
   ->  Index Scan using idx_big_orders_customer on big_orders o
   ->  Index Scan using big_customers_pkey on big_customers c
(4 rows)

The same join can be forced into each strategy to compare shapes. enable_* settings are for experiments, not production.

SQL

SET enable_hashjoin = off;
SET enable_mergejoin = off;
EXPLAIN (COSTS OFF)
SELECT c.city, count(*)
FROM big_orders o
JOIN big_customers c ON c.id = o.customer_id
GROUP BY c.city;

RESET enable_hashjoin;
RESET enable_mergejoin;

Output

                                QUERY PLAN                                 
---------------------------------------------------------------------------
 HashAggregate
   Group Key: c.city
   ->  Nested Loop
         ->  Seq Scan on big_customers c
         ->  Index Only Scan using idx_big_orders_customer on big_orders o
               Index Cond: (customer_id = c.id)
(6 rows)

With hash and merge joins disabled, the planner falls back to a nested loop that does one index lookup on big_orders for each of the 20,000 customers.

Join	Best when	Needs
Nested Loop	Few outer rows and an index on the inner join column	Any join condition
Hash Join	Large inputs, equality join	Memory for the hash table
Merge Join	Both inputs sorted or sortable, large inputs	Equality join

Example 7: sort nodes

A Sort node appears when ORDER BY, a merge join, or a window function needs sorted input. Sorting needs memory, and the data spills to disk when it exceeds work_mem. An index that already returns rows in the required order removes the sort.

SQL

EXPLAIN (COSTS OFF) SELECT * FROM big_orders ORDER BY amount;

EXPLAIN (COSTS OFF) SELECT * FROM big_orders ORDER BY amount LIMIT 10;

EXPLAIN (COSTS OFF) SELECT * FROM big_orders ORDER BY customer_id LIMIT 10;

Output

          QUERY PLAN          
------------------------------
 Sort
   Sort Key: amount
   ->  Seq Scan on big_orders
(3 rows)

             QUERY PLAN             
------------------------------------
 Limit
   ->  Sort
         Sort Key: amount
         ->  Seq Scan on big_orders
(4 rows)

                          QUERY PLAN                          
--------------------------------------------------------------
 Limit
   ->  Index Scan using idx_big_orders_customer on big_orders
(2 rows)

In the second plan, Limit sits above Sort, and the sort keeps only the 10 smallest rows (EXPLAIN ANALYZE reports this as top-N heapsort). The third has no sort node, because idx_big_orders_customer returns rows in customer_id order.

Example 8: aggregate nodes

Node	Behaviour
`Aggregate`	One result row for the whole input, no `GROUP BY`
`HashAggregate`	Groups rows in a hash table; no sorted input needed
`GroupAggregate`	Reads input sorted by the group key and finishes one group at a time

SQL

EXPLAIN (COSTS OFF) SELECT count(*), sum(amount) FROM big_orders;

EXPLAIN (COSTS OFF) SELECT status, count(*) FROM big_orders GROUP BY status;

EXPLAIN (COSTS OFF) SELECT customer_id, count(*) FROM big_orders GROUP BY customer_id ORDER BY customer_id;

Output

          QUERY PLAN          
------------------------------
 Aggregate
   ->  Seq Scan on big_orders
(2 rows)

          QUERY PLAN          
------------------------------
 HashAggregate
   Group Key: status
   ->  Seq Scan on big_orders
(3 rows)

                            QUERY PLAN                             
-------------------------------------------------------------------
 GroupAggregate
   Group Key: customer_id
   ->  Index Only Scan using idx_big_orders_customer on big_orders
(3 rows)

The third plan uses GroupAggregate because the index supplies the customer_id order, which also satisfies ORDER BY.

Example 9: estimated vs actual rows and stale statistics

The planner estimates row counts from statistics stored in pg_stats, gathered by ANALYZE (and by autovacuum). Wrong estimates lead to wrong plan choices. In EXPLAIN ANALYZE, compare rows= in the estimate with rows= in the actual part.

The table below is created with 10,000 rows, then 20,000 rows are added without running ANALYZE again.

SQL

CREATE TABLE stale_demo AS
SELECT i AS id, 'old' AS tag FROM generate_series(1, 10000) AS i;
ANALYZE stale_demo;

INSERT INTO stale_demo SELECT i, 'new' FROM generate_series(10001, 30000) AS i;

EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF)
SELECT * FROM stale_demo WHERE tag = 'new';

ANALYZE stale_demo;

EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF)
SELECT * FROM stale_demo WHERE tag = 'new';

SELECT attname, n_distinct, most_common_vals, most_common_freqs
FROM pg_stats
WHERE tablename = 'stale_demo' AND attname = 'tag';

Output

                                       QUERY PLAN                                       
----------------------------------------------------------------------------------------
 Seq Scan on stale_demo  (cost=0.00..448.88 rows=1 width=8) (actual rows=20000 loops=1)
   Filter: (tag = 'new'::text)
   Rows Removed by Filter: 10000
(3 rows)

                                         QUERY PLAN                                         
--------------------------------------------------------------------------------------------
 Seq Scan on stale_demo  (cost=0.00..527.00 rows=20000 width=8) (actual rows=20000 loops=1)
   Filter: (tag = 'new'::text)
   Rows Removed by Filter: 10000
(3 rows)

 attname | n_distinct | most_common_vals |   most_common_freqs    
---------+------------+------------------+------------------------
 tag     |          2 | {new,old}        | {0.6666667,0.33333334}
(1 row)

Before the second ANALYZE, the planner knew nothing about the value new, so it estimated 1 row against an actual 20000. After ANALYZE, the estimate matches the actual count. pg_stats holds the most common values and their frequencies that the planner used.

A gap of 10 times or more between estimate and actual is a sign of stale or insufficient statistics. Other causes are correlated columns (fix with CREATE STATISTICS) and expressions the planner cannot estimate.

Example 10: buffers

EXPLAIN (ANALYZE, BUFFERS) adds the number of 8 kB pages each node touched.

Counter	Meaning
`shared hit`	Page found in PostgreSQL's shared buffer cache
`shared read`	Page read from the operating system or disk
`shared dirtied` / `written`	Pages modified or flushed by this query
`temp read` / `written`	Spill files, usually from sorts or hashes exceeding `work_mem`

The first statement runs the lookup once, so its pages are in the cache and the counts below are hit values.

SQL

DO $$ BEGIN PERFORM * FROM big_orders WHERE id = 1234; END $$;

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM big_orders WHERE id = 1234;

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) FROM big_orders;

Output

                               QUERY PLAN                               
------------------------------------------------------------------------
 Index Scan using big_orders_pkey on big_orders (actual rows=1 loops=1)
   Index Cond: (id = 1234)
   Buffers: shared hit=4
(3 rows)

                        QUERY PLAN                         
-----------------------------------------------------------
 Aggregate (actual rows=1 loops=1)
   Buffers: shared hit=1408
   ->  Seq Scan on big_orders (actual rows=200000 loops=1)
         Buffers: shared hit=1408
(4 rows)

The index lookup touches a handful of pages (index levels plus one table page). The full count touches every page of the table. Fewer buffers means less work and less I/O. Whether a page counts as hit or read depends on the cache, but the total stays the same between runs while timings vary, so the total is a reliable measure when tuning.

MySQL 8 has EXPLAIN ANALYZE and EXPLAIN FORMAT=TREE. SQL Server shows plans with SET SHOWPLAN_XML or SET STATISTICS IO ON. Oracle uses EXPLAIN PLAN FOR with DBMS_XPLAN.DISPLAY.

Interview questions

What is the difference between EXPLAIN and EXPLAIN ANALYZE? EXPLAIN shows the estimated plan without running. EXPLAIN ANALYZE runs the query and adds actual rows, loops, and time.
How do you read a plan? Start at the most indented leaf nodes. Each node feeds its parent.
When is a hash join chosen over a nested loop? For large inputs without a useful index on the join column.
When is a merge join chosen? When both inputs are already sorted on the join key, or a sorted result is needed.
The estimate says 1 row, the actual is 50,000. What next? Run ANALYZE, check pg_stats, and consider extended statistics or a higher statistics target.
What does Filter mean compared with Index Cond? Index Cond narrows the index range. Filter discards rows after they are read.
Is EXPLAIN ANALYZE safe on UPDATE? It applies the change. Use a transaction and ROLLBACK.

Summary

EXPLAIN shows the estimated plan. EXPLAIN ANALYZE runs the query and shows actual rows, loops, and time.
Read plans from the innermost node outward. Index Cond limits the index range, Filter drops rows after reading.
Nested loop suits few outer rows with an inner index, hash join suits large equality joins, merge join suits sorted inputs.
Large gaps between estimated and actual rows point to stale statistics. Run ANALYZE.
BUFFERS counts the pages touched and is a stable way to compare plans.