Indexes and EXPLAIN

Until now the tables had a handful of rows, so every query was instant and indexes were invisible. This lesson makes a table with one million rows so you can see the difference. For the short theory of indexes and EXPLAIN under an API, see Backend, Lesson 07; this lesson is the hands-on version, with real plans to read line by line. For load and caching topics around it see Full-Stack Q&A, Lesson 27. The orders shape comes from the shop of lesson 0, and the design rules (index your foreign keys) from lesson 4.

Words used in this lesson

• An index is a separate, sorted structure that points to table rows. PostgreSQL's default type is the B-tree.
• A B-tree is a balanced tree of sorted keys. Finding a key takes a few steps, even with millions of entries.
• The heap is the table's own storage, where the full rows live, in pages (blocks of 8 kB).
• A query plan is the step-by-step method the planner picked. EXPLAIN prints it. EXPLAIN ANALYZE also runs the query and prints what really happened.
• A scan is a way of reading a table: sequential (everything), index, bitmap, or index only.
• Selectivity is how small a fraction of the rows a condition keeps. "Very selective" means few rows match.
• Cost is the planner's made-up unit for effort. It compares plans. It is not milliseconds.
• Statistics are the numbers PostgreSQL keeps about each column (how many distinct values, the common ones). The planner uses them to estimate row counts. ANALYZE refreshes them.
• A buffer hit means a page was found in PostgreSQL's memory cache. A read means it had to be fetched from the operating system (which may still have it in its own cache).

What an index is, and how a B-tree helps

Without an index, to find the rows where customer_id = 777, PostgreSQL must read every page of the table and test every row. That is a sequential scan, and its cost grows with the table.

A B-tree index on customer_id keeps all the customer_id values sorted, each with a pointer (a page number and a position) to its row. Finding 777 means walking down a tree of just a few levels: the top page says "values below 30000 are on the left", and so on, down to a leaf page that lists the pointers for 777. A million rows is only a few levels deep (my primary key lookup below touched 3 index pages and 1 table page). Then PostgreSQL visits the few table pages the pointers name.

A sketch (made-up numbers, not real output):

            [ root page: 1..16000 | 16001..33000 | ... ]                 |                      |     [ inner pages ... ]        [ inner pages ... ]        | [ leaf: 770 -> (page 19, row 4), 777 -> (page 8841, row 2), (page 51033, row 7) ... ]

Because the keys are sorted, a B-tree is good for =, <, >, BETWEEN, IN, ORDER BY and LIKE 'abc%' (with the right operator class or collation), and for min/max. It cannot help with LIKE '%abc', or with a condition that runs a function on the column (more on both below).

The table: one million orders

Run this in an empty database. It builds orders_big with 1,000,000 rows: 50,000 customers, four statuses (paid is half of them), an amount, a timestamp every 30 seconds, and a text column to make rows realistic.

SELECT setseed(0.42);CREATE TABLE orders_big ASSELECT g AS id,       1 + (random() * 49999)::int AS customer_id,       (ARRAY['new','paid','paid','paid','shipped','shipped','cancelled'])[1 + (random()*6)::int] AS status,       100 + (random() * 99900)::int AS amount_cents,       timestamptz '2024-01-01 00:00+00' + g * interval '30 seconds' AS ordered_at,       md5(g::text) AS noteFROM generate_series(1, 1000000) g;ALTER TABLE orders_big ADD PRIMARY KEY (id);ANALYZE orders_big;

On this machine creating the rows took 4.4 seconds and the primary key 0.8 seconds. A look at the size and contents:

\dt+ orders_bigSELECT * FROM orders_big LIMIT 3;SELECT status, count(*) FROM orders_big GROUP BY status ORDER BY 2 DESC;

 Schema |    Name    | Type  |  Owner   | Persistence | Access method | Size  --------+------------+-------+----------+-------------+---------------+------- public | orders_big | table | postgres | permanent   | heap          | 96 MB  id | customer_id | status | amount_cents |       ordered_at       |               note               ----+-------------+--------+--------------+------------------------+----------------------------------  1 |       37148 | paid   |        93667 | 2024-01-01 00:00:30+00 | c4ca4238a0b923820dcc509a6f75849b  2 |        3217 | paid   |        21465 | 2024-01-01 00:01:00+00 | c81e728d9d4c2f636f067f89cc14862c  3 |       24544 | paid   |        57592 | 2024-01-01 00:01:30+00 | eccbc87e4b5ce2fe28308fd9f2a7baf3  status   | count  -----------+-------- paid      | 499934 shipped   | 333071 new       |  83525 cancelled |  83470

The table is 96 MB, which is 12,224 pages of 8 kB. Remember that number.

Reading EXPLAIN, line by line

EXPLAIN alone shows the plan and the planner's estimates, without running the query. Add ANALYZE to run it and see actual numbers. Add BUFFERS to see how many pages were touched. Careful: EXPLAIN ANALYZE really executes the statement, so wrap INSERT, UPDATE and DELETE in a transaction you roll back (lesson 6).

The query: all orders of customer 777. There is no index on customer_id yet. This is the default plan, with parallel workers on:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders_big WHERE customer_id = 777;

 Gather  (cost=1000.00..18434.43 rows=21 width=59) (actual time=2.295..38.743 rows=18 loops=1)   Workers Planned: 2   Workers Launched: 2   Buffers: shared hit=2401 read=9823   ->  Parallel Seq Scan on orders_big  (cost=0.00..17432.33 rows=9 width=59) (actual time=5.342..26.260 rows=6 loops=3)         Filter: (customer_id = 777)         Rows Removed by Filter: 333327         Buffers: shared hit=2401 read=9823 Planning Time: 0.016 ms Execution Time: 38.762 ms

For the rest of the lesson I turn parallelism off so the plan is one simple line:

SET max_parallel_workers_per_gather = 0;EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders_big WHERE customer_id = 777;

 Seq Scan on orders_big  (cost=0.00..24724.00 rows=21 width=59) (actual time=2.167..63.074 rows=18 loops=1)   Filter: (customer_id = 777)   Rows Removed by Filter: 999982   Buffers: shared hit=2594 read=9630 Planning:   Buffers: shared hit=97 Planning Time: 0.421 ms Execution Time: 63.156 ms

Read it line by line.

• Seq Scan on orders_big: the node. It reads the whole table in order. Plans are trees; the innermost, most indented nodes run first and feed their parent.
• cost=0.00..24724.00: two numbers. The first is the startup cost (effort before the first row can come out). The second is the total cost. The unit is arbitrary, but it is not magic: here it is 12,224 pages x 1.0 (seq_page_cost) + 1,000,000 rows x 0.01 (cpu_tuple_cost) + 1,000,000 rows x 0.0025 (cpu_operator_cost, for testing the filter) = 24,724. That matches exactly.
• rows=21 (first set of numbers): the planner's estimate of how many rows come out. width=59: the average row size in bytes.
• actual time=2.167..63.074: real milliseconds to the first row and to the last row. rows=18: the real row count. loops=1: how many times this node ran. When loops is more than 1, the time and rows are per loop, so multiply.
• Filter and Rows Removed by Filter: 999982: the database read 1,000,000 rows, kept 18, and threw away 999,982. Large numbers here are the clue that an index would help.
• Buffers: shared hit=2594 read=9630: 2,594 pages came from PostgreSQL's cache and 9,630 had to be fetched from the operating system. Together 12,224 pages: the whole table.
• Planning Time and Execution Time: parse-and-plan time, and run time. About 63 ms here, all of it reading and testing a million rows.

Compare the estimate (21 rows) with the actual (18). When these two are close, the statistics are healthy. A large gap (10x or more) is the first thing to look at in a slow query.

Now add an index

CREATE INDEX orders_big_customer_idx ON orders_big (customer_id);

This took 0.43 seconds here, and the index is 7.8 MB (the table is 96 MB; the primary key index is 21 MB). Run the same query:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders_big WHERE customer_id = 777;

 Bitmap Heap Scan on orders_big  (cost=4.59..86.24 rows=21 width=59) (actual time=0.036..0.140 rows=18 loops=1)   Recheck Cond: (customer_id = 777)   Heap Blocks: exact=18   Buffers: shared hit=4 read=17   ->  Bitmap Index Scan on orders_big_customer_idx  (cost=0.00..4.58 rows=21 width=0) (actual time=0.024..0.024 rows=18 loops=1)         Index Cond: (customer_id = 777)         Buffers: shared read=3 Planning Time: 0.071 ms Execution Time: 0.148 ms

From 63 ms to 0.15 ms (cold) on this machine. Read from the inside out:

1. Bitmap Index Scan on orders_big_customer_idx walks the index for customer_id = 777 and reads only 3 index pages (Buffers: shared read=3). It builds a bitmap: an in-memory list of which table pages contain matching rows. It returns no rows itself (width=0).
2. Bitmap Heap Scan reads exactly those table pages, in physical order (Heap Blocks: exact=18), and rechecks the condition on each row (Recheck Cond).
3. Total pages touched: 3 + 18 = 21, instead of 12,224.

The 18 matching rows were spread over 18 different pages, since customer_id is random compared to the insert order. The second run of the same query, with everything cached, took 0.019 ms. Always run twice and know whether you are looking at a cold or a warm cache.

The three scan types, and when each wins

Index Scan. A lookup by primary key returns one row:

 Index Scan using orders_big_pkey on orders_big  (cost=0.42..8.44 rows=1 width=59) (actual time=0.089..0.091 rows=1 loops=1)   Index Cond: (id = 500000)   Buffers: shared read=4 Planning Time: 0.085 ms Execution Time: 0.106 ms

Four pages: three index levels and one table page. Index scans also win when the rows are in the same order as the table. ordered_at grows with id, so a day of orders sits together (the planner knows this from the correlation statistic, below). One day, 2,880 rows:

CREATE INDEX orders_big_ordered_at_idx ON orders_big (ordered_at);EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders_bigWHERE ordered_at >= '2024-06-01' AND ordered_at < '2024-06-02';

 Index Scan using orders_big_ordered_at_idx on orders_big  (cost=0.42..129.11 rows=2884 width=59) (actual time=0.039..0.383 rows=2880 loops=1)   Index Cond: ((ordered_at >= '2024-06-01 00:00:00+00'::timestamp with time zone) AND (ordered_at < '2024-06-02 00:00:00+00'::timestamp with time zone))   Buffers: shared hit=37 read=10 Execution Time: 0.506 ms

Rows 2,880, pages 47 (37 + 10). An index also gives sorted output for free, so ORDER BY ordered_at DESC LIMIT 5 reads the end of the index and stops:

 Limit  (cost=0.42..0.62 rows=5 width=59) (actual time=0.028..0.030 rows=5 loops=1)   ->  Index Scan Backward using orders_big_ordered_at_idx on orders_big  ... Execution Time: 0.038 ms

Without an index the same top-5 on amount_cents has to read and sort everything. That plan is worth a look, because of the Sort node:

 Limit  (cost=38833.64..38833.65 rows=5 width=59) (actual time=137.045..137.049 rows=5 loops=1)   ->  Sort  (cost=38833.64..41333.64 rows=1000000 width=59) (actual time=137.043..137.045 rows=5 loops=1)         Sort Key: amount_cents DESC         Sort Method: top-N heapsort  Memory: 26kB         ->  Seq Scan on orders_big  (cost=0.00..22224.00 rows=1000000 width=59) (actual time=0.005..61.748 rows=1000000 loops=1) Execution Time: 137.067 ms

top-N heapsort is the cheap sort used with LIMIT: it only keeps 5 rows (26 kB). Sort Method: external merge Disk: ... would mean the sort did not fit in work_mem and used disk, which is a classic reason for slowness.

Bitmap scan on a range. 31 customers, 654 rows spread over 633 pages:

 Bitmap Heap Scan on orders_big  (cost=11.05..2111.67 rows=646 width=59) (actual time=0.125..3.531 rows=654 loops=1)   Recheck Cond: ((customer_id >= 100) AND (customer_id <= 130))   Heap Blocks: exact=633   ->  Bitmap Index Scan on orders_big_customer_idx  (cost=0.00..10.89 rows=646 width=0) (actual time=0.059..0.059 rows=654 loops=1) Execution Time: 3.575 ms

Here a plain index scan would jump to a random page for every row. The bitmap sorts those page visits first and touches each page once. I forced the plain Index Scan for a similar range (customers 100 to 700, 12,037 rows) with SET enable_bitmapscan = off: it took 11.5 ms, almost the same as the bitmap's 11.1 ms. So on this warm cache it did not matter much; on a cold disk it matters more. (Never leave enable_* switches off in real code; they are only for experiments.)

Index Only Scan. If the query needs only customer_id, the answer is in the index. I first got a bitmap scan, because PostgreSQL could not be sure the rows were visible to every transaction. After VACUUM marked the pages "all visible", the same query became:

VACUUM orders_big;EXPLAIN (ANALYZE, BUFFERS) SELECT customer_id FROM orders_big WHERE customer_id = 777;

 Index Only Scan using orders_big_customer_idx on orders_big  (cost=0.42..8.79 rows=21 width=4) (actual time=0.037..0.040 rows=18 loops=1)   Index Cond: (customer_id = 777)   Heap Fetches: 0   Buffers: shared hit=4 Execution Time: 0.057 ms

Heap Fetches: 0 is the proof: it never went to the table. Four pages for 18 rows.

Why the index is sometimes not used

"I made an index and the query is still slow" is a common problem. These are the usual reasons. Each is something I ran.

1. The query needs most of the table. Reading an index and then jumping around the table is more work than just reading the table. For status IN ('paid','shipped') (83% of rows), with an index on status:

 Seq Scan on orders_big  (cost=0.00..24724.00 rows=832200 width=59) (actual time=0.014..105.347 rows=833005 loops=1)   Filter: (status = ANY ('{paid,shipped}'::text[]))   Rows Removed by Filter: 166995 Execution Time: 137.308 ms

The planner was right. I forced the index (SET enable_seqscan = off) and got a bitmap scan at 133.6 ms: no better. Even status = 'paid' (50%) was only a coin toss: a bitmap scan at 130 ms. And an index on a column with 4 distinct values (like status) is rarely worth having by itself.

2. A function or calculation on the column. The index holds customer_id, not customer_id + 0:

 Seq Scan on orders_big  (cost=0.00..27224.00 rows=5000 width=59)   Filter: ((customer_id + 0) = 777)

The same happens with WHERE id::text = '500000', even with a primary key on id. Rewrite the condition so the bare column is compared. For dates that means a range, as above, and not date_trunc('day', ordered_at) = '2024-06-01', which took 135 ms by sequential scan. If you really need the function, index the expression. This fails for timestamptz because date_trunc on it depends on the session time zone:

ERROR:  functions in index expression must be marked IMMUTABLE

Pinning the zone makes it immutable. CREATE INDEX ON orders_big (date_trunc('day', ordered_at AT TIME ZONE 'UTC')) worked, and the matching query ran in 0.7 ms with a Bitmap Index Scan. The query must repeat the same expression exactly.

3. The wrong column order in a composite index. See the next section.

4. The table is small. On the 7-row shop, a sequential scan is the cheapest plan, so the planner ignores indexes. That is correct, not a bug. Test on realistic data.

5. Stale or missing statistics. The planner picks plans from estimates. See "Statistics" below.

6. A leading wildcard or the wrong operator class. LIKE '%abc' cannot use a B-tree. A text index with a non-C collation may not serve LIKE 'abc%' either; that needs text_pattern_ops (I did not test this here).

Composite indexes: column order matters

An index on (a, b) is sorted by a, and within equal a by b, like a phone book sorted by surname, then first name. You can find a surname fast. You cannot find all people named "Ana" fast. A composite index helps queries that use its leftmost columns.

Take WHERE customer_id = 777 AND status = 'paid'. I tried both orders.

Index (status, customer_id) (12 MB), query on both columns: fine.

 Index Scan using comp_status_cust on orders_big  (cost=0.42..41.97 rows=10 width=59) (actual time=0.040..0.056 rows=10 loops=1)   Index Cond: ((status = 'paid'::text) AND (customer_id = 777)) Execution Time: 0.077 ms

But the query on customer_id alone (the column is second in this index):

 Index Scan using comp_status_cust on orders_big  (cost=0.42..13646.73 rows=21 width=59) (actual time=1.838..10.591 rows=18 loops=1)   Index Cond: (customer_id = 777)   Buffers: shared hit=18 read=1509 Execution Time: 10.622 ms

It did use the index, but it had to scan a large part of it (1,509 pages) because the index is sorted by status first. 10.6 ms, against 0.07 ms with the right order. Now (customer_id, status):

 Bitmap Index Scan on comp_cust_status  (cost=0.00..4.52 rows=10 width=0) (actual time=0.012..0.012 rows=10 loops=1)   Index Cond: ((customer_id = 777) AND (status = 'paid'::text))   Buffers: shared hit=3

With this order, both WHERE customer_id = ... and WHERE customer_id = ... AND status = ... use 3 index pages. A query on status alone does not use it (it chose a sequential scan, 92 ms).

Rules of thumb for the order:

1. Columns compared with = first, then the column used for a range (>, BETWEEN) or for ORDER BY last.
2. Among the equality columns, put the one that is used alone most often first. Putting the most selective first is a common guess, but "which queries can use the index" matters more.
3. You do not need a separate index on (a) if you have (a, b). The composite one serves both.

Covering indexes with INCLUDE

An index-only scan needs every column of the query to be in the index. You can add extra payload columns with INCLUDE. They are stored in the index but not used for searching or sorting. Example query: "amounts and times of paid orders for a customer, newest first".

CREATE INDEX cover_idx ON orders_big (customer_id, status, ordered_at) INCLUDE (amount_cents);VACUUM orders_big;EXPLAIN (ANALYZE, BUFFERS)SELECT amount_cents, ordered_at FROM orders_bigWHERE customer_id = 777 AND status = 'paid' ORDER BY ordered_at DESC;

 Index Only Scan Backward using cover_idx on orders_big  (cost=0.42..4.62 rows=10 width=12) (actual time=0.057..0.060 rows=10 loops=1)   Index Cond: ((customer_id = 777) AND (status = 'paid'::text))   Heap Fetches: 0   Buffers: shared hit=1 read=3 Execution Time: 0.083 ms

No Sort node (the index is already ordered by ordered_at, read backward) and no heap visits. It is the fastest shape for this query. But look at the price: this index is 47 MB, half of the table. Without INCLUDE, the index (customer_id, status, ordered_at) ran the same query in 0.126 ms as a bitmap scan plus a sort of 10 rows, so the covering version saved very little time here. A covering index pays off for hot, repeated queries; it is not a default.

Partial indexes

If a query always filters on a fixed condition, index only those rows. The dashboard question "the 10 oldest orders still in status new":

SELECT id, ordered_at FROM orders_big WHERE status = 'new' ORDER BY ordered_at LIMIT 10;

Without a helpful index: a sequential scan of 1,000,000 rows, a sort, and 83 ms. Now a partial index:

CREATE INDEX new_orders_idx ON orders_big (ordered_at) WHERE status = 'new';

 Limit  (cost=0.29..0.67 rows=10 width=12) (actual time=0.037..0.043 rows=10 loops=1)   ->  Index Scan using new_orders_idx on orders_big  (cost=0.29..3225.25 rows=84633 width=12) (actual time=0.036..0.040 rows=10 loops=1) Execution Time: 0.058 ms

And the size: 1,848 kB, against 21 MB for a full index on ordered_at. It indexes only the 8% of rows that are new. A partial index is smaller, cheaper to write, and fits the question exactly. The query's WHERE must imply the index's WHERE; the same query for 'paid' cannot use it. This pattern also gives unique rules for a subset (the soft-delete email index in lesson 4).

Joins and the plan nodes you will meet

A join plan is the same tree with join nodes. Orders of customer 777 with the customer's name (I made a 50,000-row customers_big with a primary key):

 Nested Loop  (cost=4.88..94.75 rows=21 width=22) (actual time=0.052..0.151 rows=18 loops=1)   ->  Index Scan using customers_big_pkey on customers_big c  (cost=0.29..8.31 rows=1 width=18) (actual time=0.015..0.016 rows=1 loops=1)         Index Cond: (id = 777)   ->  Bitmap Heap Scan on orders_big o  (cost=4.59..86.24 rows=21 width=12) (actual time=0.031..0.127 rows=18 loops=1)         Recheck Cond: (customer_id = 777)         ->  Bitmap Index Scan on orders_big_customer_idx  (cost=0.00..4.58 rows=21 width=0) (actual time=0.020..0.020 rows=18 loops=1) Execution Time: 0.175 ms

Nested Loop: for each row from the first child (one customer), run the second child (find the orders). It is the right join for a few outer rows plus an index on the inner side. For customers below 20 (371 orders), the planner chose differently:

 Hash Join  (cost=1452.49..2809.35 rows=396 width=18) (actual time=11.970..14.173 rows=371 loops=1)   Hash Cond: (o.customer_id = c.id)   ->  Bitmap Heap Scan on orders_big o  ...   ->  Hash  (cost=820.00..820.00 rows=50000 width=18) (actual time=11.849..11.850 rows=50000 loops=1)         Buckets: 65536  Batches: 1  Memory Usage: 3051kB         ->  Seq Scan on customers_big c  ...

Hash Join: build an in-memory hash table from one input (Hash), then probe it with each row of the other. Good for bigger inputs without a useful index. Batches: 1 means it fit in memory; more batches mean it spilled to disk. The third kind is Merge Join: both inputs sorted on the join key, then walked together. It shines when the data is already sorted.

The cost of indexes: every write pays

An index is a second copy of some data that must be kept in step. Each INSERT adds an entry to every index, each DELETE marks entries dead, and each UPDATE that changes an indexed column (or cannot reuse the same page) writes new entries. To measure, I made three copies of half the table (500,000 rows) with different indexes and timed the same INSERT ... SELECT:

The four-index table was 11 times slower to load than the bare table, and its indexes are bigger than the table itself. Then UPDATE ... SET amount_cents = amount_cents + 1 WHERE id <= 100000: 0.56 s with no indexes against 2.37 s with four. In w4 the updated column was in a covering index, which forces a rewrite of that index entry. When no indexed column changes, PostgreSQL can often do a cheaper "HOT" update; I did not measure that separately.

Indexes also use disk and memory, make VACUUM work longer, and slow down bulk loads (drop the secondary indexes, load, recreate). Practical rules:

• Index what your real queries filter, join and sort on. Not every column.
• Check which indexes are used: SELECT relname, indexrelname, idx_scan FROM pg_stat_user_indexes;. An index with idx_scan = 0 over a long time is a candidate to drop. (Check on a primary, and on every replica, before dropping.)
• Do not keep (a) and (a, b) together without a reason.
• Create indexes on a live table with CREATE INDEX CONCURRENTLY so writes are not blocked (lesson 7).

Statistics and ANALYZE

The planner does not look at the data when it plans. It uses statistics stored in pg_stats, built by ANALYZE from a sample of the table. Autovacuum runs it in the background after enough rows change. You can read what it knows:

SELECT attname, null_frac, n_distinct, most_common_vals, most_common_freqsFROM pg_stats WHERE tablename = 'orders_big' AND attname = 'status';

 attname | null_frac | n_distinct |       most_common_vals       |               most_common_freqs                ---------+-----------+------------+------------------------------+------------------------------------------------ status  |         0 |          4 | {paid,shipped,new,cancelled} | {0.50116664,0.33103332,0.084633335,0.08316667}

This is how the planner knew status = 'paid' returns about 50% of the rows (501,167 estimated, 499,934 real). The correlation column from the same view said id and ordered_at have correlation 1 (stored in order), and customer_id 0.002 (random). That is why range queries on ordered_at use a plain index scan and on customer_id a bitmap scan.

Statistics go stale after big changes. To show it, I made a table with autovacuum off, filled it with 100,000 rows of 'a', ran ANALYZE, then added 100,000 rows of 'b' and asked for the 'b' rows:

 Index Scan using stale_kind_idx on stale  (cost=0.29..4.31 rows=1 width=6) (actual time=0.015..10.640 rows=100000 loops=1)   Index Cond: (kind = 'b'::text)

The planner expected 1 row and got 100,000. pg_stat_user_tables showed n_mod_since_analyze = 200000. After ANALYZE stale; the estimate became rows=99727. Here the plan shape stayed the same, but a 100,000x wrong estimate makes the planner choose a nested loop where a hash join belongs, and that is how a query goes from milliseconds to minutes after a big import. So: run ANALYZE after bulk loads and big deletes, and compare rows= estimates with actual rows in every slow plan.

For columns with skewed data, you can raise the sample detail with ALTER TABLE ... ALTER COLUMN ... SET STATISTICS 500, then ANALYZE. For columns that depend on each other (city and country), CREATE STATISTICS helps. I read about both and did not run them here.

A workflow for a slow query

1. Get the real slow query and real parameters. Run EXPLAIN (ANALYZE, BUFFERS). Run it twice.
2. Find the node with the most actual time (remember loops). Look for Rows Removed by Filter in the millions, Sort Method: external merge, Heap Fetches high, or rows= estimates far from actual.
3. Estimates wrong? ANALYZE, then look at pg_stats.
4. Condition not indexable? Rewrite it (bare column, ranges, EXISTS).
5. Still a full scan on a selective filter? Add the narrowest index that fits: composite in the right order, partial if the filter is fixed, INCLUDE for a hot read.
6. Re-run the plan and confirm. Check the write cost.

Pagination is a good test. ORDER BY id LIMIT 20 OFFSET 900000 has to walk past 900,020 index entries: 258 ms. The keyset form WHERE id > 900000 ORDER BY id LIMIT 20 jumps straight there, in 0.06 ms (lesson 0 introduced this):

 Limit  (cost=0.42..1.24 rows=20 width=8) (actual time=0.034..0.040 rows=20 loops=1)   ->  Index Scan using orders_big_pkey on orders_big  (cost=0.42..4042.27 rows=99191 width=8) (actual time=0.033..0.037 rows=20 loops=1)         Index Cond: (id > 900000) Execution Time: 0.064 ms

Try it

Use the orders_big table above (turn off parallelism the same way).

1. Drop the customer_id index and run EXPLAIN (ANALYZE, BUFFERS) for customer_id = 777. Find Rows Removed by Filter and the buffer count. Then create the index and compare.
2. Compare WHERE amount_cents = 5000 before and after an index. How many rows match? Is the index worth it?
3. Create an index on (status, ordered_at). Which of these can use it well: WHERE status = 'new', WHERE ordered_at > '2024-12-01', WHERE status = 'new' ORDER BY ordered_at LIMIT 5?
4. Why does WHERE lower(note) = 'abc' not use an index on note? Fix it.
5. Run INSERT INTO orders_big ... for 100,000 new rows inside BEGIN; ... ROLLBACK; with EXPLAIN ANALYZE, once with and once without an extra index. What do you see?

Answers

1. Without the index: a sequential scan, Rows Removed by Filter about 999,982, about 12,000 buffers, tens of milliseconds. With it: a bitmap scan with about 21 buffers and a fraction of a millisecond (my numbers above).
2. About 10 rows should match (not run: amounts are random between 100 and 100,000, so each value is rare), so the index is very selective and a good fit for exact lookups. Whether it is worth it depends on whether you actually run that query.
3. status = 'new' and status = 'new' ORDER BY ordered_at LIMIT 5 use it well (the second also avoids a sort). ordered_at > ... alone does not: the leading column is status.
4. The index stores note, not lower(note). Create CREATE INDEX ON orders_big (lower(note)); and keep the expression identical in the query, or compare without the function.
5. The insert with the extra index takes visibly longer (my table: 11 times slower with four indexes). The ROLLBACK throws the rows away, but the work was done.

Interview questions

Key takeaways

• An index is a sorted structure: it makes reads of a few rows fast, and every write pay for it.
• Read plans from the inside out. Compare rows= estimated and actual, look at Rows Removed by Filter, loops and Buffers. Use EXPLAIN (ANALYZE, BUFFERS) and run it twice.
• Seq scan for large shares, index scan for few rows or sorted output, bitmap scan in the middle, index-only scan when the index covers the query.
• No index use usually means: low selectivity, a function on the column, wrong composite order, a small table, or bad statistics.
• Composite indexes work from the left: equality columns first, range or sort column last.
• Partial indexes and INCLUDE are sharp tools: use them for specific hot queries.
• ANALYZE after bulk changes. Stale statistics produce wrong plans.

What's next

Lesson 6: Transactions, Isolation and Locks. Indexes make single queries fast. Next: what happens when many sessions read and write the same rows at once, how a transaction keeps a group of changes all-or-nothing, and how locks and isolation levels prevent (or cause) trouble. Lesson 7 returns to CREATE INDEX CONCURRENTLY and other safe migrations. For Prisma and SQL questions see Full-Stack Q&A, Lesson 08.