A foreign key with no index

A crew member's card shows their shifts. It opens slowly for everyone, even though a person has only dozens of shifts. crew_id is a foreign key to ls_crew. PostgreSQL creates an index for a PRIMARY key, but never for a foreign one: the link is declared, yet there is no road along it. Two hundred thousand shifts are read whole for the sake of forty rows. Build the missing road — and spare the plan its sort while you are at it.

Speed this query up with an index

SELECT shift_id, duty_day, deck, hours
FROM ls_shifts
WHERE crew_id = 4242
ORDER BY duty_day

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_shifts
the query reads at most 134 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