Skip to content

Incident: a query slows down as data grows

Same 50 rows, ten times the reading: when the statistics are fresh, the plan is the cause, so change the index, not the statistics.

Predict first

Before you read: predict

PAGE · ECON-201 flag review · the proctors' list took 90 ms in June and takes 2.4 s now · no deploy since June

The list runs:

SELECT id, candidate_id, submitted_at FROM attempts WHERE exam_id = $1 ORDER BY submitted_at DESC LIMIT 50;

attempts has its primary key and one other index, on (exam_id). In June ECON-201 had 40,000 attempts. Each weekly session since has added 40,000, and nine sessions later it has 400,000. The table also holds 200,000 attempts of other exams, which have not changed. Autovacuum runs with PostgreSQL's default settings, and parallel query is off on this server. The on-call engineer runs EXPLAIN (ANALYZE, BUFFERS) on the list for ECON-201.

What does the plan show?

Pick one

The 30-second version

A list that slows as data grows, with no deploy, does more work per call, not more calls. Rule out stale statistics: autovacuum re-analyzes growing tables, so estimates stay close. Then read the scan: it keeps every match to return 50. An index on filter then sort, with keyset pages, reads only the page.

Same 50 rows, ten times the reading: when the statistics are fresh, the plan is the cause, so change the index, not the statistics.

How it works

Step 1 of 12

The symptom. The page returns the same 50 rows as before, nothing was deployed, yet each call takes longer: each call does more work.
In the kitchen
  • one trader's drawer grows tenfold over a season of weekly auctions → the key's rows grow; the answer is still 50 (steps 1, 10)

Analogy: A pawnshop's pledge drawers

Pledge tickets go into drawers by customer, in no order inside a drawer. A regular asks for his 50 latest pledges, newest first.

At the pawnshop In the incident
a drawer per customer, tickets in no order inside it the index on the filter column only
pulling the whole drawer and sorting it on the counter to hand over 50 every match kept, then a Sort (step 7)
one trader's drawer grows tenfold over a season of weekly auctions the key's rows grow; the answer is still 50 (steps 1, 10)
the clerk walks the back room shelf by shelf once that trader fills most of it the plan flips to reading the table (step 8)
the owner recounts every drawer after each big intake; recounting again changes nothing autovacuum's analyze; ANALYZE by hand is not the fix (steps 5, 9)
a ledger tabbed by customer and dated inside each tab: read the last 50 lines and stop the index on filter then sort (step 11)
"carry on after the ticket of 12 March, number 4417", not "skip the first 20,000" keyset, not OFFSET (step 11)

Breaks down when: a clerk notices a drawer getting fat. Postgres never warns; someone has to compare the pages a call reads over time (step 12).

Contrasting case: a list whose index already matches filter then sort. It reads 50 rows at any size; it slows with growth only on deep OFFSET pages (api-pagination-cursor-offset step 2).

In real terms: before you run ANALYZE on a slow list, check the estimate and the rows the scan keeps; fresh numbers and every match kept mean the index is the fix.

Examples, run for real

4.1 ExamHall EXH-2940: the flag-review list over the term

The stem's table on PostgreSQL 17, with autovacuum on: June's 40,000 ECON-201 attempts, then one load per weekly session. After each load, the plan; then pg_stat_statements for a June window and a window now, and an ANALYZE by hand at the end:

4.1 PostgreSQL 17.11 · attempts: other exams 200000, an index on (exam_id) · autovacuum as shipped, waking every second · ECON-201: June 40000, then 40000 a weekly session
4.1 June   · ECON-201 40000 · autovacuum analyzed: yes · n_mod_since_analyze 0 · Bitmap Heap Scan · kept 40000 · removed by filter 0 · estimate within a tenth of kept · Sort yes · returned 50
4.1 week 1 · ECON-201 80000 · autovacuum analyzed: yes · n_mod_since_analyze 0 · Bitmap Heap Scan · kept 80000 · removed by filter 0 · estimate within a tenth of kept · Sort yes · returned 50
4.1 week 2 · ECON-201 120000 · autovacuum analyzed: yes · n_mod_since_analyze 0 · Bitmap Heap Scan · kept 120000 · removed by filter 0 · estimate within a tenth of kept · Sort yes · returned 50
4.1 week 3 · ECON-201 160000 · autovacuum analyzed: yes · n_mod_since_analyze 0 · Bitmap Heap Scan · kept 160000 · removed by filter 0 · estimate within a tenth of kept · Sort yes · returned 50
4.1 week 4 · ECON-201 200000 · autovacuum analyzed: yes · n_mod_since_analyze 0 · Bitmap Heap Scan · kept 200000 · removed by filter 0 · estimate within a tenth of kept · Sort yes · returned 50
4.1 week 5 · ECON-201 240000 · autovacuum analyzed: no  · n_mod_since_analyze 40000 · Bitmap Heap Scan · kept 240000 · removed by filter 0 · estimate within a tenth of kept · Sort yes · returned 50
4.1 week 6 · ECON-201 280000 · autovacuum analyzed: yes · n_mod_since_analyze 0 · Seq Scan · kept 280000 · removed by filter 200000 · estimate within a tenth of kept · Sort yes · returned 50
4.1 week 7 · ECON-201 320000 · autovacuum analyzed: no  · n_mod_since_analyze 40000 · Seq Scan · kept 320000 · removed by filter 200000 · estimate within a tenth of kept · Sort yes · returned 50
4.1 week 8 · ECON-201 360000 · autovacuum analyzed: yes · n_mod_since_analyze 0 · Seq Scan · kept 360000 · removed by filter 200000 · estimate within a tenth of kept · Sort yes · returned 50
4.1 week 9 · ECON-201 400000 · autovacuum analyzed: no  · n_mod_since_analyze 40000 · Seq Scan · kept 400000 · removed by filter 200000 · estimate within a tenth of kept · Sort yes · returned 50
4.1 pg_stat_statements, June window: calls 30 · rows per call 50 · pages per call 367
4.1 pg_stat_statements, now  window: calls 45 · rows per call 50 · pages per call 4804
4.1 ANALYZE attempts by hand, then the plan: Seq Scan · kept 400000 · removed by filter 200000 · estimate within a tenth of kept · Sort yes · returned 50 · same scan, same rows: true

Autovacuum re-analyzed after most sessions, so every estimate stayed close (steps 5–6). Each call returns 50 rows and keeps every ECON-201 attempt; from week 6 the plan reads the whole table (steps 7–8). Per call, the pages grew and rows did not (steps 2–3); ANALYZE changed nothing (step 9).

4.2 Chat MR !3240: the index that reads only the page

A chat service's #general history had the same shape: WHERE room_id = $1 ORDER BY sent_at DESC LIMIT 50 on an index on (room_id). MR !3240 builds (room_id, sent_at, id) with CREATE INDEX CONCURRENTLY while messages keep arriving:

4.2 PostgreSQL 17.11 · messages 400000, #general 40000, an index on (room_id) · before: Bitmap Heap Scan · kept 40000 · removed by filter 0 · estimate within a tenth of kept · Sort yes · returned 50
4.2 MR !3240 · CREATE INDEX CONCURRENTLY messages_room_sent_idx ON messages (room_id, sent_at, id), on its own connection · an insert committed while it built: true · index valid: true
4.2 after, page 1: Index Scan Backward using messages_room_sent_idx · kept 50 · removed by filter 0 · stops at the LIMIT · Sort no · returned 50
4.2 after, OFFSET 20000: rows the scan reads at least 20050 (the 20000 skipped and the 50 shown): true · returned 50
4.2 after, keyset after the 20,000th row: Index Scan Backward using messages_room_sent_idx · kept 50 · removed by filter 0 · stops at the LIMIT · Sort no · returned 50 · same 50 rows as the OFFSET page: true

Page 1 now reads 50 rows (step 11). A deep OFFSET page still reads every row it skips, and the keyset page after the same row reads 50 (api-pagination-cursor-offset steps 2 and 6).

Try it

Answer first, then check. Nothing is saved.

Question 1 of 7

A chat service's room history runs:

SELECT id, author_id, body, sent_at FROM messages WHERE room_id = $1 ORDER BY sent_at DESC LIMIT 50;

messages has its primary key and an index on (room_id), and its statistics are current. Today the #launch room holds 6,000 of the table's 600,000 messages. Each week #launch gains 1,000 messages and the other rooms 19,000.

Counting today as week 0, in which week does one page of #launch first read more than 30,000 rows?

Pick one

Question 2 of 7

A streaming service's plays table holds 290,000 plays after its launch week. Each week since has added 60,000 plays as one load; nothing is updated or deleted. Autovacuum runs with PostgreSQL's default settings, and analyzed the table after the launch week.

Counting the launch week as week 0, after which week's load does autovacuum first leave the table's statistics as they were before that load?

Pick one

Question 3 of 7

A box office's latest-sales page for one event runs:

SELECT id, seat, sold_at FROM sales WHERE event_id = $1 ORDER BY sold_at DESC LIMIT 50;

sales has its primary key and an index on (event_id). Between March and now a stadium tour's sales grew from 20,000 to 200,000; the other events' 300,000 did not change, and nothing was deployed. The tour's page was viewed 40 times in a March window and 60 times in a window now. The on-call engineer reset pg_stat_statements at the start of each window and saved the statement's row at its end.

Which value, compared between the two rows, grows by the same factor as the pages one view's query reads?

Pick one

Question 4 of 7

A payments ledger's statement page runs:

SELECT id, amount_cents, created_at FROM ledger_entries WHERE merchant_id = $1 ORDER BY created_at DESC LIMIT 50;

ledger_entries has its primary key and an index on (merchant_id). It was created with autovacuum switched off and was last analyzed when merchant 77 had 30,000 entries. Merchant 77 now has 300,000 of the table's 400,000. Its plan today (parallel query is off on this server):

Limit (actual rows=50 loops=1)
  ->  Sort (actual rows=50 loops=1)
        ->  Bitmap Heap Scan on ledger_entries (actual rows=300000 loops=1)
              Recheck Cond: (merchant_id = 77)
              ->  Bitmap Index Scan on ledger_entries_merchant_id_idx (actual rows=300000 loops=1)
                    Index Cond: (merchant_id = 77)

The on-call engineer runs ANALYZE ledger_entries, then the same EXPLAIN (ANALYZE, BUFFERS).

What does the plan show now?

Pick one

Question 5 of 7

In the interview you are given EXH-2940's page and asked: "A page that took 90 ms in June takes 2.4 s now, with no deploy.

How do you investigate?" What do you answer, in at most 150 words?

Question 6 of 7

A streaming service's "recent plays" list, WHERE title_id = $1 ORDER BY played_at DESC LIMIT 50 on an index on (title_id), is one statement for every title. Since March the database has been busier at peak, with no deploy, and the team suspects this list. In March the on-call engineer reset pg_stat_statements at the start of a window and saved the list's row at its end. Nobody has reset it since. Now the engineer saved the row at the start of a window of the same length and again at its end (parallel query is off on this server):

saved               | calls | rows  | shared_blks_hit | shared_blks_read
end of March window |    40 |  2000 | 6643            | 0
start of now window |   440 | 22000 | 85843           | 0
end of now window   |   520 | 26000 | 95243           | 0

What do the rows say about the list?

Pick one

Question 7 of 7

A chat service's #announcements history runs:

SELECT id, author_id, body, sent_at FROM messages WHERE room_id = $1 ORDER BY sent_at DESC LIMIT 50;

messages has its primary key and an index on (room_id); autovacuum runs with PostgreSQL's default settings and parallel query is off. A week ago the table held 1,000,000 messages, 1,000 of them in #announcements. Since then a migration bot has posted 7,800 messages a day to #announcements for five days, one load a day, and nothing else has changed. The history is slow. The on-call engineer reads the scan node of EXPLAIN (ANALYZE, BUFFERS) and the table's row in pg_stat_user_tables:

scan node           | Bitmap Heap Scan on messages
rows actual         | 40,000
above the scan      | a Sort that keeps 50
autoanalyze_count   | 1
n_mod_since_analyze | 39,000

Which hypothesis does this readout support?

Pick one

Go deeper

Pro tips
  1. Read "rows" on the scan node first. 400,000 rows kept to return 50 is the diagnosis in one number (curriculum row's own tip). When: the first EXPLAIN (ANALYZE, BUFFERS) of a list that slowed with growth. Status: EXECUTED.
  2. Check the statistics before blaming them. SELECT relname, last_autoanalyze, n_mod_since_analyze FROM pg_stat_user_tables WHERE relname = 'attempts'; answers "stale or not" before anyone runs ANALYZE. When: step 6, before changing anything. Status: EXECUTED.
Source

PostgreSQL behaviour can change between versions: the facts below are as of 2026-10-06, run on PostgreSQL 17.11. No item is keyed on a source; every key is executed. Valid as of 2026-10-06.

  • PostgreSQL documentation, 11.4 Indexes and ORDER BY: https://www.postgresql.org/docs/current/indexes-ordering.html (pg-current-indexes-ordering.txt, the row's first source; the version 17 page, pg17-indexes-ordering.txt, says the same). Quoted: "an explicit sort will have to process all the data to identify the first n rows, but if there is an index matching the ORDER BY, the first n rows can be retrieved directly". Supports steps 7 and 11.
  • PostgreSQL documentation, 7.6 LIMIT and OFFSET: https://www.postgresql.org/docs/current/queries-limit.html (pg-current-queries-limit.txt, the row's second source). Quoted: "The rows skipped by an OFFSET clause still have to be computed inside the server; therefore a large OFFSET might be inefficient."
  • Markus Winand, Use The Index, Luke: "We need tool support for keyset pagination" (no-offset): https://use-the-index-luke.com/no-offset (luke-no-offset.txt), the row's third source. Quoted: "big offsets impose a lot of work on the database—no matter whether SQL or NoSQL." Supports step 11.
  • PostgreSQL 17 documentation, 25.1.6 The Autovacuum Daemon: https://www.postgresql.org/docs/17/routine-vacuuming.html (pg17-routine-vacuuming.txt). Quoted: "analyze threshold = analyze base threshold + analyze scale factor * number of tuples"; "is compared to the total number of tuples inserted, updated, or deleted since the last ANALYZE." And 19.10 Automatic Vacuuming: https://www.postgresql.org/docs/17/runtime-config-autovacuum.html (pg17-runtime-config-autovacuum.txt): autovacuum_analyze_threshold "The default is 50 tuples"; autovacuum_analyze_scale_factor "The default is 0.1 (10% of table size)"; autovacuum_naptime "The default is one minute". Supports steps 5–6.
  • PostgreSQL 17 documentation, 27.2 The Cumulative Statistics System (pg_stat_user_tables): https://www.postgresql.org/docs/17/monitoring-stats.html (pg17-monitoring-stats.txt). Quoted: n_mod_since_analyze "Estimated number of rows modified since this table was last analyzed"; last_autoanalyze "Last time at which this table was analyzed by the autovacuum daemon". Supports step 6.
  • PostgreSQL 17 documentation, F.32 pg_stat_statements: https://www.postgresql.org/docs/17/pgstatstatements.html (pg17-pgstatstatements.txt). Quoted: calls "Number of times the statement was executed"; rows "Total number of rows retrieved or affected by the statement"; shared_blks_hit "Total number of shared block cache hits by the statement". Supports steps 2–3.
  • PostgreSQL 17 documentation, 14.1 Using EXPLAIN and 14.2 Statistics Used by the Planner: https://www.postgresql.org/docs/17/using-explain.html and https://www.postgresql.org/docs/17/planner-stats.html (pg17-using-explain.txt, pg17-planner-stats.txt). Support steps 6–8.

Related lessons

Checked against the tools and docs as of 2026-10-06.