The index that did nothing: reading an EXPLAIN plan line by line

The ticket said “Open orders page slow for big accounts.” The query behind it was boring, which is usually a sign the problem is not the query.

SELECT id, total_cents, created_at
FROM orders
WHERE account_id = 4812
  AND status = 'open'
ORDER BY created_at DESC
LIMIT 20;

-- the first fix someone shipped
CREATE INDEX CONCURRENTLY orders_status_idx ON orders (status);

The orders table has about 48 million rows. Account 4812 is a wholesale customer with 1.9 million of them, and only a few thousand open. The new index on status went out on a Tuesday. On Wednesday the page was exactly as slow. Nobody had run EXPLAIN before or after.

Read the plan from the inside out

Here is what EXPLAIN (ANALYZE, BUFFERS) showed after the index existed:

Limit  (actual time=1184.302..1184.309 rows=20 loops=1)
  Buffers: shared hit=3121 read=41877
  ->  Sort  (actual time=1184.300..1184.304 rows=20 loops=1)
        Sort Key: created_at DESC
        Sort Method: top-N heapsort  Memory: 27kB
        ->  Bitmap Heap Scan on orders  (actual time=212.4..1171.9 rows=3610 loops=1)
              Recheck Cond: (account_id = 4812)
              Filter: (status = 'open'::text)
              Rows Removed by Filter: 1907002
              Heap Blocks: exact=41210
              ->  Bitmap Index Scan on orders_account_id_idx  (actual time=180.2..180.2 rows=1910612 loops=1)
                    Index Cond: (account_id = 4812)
Planning Time: 0.212 ms
Execution Time: 1184.351 ms

A plan is a tree, and the work starts at the most indented line. Reading bottom to top:

1. Bitmap Index Scan on the existing account_id index finds 1.9 million matching row locations. That alone takes 180 ms.
2. Bitmap Heap Scan visits 41,210 table pages to fetch those rows, then throws away all but 3,610 because their status is not open. Rows Removed by Filter: 1907002 is the most important line in the whole plan.
3. Sort keeps the top 20 by date with a heapsort. Cheap, 27 kB of memory.
4. Limit returns 20 rows. We read nearly two million to produce them.

The read=41877 in the buffers line tells the rest of the story: those pages came from disk, not cache.

Why the planner ignored our index

Our new index was never mentioned. The planner did consider it, but status = 'open' matches about 2 million rows across all accounts, so on its own it is barely more selective than account_id. Combining the two with a BitmapAnd would still mean building two bitmaps with millions of entries. The planner estimated, correctly, that it would not be faster.

An index helps when it lets Postgres skip work. This one did not match the shape of the question, which is “for one account, the newest open orders, stop after 20.”

The index that matched the query

We need the rows for one account, already ordered by date, containing only open orders. That is a composite index with a predicate:

DROP INDEX CONCURRENTLY orders_status_idx;

CREATE INDEX CONCURRENTLY orders_account_open_created_idx
  ON orders (account_id, created_at DESC)
  WHERE status = 'open';

ANALYZE orders;

The new plan is two lines: an Index Scan on orders_account_open_created_idx feeding a Limit. It reads 20 index entries in order and stops. Execution time dropped from 1,184 ms to 0.12 ms, with 24 buffer hits and zero reads.

Because it is a partial index, it only contains open orders, around 4% of the table. It is 61 MB instead of the 1.4 GB a full composite index would take, and updates to closed orders never touch it.

What we changed in our process

The query was fine and Postgres was fine. The mistake was adding an index based on the WHERE clause alone, without looking at the ORDER BY and LIMIT, and without measuring. Our migration template now has a required field for the before and after plan, pasted from the production replica. Reviewers reject index migrations without it, and so far nobody has complained about the extra two minutes.