Skip to content

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)
  1. Reading EXPLAIN ANALYZE step by step
  2. Seq Scan vs. Index Scan
  3. Choosing the index columns: WHERE and ORDER BY
  4. Measuring before and after
  5. What indexes cost on writes
  6. Finding unused indexes
  7. Common mistakes
  8. Frequently asked questions
  9. Checklist

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
  1. The plan reads from the inside out: Seq Scan produces rows, Sort orders them and Limit keeps 20.
  2. cost=0.00..4167.00 is an estimate in arbitrary units, from startup to total; a node's cost includes its children's.
  3. actual time=0.129..15.287 rows=40.00 loops=1 is what was measured: milliseconds to the first and the last row, rows, and how many times the node ran. With loops above 1, multiply the time by it.
  4. Filter and Rows Removed by Filter: 199960: it read 200,000 rows to keep 40. That number gives away a missing index.
  5. Buffers: shared hit=1667 are the pages read, all already in Postgres's cache (hit). In PostgreSQL 18, ANALYZE turns BUFFERS on by itself; writing it does no harm.
  6. Planning Time is how long it took to choose the plan and Execution Time how long it took to run it.
the same plan, line by line, without the index and with itbefore · no indexLimitSort (Sort Key: created_at DESC)sorts 40 rows in memorySeq Scan on ordersreads the whole tableactual time=0.129..15.287 rows=40 loops=1real ms · rows · runsFilter: (customer_id = 4242)the WHERE is applied hereRows Removed by Filter: 199960rows read for nothingBuffers: shared hit=1667pages readExecution Time: 15.432 mswhat it really tookafter · index (customer_id, created_at DESC)LimitIndex Scan using orders_customer_created_idxno Sort: already in orderIndex Cond: (customer_id = 4242) · Buffers: shared hit=24Execution Time: 0.123 ms24 pages, not 1667read actual time and Buffers, not only costmeasured on PGlite 0.5.10 (PostgreSQL 18.6), 200,000 invented rows

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.

a book without and with an index: how many pages you openno index · Seq Scanflips through the whole book: 1667 pages read · 15.4 mswith index · Index Scancustomer_id = 4242 → go straight to these pagesopens the index and jumps to the row: 24 pages read · 0.12 msthe index saves pages, not magicschematic, not to scale · figures measured on PGlite with 200,000 rows

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
  1. customer_id goes first because the query compares it with =: Postgres jumps to that customer's entries.
  2. created_at DESC goes next: within each customer the entries are already in the order the query asks for, so the Sort disappears and Limit stops at 20.
  3. ANALYZE orders refreshes the statistics the planner uses.
  4. The plan is now an Index Scan with an Index Cond, no Filter and no Sort, 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.

VariantRead nodeBuffersExecution Time
A · no indexSeq Scan + Sort166715.432 ms
B · (customer_id)Bitmap Heap Scan + Sort420.468 ms
D · (created_at, customer_id)Index Scan Backward4003.596 ms
C · (customer_id, created_at DESC)Index Scan, no Sort240.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.

the read nodes you will see in a plannodewhat it readsin our testSeq Scanthe whole table, row by rowA · no index · 15.4 msBitmap Heap Scanthe rows the index points toB · (customer_id) · 0.47 msIndex Scanrows following the indexC · 2 columns · 0.12 msIndex Scan Backwardthe index, last entry firstD · reversed · 3.6 msC = (customer_id, created_at DESC) · D = (created_at, customer_id)the planner picks the node; you give it the optionssame query, 200,000 rows, PGlite 0.5.10 (PostgreSQL 18.6)

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
  1. idx_scan = 0 are the indexes no plan has used since the statistics started counting.
  2. NOT i.indisunique leaves out the primary key and unique constraints.
  3. 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. Read actual time, Rows Removed by Filter and Buffers.
  • 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 CONCURRENTLY in production. It blocks writes to the table while it builds.
  • Running EXPLAIN ANALYZE on a write without ROLLBACK. 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 Filter and Buffers, not just cost.
  • Writes are tested inside BEGIN and ROLLBACK.
  • The index has the = columns of the WHERE first and the ORDER BY columns after.
  • You run ANALYZE and compare the plan before and after, with the same data volume.
  • In production you build the index with CONCURRENTLY and look for INVALID indexes 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_indexes over 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

Luis Morales

Luis Morales

Frontend and Shopify developer, EcomLabs

@luismoralesdev on Instagram

Published November 12, 2026 · updated November 18, 2026

← Blog