Postgres EXPLAIN ANALYZE: read the plan, pick the index
From the work/ 10 min read
How to read EXPLAIN ANALYZE, tell a Seq Scan from an Index Scan, choose index columns for WHERE and ORDER BY, and measure the write cost. Tested on PGlite.
- Databases
- Performance
In this article (9)
EXPLAIN ANALYZE runs the query and shows the real plan: what Postgres read, how many rows it discarded and how long it took. A Seq Scan reading the whole table for a few rows usually means an index is missing on the WHERE and ORDER BY columns. With the right index, our test query dropped from 15.4 to 0.12 ms.
Below: how to read the plan line by line, the read nodes you will see, how to choose the index columns, how to measure before and after, what each index costs on writes and how to find the ones nobody uses. All of it comes from a run on PGlite 0.5.10 (PostgreSQL 18.6 in WebAssembly) with 200,000 invented orders; the timings are from that environment, not from your server.
Reading EXPLAIN ANALYZE step by step
Putting EXPLAIN ANALYZE in front of a query really runs it and returns the plan with the real numbers. This is the test query: a customer's last 20 orders, on an orders table with no index other than the primary key. (The 4 seconds I mention in the Reel are an illustrative example; the timings in this article are measured.)
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, total
FROM orders
WHERE customer_id = 4242
ORDER BY created_at DESC
LIMIT 20;
Limit (cost=4168.06..4168.11 rows=20 width=22) (actual time=15.375..15.402 rows=20.00 loops=1)
Buffers: shared hit=1667
-> Sort (cost=4168.06..4168.16 rows=40 width=22) (actual time=15.372..15.382 rows=20.00 loops=1)
Sort Key: created_at DESC
Sort Method: quicksort Memory: 19kB
Buffers: shared hit=1667
-> Seq Scan on orders (cost=0.00..4167.00 rows=40 width=22) (actual time=0.129..15.287 rows=40.00 loops=1)
Filter: (customer_id = 4242)
Rows Removed by Filter: 199960
Buffers: shared hit=1667
Planning Time: 0.109 ms
Execution Time: 15.432 ms
- The plan reads from the inside out:
Seq Scanproduces rows,Sortorders them andLimitkeeps 20. cost=0.00..4167.00is an estimate in arbitrary units, from startup to total; a node's cost includes its children's.actual time=0.129..15.287 rows=40.00 loops=1is what was measured: milliseconds to the first and the last row, rows, and how many times the node ran. Withloopsabove 1, multiply the time by it.FilterandRows Removed by Filter: 199960: it read 200,000 rows to keep 40. That number gives away a missing index.Buffers: shared hit=1667are the pages read, all already in Postgres's cache (hit). In PostgreSQL 18,ANALYZEturnsBUFFERSon by itself; writing it does no harm.Planning Timeis how long it took to choose the plan andExecution Timehow long it took to run it.
Scroll the figure sideways to see all of it
Two warnings from the EXPLAIN documentation. Because EXPLAIN ANALYZE executes the query, an INSERT, UPDATE or DELETE does change data: wrap it in BEGIN and ROLLBACK. And results on a small table cannot be extrapolated to a large one: measuring has its own cost too.
Seq Scan vs. Index Scan
A Seq Scan reads the whole table, row by row. An Index Scan walks the index, which is sorted, and goes only to the rows it needs. A Bitmap Heap Scan sits in between: a Bitmap Index Scan marks where the rows are and then Postgres reads them by page, in physical order. Neither is always better: according to the documentation, when a query needs a large fraction of the table, sorting everything is often faster than hopping back and forth through the index. That is why the planner decides from statistics, and why having an index does not guarantee it gets used.
Scroll the figure sideways to see all of it
Choosing the index columns: WHERE and ORDER BY
The rule: equality columns from the WHERE first, then the ORDER BY columns. The multicolumn index documentation says equality constraints on the leading columns limit the part of the index that is scanned, and the page on indexes and ORDER BY says that with an ORDER BY plus LIMIT n, a matching index returns the first n rows directly, without scanning the rest. For our query:
CREATE INDEX orders_customer_created_idx ON orders (customer_id, created_at DESC);
ANALYZE orders;
Limit (cost=0.42..82.77 rows=20 width=22) (actual time=0.021..0.094 rows=20.00 loops=1)
Buffers: shared hit=24
-> Index Scan using orders_customer_created_idx on orders (cost=0.42..165.11 rows=40 width=22) (actual time=0.018..0.065 rows=20.00 loops=1)
Index Cond: (customer_id = 4242)
Buffers: shared hit=24
Planning Time: 0.086 ms
Execution Time: 0.123 ms
customer_idgoes first because the query compares it with=: Postgres jumps to that customer's entries.created_at DESCgoes next: within each customer the entries are already in the order the query asks for, so theSortdisappears andLimitstops at 20.ANALYZE ordersrefreshes the statistics the planner uses.- The plan is now an
Index Scanwith anIndex Cond, noFilterand noSort, reading 24 pages instead of 1667.
In production, build the index with CREATE INDEX CONCURRENTLY: according to the CREATE INDEX documentation, a normal build blocks writes to the table while it runs. CONCURRENTLY does not block them, but it takes longer, cannot run inside a transaction and, if it fails, leaves an INVALID index you have to drop and create again.
Order matters. With the columns reversed, (created_at, customer_id), Postgres used an Index Scan Backward: it walks from every customer's newest entries and checks customer_id on each until it has 20. It read 400 pages and took 3.6 ms, about 17 times the pages and 29 times the time of the right index. The documentation anticipates it: conditions on columns to the right are checked in the index, but do not necessarily reduce what is scanned. And if you need ORDER BY a ASC, b DESC, a plain index will not do: define it as (a ASC, b DESC); but for DESC on every column a plain one is enough, because Postgres walks it backward.
Two limits: the documentation asks you to use multicolumn indexes sparingly, and says that beyond three columns they rarely help. And if you only query a subset of rows, a partial index is smaller; that is the case of the jobs table in the Postgres job queue with SKIP LOCKED, which indexes only the pending ones.
Measuring before and after
Always compare the same query in the same state. Our recipe: ANALYZE after loading the data and after each index, three runs in a row per variant, keeping the third (warm cache). Compare Buffers as well as milliseconds: pages read barely change between runs; time changes quite a bit.
| Variant | Read node | Buffers | Execution Time |
|---|---|---|---|
| A · no index | Seq Scan + Sort | 1667 | 15.432 ms |
B · (customer_id) | Bitmap Heap Scan + Sort | 42 | 0.468 ms |
D · (created_at, customer_id) | Index Scan Backward | 400 | 3.596 ms |
C · (customer_id, created_at DESC) | Index Scan, no Sort | 24 | 0.123 ms |
An index on customer_id alone already helps a lot: with about 40 orders per customer, sorting them is cheap. With a customer who has thousands of orders that Sort would stop being cheap, though that is reasoning, not something we measured.
Scroll the figure sideways to see all of it
What indexes cost on writes
Every INSERT adds an entry to every index on the table, and so does every UPDATE of an indexed column. We measured it: inserting 100,000 rows into a fresh table, three runs per variant, on PGlite.
- Primary key only: 684, 739 and 703 ms (median 703).
- One more index: 937, 729 and 720 ms (median 729, within the noise).
- Three more indexes: 1257, 1501 and 1210 ms (median 1257, about 1.8 times the baseline).
It is a small local test, but the direction is clear. An index no query uses only pays that tax, and it takes disk too: the status index in the test weighed 1376 kB.
Finding unused indexes
The pg_stat_user_indexes view counts in idx_scan how many scans started on each index. This query lists the ones at zero, leaving out unique indexes (they protect data even when nobody reads them):
SELECT s.relname AS table_name, s.indexrelname AS index_name, s.idx_scan,
pg_size_pretty(pg_relation_size(s.indexrelid)) AS size
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0 AND NOT i.indisunique
ORDER BY pg_relation_size(s.indexrelid) DESC;
table_name | index_name | idx_scan | size
orders | orders_status_idx | 0 | 1376 kB
idx_scan = 0are the indexes no plan has used since the statistics started counting.NOT i.indisuniqueleaves out the primary key and unique constraints.- Sorting by size puts the ones that take the most disk on top.
Be careful before dropping. Statistics survive a clean shutdown, but according to the documentation they are reset after a crash, an immediate shutdown, a base backup start or point-in-time recovery. Look at a representative period, month-end reports included, and drop with the CREATE INDEX statement at hand to recreate it.
Common mistakes
- Looking only at
cost. It is an estimate. Readactual time,Rows Removed by FilterandBuffers. - Measuring on a toy table. With few rows a Seq Scan wins and the index goes unnoticed. Test with a volume close to the real one.
- Indexing every column just in case. Each index costs on writes and takes disk. Create one when a query asks for it and check it with the plan.
- Putting the columns in the wrong order. Equalities first, then the
ORDER BY. Our variant D read 17 times the pages. - Forgetting
ANALYZE. The planner decides from statistics; after loading or changing a lot of data, refresh them. - Creating the index without
CONCURRENTLYin production. It blocks writes to the table while it builds. - Running
EXPLAIN ANALYZEon a write withoutROLLBACK. The changes really happen.
Frequently asked questions
Is EXPLAIN ANALYZE safe in production?
It runs the query, so it costs the same as the query: with a slow one, expect that slowness. If it is an INSERT, UPDATE or DELETE, it makes the changes; wrap it in BEGIN and ROLLBACK. To only see the plan without running anything, use a plain EXPLAIN.
Why does Postgres not use my index?
The most common causes: the query needs a large fraction of the table, the index columns do not match the WHERE and the ORDER BY, the statistics are stale, or the table is so small that a Seq Scan is cheaper. Check the plan before forcing anything.
How many columns should an index have?
The ones the query needs, and few: the documentation says to use multicolumn indexes sparingly and that beyond three columns they rarely help, except for very stylized table usage.
How many indexes are too many?
There is no number. In our test, three extra indexes took the 100,000-row insert to about 1.8 times the baseline. If your table takes a lot of writes, measure, and check pg_stat_user_indexes from time to time.
Checklist
- You read
actual time,Rows Removed by FilterandBuffers, not justcost. - Writes are tested inside
BEGINandROLLBACK. - The index has the
=columns of theWHEREfirst and theORDER BYcolumns after. - You run
ANALYZEand compare the plan before and after, with the same data volume. - In production you build the index with
CONCURRENTLYand look forINVALIDindexes if it fails. - You measure the index's cost on your writes if the table takes a lot of them.
- You check
pg_stat_user_indexesover a representative period before dropping anything.
If the table is a queue, the partial index in the Postgres job queue with SKIP LOCKED is a good example of indexing only what gets queried. With the steps above, read the plan, create an index, measure and decide with data.
Saw this on Instagram? Comment INDICES on the Reel and I'll send you the guide.
Written by

Published November 12, 2026 · updated November 18, 2026