Mastering PostgreSQL 9.6

Making sense of pg_stat_user_tables

Reading all this data might be interesting; however, unless you are able to make sense out of it, it is pretty pointless. One way to use pg_stat_user_tables is to detect which tables might need an index. One way to get a clue of the right direction is to use the following query, which has served me well over the years:

SELECT schemaname, relname,         seq_scan,         seq_tup_read,         seq_tup_read / seq_scan AS avg,         idx_scan  FROM     pg_stat_user_tables  WHERE    seq_scan > 0  ORDER BY seq_tup_read  DESC LIMIT 25;

The idea is to find large tables, which have been used frequently in a sequential scan. Those tables will naturally come out on top of the list to bless us with enormously high seq_tup_read values, which can be ...