The line that fixed the report turned a 2ms lookup into a scan of 42 million rows.
Context, code, logs and metrics, the same material you would have in production, with nobody pointing at the problem.
No multiple choice and no single right answer: you write what you think is happening, what you would do and what it costs.
The moment you submit, the solution written by an engineer opens next to your answers. Nobody scores you: the comparison is yours to make, and it stays open to reread.
The audit-api holds the platform's audit log: who changed what, and when. The compliance team opens a panel that asks for one day's events.
GET /api/v1/audit-events?on=2026-08-20select id, actor_id, action, occurred_at
from audit_events
where occurred_at >= $1 and occurred_at <= $2
order by occurred_at desc
limit 500The two parameters are built in the application: '2026-08-20 00:00:00' and '2026-08-20 23:59:59'. The table has 42 million rows and takes about 180 thousand a day. occurred_at is a timestamptz and the database runs in UTC.
A fix shipped on Tuesday. An event was in the table and did not show up in the report: it had happened at 23:59:59.412, and the <= on 23:59:59 sharp left it out. The report is correct again.
> The SQL is PostgreSQL because the query plan is this challenge's evidence. > The same predicate throws the index away in MySQL, SQL Server and Oracle.
Since Tuesday the compliance panel times out. The endpoint's p95 went from 48ms to 8.4s, and database CPU went from 31% to 88% — the other endpoints on the same database got slower along with it, without being touched.
No migration shipped on Tuesday. The table grew 0.4% over the week, and the team's first suspicion was volume: it is big and it keeps growing.
The change:
- where occurred_at >= $1 and occurred_at <= $2
+ where date(occurred_at) = $1The endpoint now sends a single parameter, '2026-08-20'.
The table:
=> \d audit_events
Column | Type | Nullable
-------------+--------------+----------
id | bigint | not null
actor_id | uuid | not null
action | text | not null
occurred_at | timestamptz | not null
Indexes:
"audit_events_pkey" PRIMARY KEY, btree (id)
"audit_events_occurred_at_idx" btree (occurred_at)seq_scan in pg_stat_user_tables for this table was 6 on Friday and is at 14,902 today. idx_scan stopped growing on Tuesday.
The two plans, same report day, before and after:
audit_events_occurred_at_idx index is still on the table and the column in the where is still occurred_at. Why does the plan scan 42 million rows?create index on audit_events (date(occurred_at)) and calling it a day. What do you tell them?What opens
-- before
Limit (cost=0.57..784.10 rows=500 width=52)
(actual time=0.038..1.902 rows=500 loops=1)
-> Index Scan Backward using audit_events_occurred_at_idx on audit_events
(cost=0.57..281043.22 rows=179254 width=52)
(actual time=0.036..1.771 rows=500 loops=1)
Index Cond: ((occurred_at >= '2026-08-20 00:00:00+00'::timestamptz)
AND (occurred_at <= '2026-08-20 23:59:59+00'::timestamptz))
Buffers: shared hit=506 read=4
Execution Time: 1.974 ms-- after
Limit (cost=1284402.31..1284460.63 rows=500 width=52)
(actual time=8398.442..8401.117 rows=500 loops=1)
-> Sort (cost=1284402.31..1284927.16 rows=210000 width=52)
(actual time=8398.440..8400.988 rows=500 loops=1)
Sort Key: occurred_at DESC
Sort Method: top-N heapsort Memory: 96kB
-> Gather (cost=1000.00..1273899.42 rows=210000 width=52)
(actual time=2.914..8377.601 rows=179254 loops=1)
Workers Launched: 2
-> Parallel Seq Scan on audit_events
(cost=0.00..1251905.42 rows=87500 width=52)
(actual time=1.883..8355.204 rows=59751 loops=3)
Filter: (date(occurred_at) = '2026-08-20'::date)
Rows Removed by Filter: 13940249
Buffers: shared hit=2914 read=712088
Execution Time: 8404.913 msReading is free; answering is what uses a credit. Every new account starts with 3, no card.