Book Image

PostgreSQL Administration Essentials

Book Image

PostgreSQL Administration Essentials

Overview of this book

Table of Contents (14 chapters)

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 option contains statistical information about tables and their access patterns. In this example, we found a classic problem. The t_user table has been scanned close to 1 million times...