Query Optimization — Series 3

Preview — 3 of 10 questions

A status column has an index, but EXPLAIN shows a Seq Scan for WHERE status = 'active', even though 95% of the 1M rows are 'active'. Why?

AThe index is corrupted and needs REINDEX

BPostgreSQL never uses indexes for equality comparisons on text columns

CThe planner estimates that scanning almost the whole table sequentially is cheaper than following an index into random heap pages for 95% of the rows — a sequential scan reading data in physical order beats an index scan when a filter isn't selective

DThe query needs WHERE status = 'active'::text with an explicit cast to use the index

Given an index on created_at, why does SELECT * FROM events ORDER BY created_at DESC LIMIT 10; typically run fast even on a huge table?

AThe planner can walk the created_at index backward, taking just the first 10 entries it finds — it never needs to sort or even touch most of the table

BLIMIT makes PostgreSQL skip ORDER BY entirely

CPostgreSQL caches the 10 most recent rows automatically

DLIMIT 10 forces a sequential scan capped at 10 rows

A paginated API uses ORDER BY id LIMIT 20 OFFSET N. Page 5 (OFFSET 80) is fast; page 5,000 (OFFSET 100000) is noticeably slower. Why?

AOFFSET values above 10,000 trigger a different, slower query plan in PostgreSQL

BPostgreSQL has to actually compute and discard the first OFFSET rows every single time — the database re-does that work on every request, so cost grows roughly linearly with OFFSET, unlike LIMIT which stays cheap

CLater pages have more data because the table has grown since page 1 was requested

DIt only gets slower if the table lacks a primary key

Answer all 10 questions (7 more), see explanations for every answer, and track your score.