Learn index strategy vocabulary: composite indexes, covering indexes, partial indexes, index bloat, unused indexes, and index maintenance.
0 / 5 completed
1 / 5
What is a 'covering index' and when is it valuable?
A covering index adds non-key columns to the index (using INCLUDE in PostgreSQL) so the query engine can return results directly from the index. This eliminates expensive heap fetches and dramatically speeds up read-heavy queries.
2 / 5
What is a 'partial index' and what problem does it solve?
Partial indexes index only the rows matching a predicate, making them smaller and faster to maintain than full-table indexes. They're ideal for queries that always filter on the same condition, like querying only active records or only unprocessed jobs.
3 / 5
What is 'index bloat' and how does it affect performance?
In PostgreSQL, deleted or updated rows leave dead tuples in both the table and its indexes until VACUUM cleans them. Index bloat means the index pages contain many dead entries, making scans slower and the index larger than the live data warrants. REINDEX or VACUUM can reclaim space.
4 / 5
A DBA says 'the index isn't being used because the query has a function call on the column'. What does this mean?
Standard B-tree indexes store the column value as-is. If the WHERE clause transforms the value with a function (LOWER(), DATE_TRUNC(), etc.), the planner can't match it to the index. The fix is an expression index: CREATE INDEX ON users (LOWER(email)).
5 / 5
What does 'we dropped the unused index' mean in a database optimisation context?
Every index must be maintained on every write operation. Unused indexes consume write performance and storage with no read benefit. Regularly auditing index usage (pg_stat_user_indexes.idx_scan) and dropping unused ones is a key maintenance practice.