Indexes and EXPLAIN

Why this lesson exists

"The orders page is slow for some customers" ends, nine times out of ten, at one query doing far more work than it needs to - usually reading the whole table because no index fits. You do not need to be a query tuner to fix most of these, but you need to read the plan the database chose, see where the time goes, and know which index would change it. EXPLAIN is how PostgreSQL tells you what it is doing; this lesson teaches you to read it on real plans from the lab's orders database.

What you need to know already: SQL WHERE, JOIN, ORDER BY ... LIMIT; psql and \timing (lesson 3); table sizes with \dt+ (lesson 3); MVCC and dead tuples (lessons 6-7).

The words you need first

• Plan - the tree of steps (nodes) the planner picked to run a query. Leaves read tables; parents join, sort, aggregate.
• Cost - the planner's estimate in arbitrary units (roughly: pages read, weighted). cost=0.00..1234.56 = cost to the first row .. cost to the last.
• Seq Scan - read every page of the table. Index Scan - walk an index, then fetch the matching rows. Index Only Scan - answer from the index alone. Bitmap Heap Scan - collect matching row locations from an index first, then read those pages in order.
• Selectivity - the fraction of rows a condition keeps. Indexes pay off for selective conditions (a few rows of many); for half the table a Seq Scan is faster.
• Statistics - per-column samples (pg_stats) that ANALYZE collects; the planner's row estimates come from them.
• B-tree - the default index type: equality and ranges, sorted order, multi-column.

Reading a plan

The query behind the slow page: the latest orders of one customer - customer 1234, a key account with a few thousand orders (most customers have about ten).

$ sudo -u postgres psql -d orders -c "explain select id, status, total_cents, created_at from orders where customer_id = 1234 order by created_at desc limit 10"
                               QUERY PLAN
------------------------------------------------------------------------
 Limit  (cost=999.47..999.49 rows=10 width=26)
   ->  Sort  (cost=999.47..1006.88 rows=2966 width=26)
         Sort Key: created_at DESC
         ->  Seq Scan on orders  (cost=0.00..935.38 rows=2966 width=26)
               Filter: (customer_id = 1234)
(5 rows)

Read from the innermost (most indented) node out: a Seq Scan on orders with a Filter on customer_id, feeding a Sort, feeding the Limit. rows= is how many rows the planner expects; width the average row size in bytes. EXPLAIN alone does not run the query. EXPLAIN ANALYZE does - and adds what really happened:

$ sudo -u postgres psql -d orders -c "explain (analyze) select id, status, total_cents, created_at from orders where customer_id = 1234 order by created_at desc limit 10"
                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=999.47..999.49 rows=10 width=26) (actual time=6.067..6.074 rows=10.00 loops=1)
   Buffers: shared hit=398
   ->  Sort  (cost=999.47..1006.88 rows=2966 width=26) (actual time=6.065..6.067 rows=10.00 loops=1)
         Sort Key: created_at DESC
         Sort Method: top-N heapsort  Memory: 17kB
         Buffers: shared hit=398
         ->  Seq Scan on orders  (cost=0.00..935.38 rows=2966 width=26) (actual time=0.084..4.629 rows=3000.00 loops=1)
               Filter: (customer_id = 1234)
               Rows Removed by Filter: 39990
               Buffers: shared hit=398
 Planning Time: 0.027 ms
 Execution Time: 6.158 ms
(12 rows)

The waste is plain: the whole table read, ten rows kept. Because ANALYZE really executes the statement, never run EXPLAIN ANALYZE on an UPDATE or DELETE you do not want to happen - wrap it: BEGIN; EXPLAIN ANALYZE UPDATE ...; ROLLBACK;.

The right index

An index on the filtered column:

$ sudo -u postgres psql -d orders -c "create index orders_customer_id_idx on orders (customer_id)"
CREATE INDEX
$ sudo -u postgres psql -d orders -c "explain (analyze) select id, status, total_cents, created_at from orders where customer_id = 1234 order by created_at desc limit 10"
                                                                     QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=538.45..538.47 rows=10 width=26) (actual time=2.548..2.554 rows=10.00 loops=1)
   Buffers: shared hit=42 read=4
   I/O Timings: shared read=0.018
   ->  Sort  (cost=538.45..545.86 rows=2966 width=26) (actual time=2.546..2.548 rows=10.00 loops=1)
         Sort Key: created_at DESC
         Sort Method: top-N heapsort  Memory: 17kB
         Buffers: shared hit=42 read=4
         I/O Timings: shared read=0.018
         ->  Bitmap Heap Scan on orders  (cost=39.28..474.35 rows=2966 width=26) (actual time=0.220..1.224 rows=3000.00 loops=1)
               Recheck Cond: (customer_id = 1234)
               Heap Blocks: exact=39
               Buffers: shared hit=42 read=4
               I/O Timings: shared read=0.018
               ->  Bitmap Index Scan on orders_customer_id_idx  (cost=0.00..38.53 rows=2966 width=0) (actual time=0.204..0.204 rows=3000.00 loops=1)
                     Index Cond: (customer_id = 1234)
                     Index Searches: 1
                     Buffers: shared hit=3 read=4
                     I/O Timings: shared read=0.018
 Planning:
   Buffers: shared hit=2 read=1
   I/O Timings: shared read=0.015
 Planning Time: 0.092 ms
 Execution Time: 2.568 ms
(23 rows)

Bitmap Index Scan on the new index finds the customer's rows, Bitmap Heap Scan fetches them, a sort picks the newest ten. Far less work - but customer 1234 is a big account with thousands of orders, and every one of them is fetched and sorted to return ten. Better still, an index that also has the order the query wants, so the database can read the newest 10 entries and stop:

$ sudo -u postgres psql -d orders -c "create index orders_customer_created_idx on orders (customer_id, created_at desc)"
CREATE INDEX
$ sudo -u postgres psql -d orders -c "explain (analyze) select id, status, total_cents, created_at from orders where customer_id = 1234 order by created_at desc limit 10"
                                                                     QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.29..5.98 rows=10 width=26) (actual time=0.025..0.035 rows=10.00 loops=1)
   Buffers: shared hit=6 read=2
   I/O Timings: shared read=0.009
   ->  Index Scan using orders_customer_created_idx on orders  (cost=0.29..1687.50 rows=2966 width=26) (actual time=0.023..0.029 rows=10.00 loops=1)
         Index Cond: (customer_id = 1234)
         Index Searches: 1
         Buffers: shared hit=6 read=2
         I/O Timings: shared read=0.009
 Planning:
   Buffers: shared hit=2 read=1
   I/O Timings: shared read=0.014
 Planning Time: 0.089 ms
 Execution Time: 0.046 ms
(13 rows)

Index Scan using orders_customer_created_idx with no sort node at all. The rules for multi-column B-tree indexes:

• Equality columns first, then the range or sort column: (customer_id, created_at) serves customer_id = ? ORDER BY created_at and customer_id = ? AND created_at > ?.
• The leading column matters: (customer_id, created_at) does not help WHERE created_at > ? alone much (18 can sometimes skip scan over a low-cardinality leading column, but do not design for it).
• One good composite index usually beats two single-column ones.

Now the first index is redundant - the composite one covers customer_id = ? too. Unused indexes are not free: every INSERT and most UPDATEs must update every index, they take disk and cache, and they stop HOT updates (an update that touches no indexed column can stay on the same page without touching any index). Drop what you do not use:

$ sudo -u postgres psql -d orders -c "drop index orders_customer_id_idx"
DROP INDEX
$ sudo -u postgres psql -d orders -c "select indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) as size from pg_stat_user_indexes where relname = 'orders' order by idx_scan"
        indexrelname         | idx_scan |  size
-----------------------------+----------+---------
 orders_customer_created_idx |        1 | 1336 kB
 orders_pkey                 |    40000 | 960 kB
(2 rows)

idx_scan = 0 for weeks on a production server (statistics are per server - check the replicas too) means the index is dead weight.

Other index shapes

A query that applies a function to an indexed column cannot use a plain index on it:

$ sudo -u postgres psql -d orders -c "explain select id from customers where lower(email) = '[email protected]'"
                         QUERY PLAN
------------------------------------------------------------
 Seq Scan on customers  (cost=0.00..105.00 rows=20 width=8)
   Filter: (lower(email) = '[email protected]'::text)
(2 rows)

Seq Scan despite the unique index on email - the index is on email, not lower(email).

When the plan is wrong: statistics

The planner chooses by estimated rows. When the estimate is far from actual rows, the plan can be wildly wrong (a nested loop over 100,000 rows it thought were 10). Estimates come from ANALYZE; autovacuum runs it after enough changes, but a bulk load or a big delete can leave stale statistics behind:

$ sudo -u postgres psql -d orders -c "select attname, n_distinct, most_common_vals from pg_stats where tablename = 'orders' and attname in ('status', 'customer_id')"
   attname   | n_distinct |           most_common_vals
-------------+------------+---------------------------------------
 customer_id |       4000 | {1234}
 status      |          5 | {paid,shipped,new,cancelled,refunded}
(2 rows)
$ sudo -u postgres psql -d orders -c "select relname, last_analyze, last_autoanalyze, n_mod_since_analyze from pg_stat_user_tables where relname = 'orders'"
 relname |       last_analyze        | last_autoanalyze | n_mod_since_analyze
---------+---------------------------+------------------+---------------------
 orders  | 2026-09-22 20:01:50.75+00 |                  |                   0
(1 row)

n_distinct positive = an absolute number of distinct values, negative = a fraction of the rows (-1 = unique). After a bulk change, an ANALYZE orders is cheap and often the whole fix.

Settings that change plans on every query - set them right for your hardware once:

A sort that spills:

$ sudo -u postgres psql -d orders -c "set work_mem = '64kB'" -c "explain (analyze, costs off) select * from orders order by total_cents" 2>&1 | grep -E 'Sort Method|Execution'
   Sort Method: external merge  Disk: 2440kB
 Execution Time: 56.804 ms
$ sudo -u postgres psql -d orders -c "set work_mem = '16MB'" -c "explain (analyze, costs off) select * from orders order by total_cents" 2>&1 | grep -E 'Sort Method|Execution'
   Sort Method: quicksort  Memory: 3666kB
 Execution Time: 34.660 ms

Raise work_mem for the role or the session that runs the big report (ALTER ROLE reporting SET work_mem = '256MB'), not globally.

Tools worth knowing: EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) pasted into a visualiser (explain.depesz.com, explain.dalibo.com) for big plans, and auto_explain (a contrib module that logs the plans of slow queries automatically - next lesson's neighbour).

In an interview: "A query got slow - how do you find out why?" - run it with EXPLAIN (ANALYZE, BUFFERS), read from the innermost node: a Seq Scan with a big Rows Removed by Filter, estimates far from actual rows (stale statistics -> ANALYZE), a sort spilling to disk (work_mem). Fix with an index that matches the query - equality columns first, then the sort/range column - built with CREATE INDEX CONCURRENTLY on a live table.

What you can do now

• Read EXPLAIN and EXPLAIN (ANALYZE): nodes, costs, estimated vs actual rows, buffers, time.
• Tell Seq, Index, Index Only and Bitmap scans apart and say when each is right.
• Design a composite index for a WHERE ... ORDER BY ... LIMIT query, and expression, partial and covering indexes.
• Find unused indexes with pg_stat_user_indexes.
• Spot stale statistics and sorts that spill, and fix them (ANALYZE, work_mem per role).