The cost of indexes
On This Lesson 4 topics
This lesson is planned. The outline below lists the topics it will cover. The full lesson has not been published yet.
In this lesson
weigh read speed against write and storage cost.
Planned topics
- Every insert, and every update that changes indexed columns, touches each index on the table.
- Indexes use disk and memory and add vacuum work.
- Unused indexes are pure cost; find them in
pg_stat_user_indexes(idx_scan = 0). - Drop duplicates and prefixes covered by a longer index.
Draft outline details
Demonstration
SELECT indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) FROM pg_stat_user_indexes ORDER BY idx_scan;
Check your understanding
Why is an index with idx_scan = 0 a problem?
Show answer
It costs writes and space and is never read.
Practice
Apply this lesson with the module's lab, exercises and quiz.
Marking a lesson complete is optional and you can undo it any time. Your progress stays in this browser.