Skip to Content
PostgreSQL Administration Essentials
book

PostgreSQL Administration Essentials

by Hans-Jürgen Schönig
October 2014
Intermediate to advanced content levelIntermediate to advanced
142 pages
3h 9m
English
Packt Publishing
Content preview from PostgreSQL Administration Essentials

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

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

PostgreSQL 9 Administration Cookbook - Second Edition

PostgreSQL 9 Administration Cookbook - Second Edition

Simon Riggs, GIANNI CIOLLI, Hannu Krosing, Gabriele Bartolini

Publisher Resources

ISBN: 9781783988983