This query got slow after the table grew. Read the plan and say what you would change.

You’re handed the query behind an account’s recent-orders page and its plan, from EXPLAIN (ANALYZE, BUFFERS) with parallel workers off: What to read in it, four lines that carry the evidence: Estimate against actual. The index scan expected 355433 rows and found 360009. Close enough, so statistics are not the problem. The waste. Rows Removed by Filter: 359993, against 16 rows kept. The selective predicate, account_id, is checked row by row. Where the pages came from. Every buffer the execution touched is read, none is hit, so none of those pages were in PostgreSQL’s cache when this ran. Run it twice before you quote a latency to the customer. The sort. Memory: 26kB. The sort only sees the survivors, so it is not the problem. Show that you can read a plan, not recite indexing advice. Work from evidence to a change you can justify, and say each step as you go. Know what you’re reading. EXPLAIN (ANALYZE, BUFFERS) shows actual rows, time and pages read; plain EXPLAIN shows only estimates. If you run it yourself, ANALYZE executes the statement, so for an INSERT, UPDATE, DELETE or MERGE you wrap it in a transaction and roll back. Read from the innermost node out. Find where actual time and rows jump. Cost is in the planner’s own units, not milliseconds. Compare estimated rows with actual rows. A large gap points at statistics, and the fix is ANALYZE or better statistics, not an index. Find the wasted work. A large Rows Removed by Filter next to a small output means a selective predicate is being checked row by row instead of used to find rows. Propose one index and justify its shape. Equality columns first, then the range or sort column, in the order the query sorts, so a LIMIT can stop early when more rows match than it asks for. Say what it costs: writes maintain it, and it takes disk. Ship it safely and prove it. CREATE INDEX CONCURRENTLY, then the same EXPLAIN again, and say in advance which node you expect to see. The trap is answering “add an index” before you have pointed at the line of the plan that justifies it. Explain why the query got slower as the table grew: the plan reads every order in the date window, across all accounts, and that count grew with the table.

Read a query plan and fix a slow query

A practice prompt we wrote. No company or candidate report names it, so it carries no company tag.

How to answer

You’re handed the query behind an account’s recent-orders page and its plan, from EXPLAIN (ANALYZE, BUFFERS) with parallel workers off:

SELECT id, status, total_cents, created_at
FROM orders
WHERE account_id = 4812
  AND created_at >= now() - interval '90 days'
ORDER BY created_at DESC
LIMIT 50;

Limit  (cost=47201.64..47201.68 rows=18 width=31) (actual time=104.395..104.396 rows=16 loops=1)
  Buffers: shared read=35401
  ->  Sort  (cost=47201.64..47201.68 rows=18 width=31) (actual time=104.394..104.395 rows=16 loops=1)
        Sort Key: created_at DESC
        Sort Method: quicksort  Memory: 26kB
        Buffers: shared read=35401
        ->  Bitmap Heap Scan on orders  (cost=6566.18..47201.26 rows=18 width=31) (actual time=19.266..104.380 rows=16 loops=1)
              Recheck Cond: (created_at >= (now() - '90 days'::interval))
              Filter: (account_id = 4812)
              Rows Removed by Filter: 359993
              Heap Blocks: exact=34420
              Buffers: shared read=35401
              ->  Bitmap Index Scan on orders_created_at_idx  (cost=0.00..6566.18 rows=355433 width=0) (actual time=12.264..12.264 rows=360009 loops=1)
                    Index Cond: (created_at >= (now() - '90 days'::interval))
                    Buffers: shared read=981
Planning:
  Buffers: shared hit=16
Planning Time: 0.187 ms
Execution Time: 104.414 ms

What to read in it, four lines that carry the evidence:

Estimate against actual. The index scan expected 355433 rows and found 360009. Close enough, so statistics are not the problem.
The waste. Rows Removed by Filter: 359993, against 16 rows kept. The selective predicate, account_id, is checked row by row.
Where the pages came from. Every buffer the execution touched is read, none is hit, so none of those pages were in PostgreSQL’s cache when this ran. Run it twice before you quote a latency to the customer.
The sort. Memory: 26kB. The sort only sees the survivors, so it is not the problem.

Show that you can read a plan, not recite indexing advice. Work from evidence to a change you can justify, and say each step as you go.

Know what you’re reading. EXPLAIN (ANALYZE, BUFFERS) shows actual rows, time and pages read; plain EXPLAIN shows only estimates. If you run it yourself, ANALYZE executes the statement, so for an INSERT, UPDATE, DELETE or MERGE you wrap it in a transaction and roll back.
Read from the innermost node out. Find where actual time and rows jump. Cost is in the planner’s own units, not milliseconds.
Compare estimated rows with actual rows. A large gap points at statistics, and the fix is ANALYZE or better statistics, not an index.
Find the wasted work. A large Rows Removed by Filter next to a small output means a selective predicate is being checked row by row instead of used to find rows.
Propose one index and justify its shape. Equality columns first, then the range or sort column, in the order the query sorts, so a LIMIT can stop early when more rows match than it asks for. Say what it costs: writes maintain it, and it takes disk.
Ship it safely and prove it. CREATE INDEX CONCURRENTLY, then the same EXPLAIN again, and say in advance which node you expect to see.

The trap is answering “add an index” before you have pointed at the line of the plan that justifies it. Explain why the query got slower as the table grew: the plan reads every order in the date window, across all accounts, and that count grew with the table.

Follow-ups

What the interviewer may ask next, once your first answer is on the table.

The table takes thousands of inserts a second. What does your new index cost on the write path, and how would you measure it?
You add the index and the planner still does not use it. What do you check?
The same query is fast for most accounts and slow for a few very large ones. What is going on?

Where answers go wrong

Reads the cost numbers as milliseconds and chases the most expensive-looking node instead of the one where actual time and rows jump.
Proposes a separate index on each column in the WHERE clause. The accountid index alone reads the account’s whole history and sorts it, the createdat index alone reads every account’s orders in the window, and a BitmapAnd of both still scans the whole window in the created_at index and sorts every match; one composite index reads only this account’s recent orders, already in order.

Answer this in two minutes

Write the answer you would say out loud. The clock starts with your first word.

Compare with the model answer

Model answer

The first minute and a half. “The plan finds every order in the last 90 days through the created_at index, across every account, then checks account_id row by row and throws away all but 16 of 360009. That’s why it slowed as the table grew: the work tracks total volume, not this account. I’d add one index on (account_id, created_at), built CONCURRENTLY, so both predicates become index conditions. I’d rerun the same EXPLAIN and expect about 16 heap rows instead of 360009, and I’d watch write latency, because every insert now maintains the new index as well.”

If they want the detail:

“I read from the bottom. The only index it uses is on created_at, so it finds every order in the last 90 days: 360009 rows against an estimate of 355433, so statistics are fine. Then the heap scan visits 34420 table pages, 35401 with the index pages, to check account_id on each of those rows, and throws away all but 16. That line is the problem: the selective predicate is a Filter, not an Index Cond. The sort is cheap because it only sees what survived.”

“That’s also why it got slower as the table grew. The work is proportional to all orders in the window, across every account, so it rises with total volume even if this account’s own orders didn’t change at all.”

“I’d add one composite index, equality column first, then the column we range over and sort by:”

CREATE INDEX CONCURRENTLY orders_account_created_idx
  ON orders (account_id, created_at);

“I expect both predicates in an Index Cond on the new index, and 16 heap rows read instead of 360009. With account_id fixed, the index entries are already in created_at order, so PostgreSQL walks them backward and skips the sort; the PostgreSQL documentation on indexes and ORDER BY covers it. For this account the scan simply runs out of matches at 16:”

Limit (actual rows=16 loops=1)
  ->  Index Scan Backward using orders_account_created_idx on orders (actual rows=16 loops=1)
        Index Cond: ((account_id = 4812) AND (created_at >= (now() - '90 days'::interval)))

“For a large account with hundreds of orders in the window, the same scan stops once it has 50 rows. That’s the case the column order is for:”

Limit (actual rows=50 loops=1)
  ->  Index Scan Backward using orders_account_created_idx on orders (actual rows=50 loops=1)
        Index Cond: ((account_id = 777) AND (created_at >= (now() - '90 days'::interval)))

“Both are PostgreSQL 17 plans, shown with EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF). The node the planner picks depends on how many rows it expects; the line I’d point at is account_id inside the Index Cond, not a Filter.”

“Then the cost. Every insert, and every update that isn’t a heap-only (HOT) update, now adds an entry to the new index as well, and an update that changes either column can never be HOT, so I’d check the table’s write rate and watch insert latency after the change; the PostgreSQL page on HOT updates has the conditions. CONCURRENTLY avoids blocking writes during the build but can’t run inside a transaction, and a failed build leaves an invalid index to drop, per the CREATE INDEX reference.”

“Before adding it I’d check \d orders. If an index on account_id alone exists and the planner skipped it, that’s a finding in itself: the planner expected this account’s whole history to cost more than the date range, which points at skew or stale statistics, and it’s the same story as a few very large accounts being slow while most are fast. The composite index serves both cases and makes the single-column one redundant. I’d drop the old one only after pg_stat_user_indexes shows no scans on it over a full business cycle and it isn’t backing a constraint.”

“I wouldn’t add status or total_cents to make it index-only. The query reads 16 rows from the heap, which is nothing, and a wider index costs every write.”

“If the planner ignores the new index, I check in order: that it’s valid (indisvalid in pg_index, since a failed CONCURRENTLY build leaves it invalid); that statistics are fresh, with ANALYZE orders; that the predicate matches the column’s type, since a bigint column compared with a numeric parameter is cast to numeric and the index can’t serve it; and whether the application runs it as a prepared statement that switched to a generic plan.”

“And what I’d tell the account owner: the recent-orders page reads about 16 rows instead of 360009; the index builds tonight without blocking writes, and I’ll send the before-and-after plan tomorrow.”