A day of faults: reading the whole log

The "faults today" panel hangs on the wall of the life-support post and takes five seconds to open — long enough for the officer on duty to look away. Faults are under one percent of the log, yet the machine reads all of it: there is no road to those rows, so every one has to be looked at. The query is wired into the panel and cannot change — so the road is what you have to build. Build an index that lets the machine descend straight to the faulty rows of the day instead of sifting the whole log.

Speed this query up with an index

SELECT reading_id, sensor_id, reading_ts
FROM ls_readings
WHERE status = 'fault'
  AND reading_ts >= TIMESTAMPTZ '2184-03-01 00:00:00+00'
  AND reading_ts <  TIMESTAMPTZ '2184-03-02 00:00:00+00'
ORDER BY reading_ts

Solution requirement

Just CREATE/ALTER/DROP — no trailing SELECT needed.

What counts as solved

You may only change the schema: indexes and ANALYZE. The query stays as the task wrote it — no need to send it back.

the plan has no Seq Scan on ls_readings
the query reads at most 44 8 KB pages

Grading runs on a larger dataset than the samples show — the answer has to hold at scale.

PostgreSQLv16

Your query result will appear here

Focus radio

Paused · SomaFM · Fluid