Detecting missing indexes
Now that we have covered the basics and some selected advanced topics of indexing, we want to shift our attention to a major and highly important administrative task: hunting down missing indexes.
When talking about missing indexes, there is one essential query I have found to be highly valuable. The query is given as follows:
test=# \x Expanded display (expanded) is on. test=# SELECT relname, seq_scan, seq_tup_read, idx_scan, idx_tup_fetch, seq_tup_read / seq_scan FROM pg_stat_user_tables WHERE seq_scan > 0 ORDER BY seq_tup_read DESC; -[ RECORD 1 ]-+--------- relname | t_user seq_scan | 824350 seq_tup_read | 2970269443530 idx_scan | 0 idx_tup_fetch | 0 ?column? | 3603165
The pg_stat_user_tables
Get PostgreSQL Administration Essentials now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.