Recognises an index set not derived from access paths
Indexes missing where foreign keys, natural keys and stated access paths need them; indexes duplicated or overlapping; a non-unique index where the rule says unique. The last is a specific form of row 14; the rest are performance defects.
Can AI do this?
Not tested yet
No AI benchmark covers this knobit yet. New results are announced in the Can AI Do the Job? newsletter.
What it looks like in practice
Expected result: Missing, duplicate or wrongly non-unique indexes against the access paths
How it is assessed: Flags the deficient case and passes the acceptable one. Deficient: payment.account_id unindexed, so deleting an account scans and locks payment; an index on every column; ix_party_registry_code non-unique Acceptable: An index for each foreign key (PHY-022), each natural-key unique constraint, and each access path in the requirements, each index's comment citing which; speculative ones marked provisional
Also known as: Index set not derived from access paths (physical model)
Related knobits
Builds on
All 40 knobits of Design the physical data model.
Source: Review checklist 06-physical, row 21; Karwin, Index Shotgun; C&B ("design file organisations and indexes"); S&W 12.4; E&N 17.7