A report that was instant is now painful

Can I prove how the database is finding the rows, not just how long it took?

The ticket

CUSTOMER TICKET: per-account usage report has become painful

Account: internal, raised by the support team Impact: the report is still usable but noticeably worse Started: gradually, over the last few months

The per-account usage summary used to come back immediately. It has been getting steadily worse and now there is a real pause every time we run it. Nobody has changed the report. The only thing that has changed is that we have a lot more history than we did. I am mostly worried about where this ends up in six months rather than how it feels today.

Your job

1. Prove how the database is answering the question, not just how long it took.
2. The customer's real question is about the trend, so answer that one.
3. Make the report answerable without reading everything, and verify the numbers did not change.

Working notes

The support database is on 127.0.0.1:5434, database support_lab, user support. The report lives in labs/sql/_stack/query.sql. Edit it, then run tse check, which executes exactly that file. To explore interactively:

docker compose -f labs/sql/_stack/compose.yaml exec -e PGPASSWORD=demo-password \
  postgres psql -U support -d support_lab

Track

SQL and PostgreSQL

Time

about 35 minutes

Difficulty

Involved

Tier

Core

Do these first: A report is quietly missing customers

Start it

In a Codespace or a local clone:

tse start sql/03-report-that-was-instant-got-slow

That provisions the broken system and prints the ticket above. Investigate with ordinary tools, then run tse check.

Look at the evidence

Real output, captured by running these commands against the broken system and checked against it on every build. It shows you what the evidence looks like. It cannot fix anything, and it will not tell you what is wrong.

Type a command you would reach for, or help.

CommandRun

Enter runs it. Shift and Enter start a new line. The up and down arrows walk back through what you have typed.

Show which commands are recorded (this is close to a hint)

Investigation scratchpad

Saved in this browser as you type. Nothing is uploaded. 0 of 7 filled in.

1. Customer symptom

In their words, not yours. Include scope and urgency.

2. Prediction

Before running anything: target layer, expected output, two likely causes.

3. Evidence gathered

The command or query, and why it is safe to run here.

4. Confirmed / disproven / still unknown

Three separate lists. This is the step people skip.

5. What it proves

One proof sentence, one safe next step, one alternate hypothesis.

6. Customer update and escalation note

Plain language. Impact first. No blame, no speculation.

7. Recall

One gap, one command to repeat tomorrow, one confidence score.

Copy as markdownClear

Hints

Each hint gives away a little more. Try to spend a few minutes on your own evidence first, because the recall is what makes it stick.

Hint 1 of 3

The customer has told you the most useful thing in the ticket without meaning to: nothing changed except the amount of data. That rules out the query, the schema, and any recent deploy, and it points at how the work scales rather than what the work is.

Resist timing it. A stopwatch tells you what happened once, on this machine, with this cache state, at today's size. It cannot tell you what the customer actually asked, which is where this ends up in six months.

The database can tell you exactly how it intends to answer the question, before and separately from how long that took. Ask it that.

Hint 2 of 3

EXPLAIN shows the plan the database chose. EXPLAIN (ANALYZE, BUFFERS) runs the statement and shows what actually happened alongside it.

One warning worth carrying for the rest of your career: EXPLAIN ANALYZE executes the statement. On a SELECT that is harmless. On an UPDATE or DELETE it does the thing, so wrap those in a transaction you intend to roll back.

Read the plan for the report and find how it reaches the rows for one account. Then compare two numbers that the plan gives you: how many rows it examined, and how many it returned. When those are wildly different, the database is doing work it should not have to.

Then ask what would let it go straight to the rows for one customer instead of looking at all of them.

Hint 3 of 3

bash labs/sql/_stack/explain.sh "SELECT count(*), avg(duration_ms)::int FROM api_requests WHERE customer_id = 7 AND requested_at >= TIMESTAMPTZ '2026-07-01' AND requested_at < TIMESTAMPTZ '2026-07-08';"

The plan contains Seq Scan on api_requests. The database is reading all 300,000 rows to return 7,500. Nothing exists that lets it find one account's rows directly, so its only option is to look at every row and discard most.

That is why it degraded gradually: the cost of that scan is the size of the table, and the table grows every day.

Create something that supports the filter. Order matters: the equality predicate first, then the range.

CREATE INDEX IF NOT EXISTS idx_api_requests_customer_time
    ON api_requests (customer_id, requested_at);

Put that above the report in labs/sql/_stack/query.sql, run tse check, and confirm both that the plan changed and that the numbers did not.

Solution

Write your customer update before you read this. Comparing your wording against the model answer is worth more than reading it cold.

Reveal the solution

Solution: a report that was instant is now painful

What the evidence proved

Command	What it proved	What it did not prove
EXPLAIN (ANALYZE, BUFFERS) on the report	The plan contains Seq Scan on api_requests	Nothing about the query's correctness
Rows examined against rows returned	300,000 read to return 7,500	Nothing about why nothing better exists
\d api_requests	Nothing indexes customer_id
The same plan after indexing	Bitmap Index Scan, and the row counts are unchanged
Running the report itself	7,500 requests averaging 191ms, before and after	That the fix altered no results

The timing is the least useful number here and the customer already had it. The plan is what answers the question they actually asked.

Root cause

Nothing supports looking up rows by customer_id, so the only way PostgreSQL can answer "requests for one account in one week" is to read every row in the table and discard the ones that do not match. It read 300,000 rows to return 7,500.

The query was never wrong. It has always been a full scan, and a full scan on a small table is instant. What changed is the table: the cost of that scan is the size of api_requests, and api_requests grows every day. That is precisely why it degraded gradually rather than breaking on a particular date, and why nobody could point at a change that caused it.

Why timing was the wrong instrument

At today's size the scan finishes in about 14 milliseconds and the indexed lookup in about 4. A stopwatch would have told you this is fine.

The customer did not ask whether it is fine today. They asked where it ends up in six months, and the plan answers that directly: a sequential scan is work proportional to the table, and an index lookup is not. One of those curves bends and the other does not.

This is the general lesson. Timing tells you what happened once, on this machine, with this cache state. The plan tells you what will keep happening.

Scoped fix

CREATE INDEX IF NOT EXISTS idx_api_requests_customer_time
    ON api_requests (customer_id, requested_at);

Column order is deliberate. The equality predicate comes first, then the range, so the index can seek straight to one account and then walk the time window inside it. Reversed, the range would have to be scanned across every account.

Then confirm two things rather than one: that the plan changed, and that the numbers did not. An index that changes your results is not an index, it is a bug.

Cost worth stating out loud

An index is not free. It consumes disk, and every insert into api_requests now has to maintain it. On a write-heavy request log that is a real trade, and it is the customer's trade to make rather than yours to make silently. Here the table is read for reporting far more often than any single row is written, so it is clearly worth it, but say so rather than assume it.

Customer update

Your instinct that this is about growth rather than a change was exactly right, and it is the reason nothing showed up in the deploy history.

The report has always worked by reading the entire request history and keeping the rows for the account you asked about. That is instant when the history is small, and it gets steadily slower as the history grows, which matches what you have been feeling. To answer your real question: it would have kept getting worse in direct proportion to how much history you accumulate.

We have added an index so the database can go straight to one account's rows instead of reading all of them. The report now returns the same numbers, which I verified, and the work it does no longer grows with the size of the table.

One thing to be aware of: an index costs a little storage and a little overhead on every write. For a table you report from this often that is a clear win, but if you would like the same treatment on other reports, tell me which and I will look at each rather than indexing everything by reflex.

Engineering escalation, if you needed one

Impact: gradual degradation of the per-account usage report, not yet user-blocking, on a trajectory that does not improve. Evidence: plan shows Seq Scan on api_requests, 300,000 rows examined for 7,500 returned; no index covers customer_id; results identical before and after indexing. Confirmed: query correctness, result stability, absence of a schema or code change. Ruled out: a regression, a data quality problem, resource pressure. Suspected cause: the access pattern was never indexed, and the table has grown past the point where a full scan is acceptable. Request: confirm whether other reports filter api_requests by account, and whether a retention policy on request history is planned, since that changes whether indexing or pruning is the better answer long term.

Check your understanding

Three questions on what the evidence here proved, and what it pointedly did not. Wrong answers explain themselves, and so do right ones.

tse quiz

Check your understanding

Three questions on what the evidence proved and what it did not. Every answer explains itself, including the right one.

Question 1 of 3The plan for the report shows a sequential scan reading 300,000 rows to return 7,500. What does that establish?

• The query is written incorrectly and is returning the wrong rows.
• The work the database does grows with the size of the table rather than with the size of the answer.
• The host is short of memory or CPU.
• Someone changed the report recently.

Question 2 of 3The scan finishes in about 14 milliseconds today. Why is a stopwatch still the wrong instrument for this ticket?

• Timings are unreliable because other processes on the machine interfere.
• Millisecond durations are too small to be meaningful.
• The customer asked where this ends up as they keep growing, and a measurement only describes one run on one machine with one cache state.
• The database does not report execution time accurately.

Question 3 of 3A customer says a report that used to be instant now drags, and nothing was deployed. Which move earns the first look?

• Run the report several times to establish a reliable baseline.
• Check whether the host is saturated on CPU or memory.
• Add an index on every column the report filters on and see whether it helps.
• Ask the database how it intends to answer the query, remembering that on a statement which modifies data that means wrapping it in a transaction you roll back.

3 questions, none answered yet.

Why this one exists

Timing tells you what happened once. The plan tells you what will keep happening: a full scan grows with the table and a lookup does not, which is why a report can be fine for a year and then suddenly not be.

In an interview

"It got slow as we grew" is one of the most common support tickets there is, and the useful answer is never a stopwatch. Reading a plan and naming a sequential scan over a filtered lookup is a concrete, senior-sounding diagnosis that takes one command.

Commands introduced

• EXPLAIN (ANALYZE, BUFFERS)
• CREATE INDEX
• reading a query plan

Evidence layers

• query plan
• rows examined versus rows returned
• table growth over time