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...