Index Design for a Land Registry Query Mix
A land registry table holds 40 million parcel rows: parcel_id (primary key), district_code (about 600 distinct values), owner_national_id, registration_date, status (5 values, heavily skewed toward 'active'), and a free-text remarks column. The three dominant queries are: (a) fetch a parcel by parcel_id; (b) list parcels for a district filtered by status and sorted by registration_date; (c) count active parcels per district for a monthly report. Writes are about 3,000 inserts and updates per day. A developer proposes indexing every column to 'make queries fast'.
1. Critique the 'index every column' proposal and design indexes for queries (a), (b), and (c).
Read the worked answer
Critique. Each index adds write cost and storage. At 3,000 writes/day the write volume is low, so write amplification is not fatal, but indexing low-selectivity columns (status with 5 values, district_code with 600 values over 40M rows) yields poor selectivity alone. Indexing the free-text remarks column is close to useless for equality/range queries and expensive in space. 'Index everything' is a reflex, not a design.
(a) The primary key already provides an index; no extra index needed.
(b) A composite index on (district_code, status, registration_date) serves the filter and the sort. Leading with district_code gives good selectivity (about 67,000 rows per district), status narrows further, and registration_date satisfies the ORDER BY without a sort step. Column order matters: putting status first would be weaker because 'active' matches most rows.
(c) A covering index on (district_code, status) allows the count to be answered from the index without touching the table. Alternatively a materialised monthly summary table may be cheaper if the report is run often. The answer should justify column order by selectivity and sort avoidance.
2. The monthly report is slow despite indexing. What else should be investigated?
Read the worked answer
Several causes are plausible and should be checked in order:
- Stale statistics. The optimiser may choose a poor plan if statistics are outdated, especially with a heavily skewed status column. Refresh statistics and check the plan.
- Skew. Because 'active' dominates, a query counting active parcels scans most of the table regardless of index. A partial index filtered to active rows, or a summary table maintained on write, may be the real fix.
- Report design. A per-district count over 40M rows is an aggregation; running it against the live table monthly is wasteful. A materialised view refreshed nightly is often the correct architecture.
- Covering vs table access. Confirm the index actually covers the query; otherwise the engine performs many random reads.
The judgement being tested is to distinguish 'index missing' from 'aggregation should be precomputed'. Adding more indexes to a skewed aggregate query is unlikely to help and may mislead the team.