Mastering PostgreSQL 12 - Third Edition

Making sense of pg_stat_user_tables

Reading all of this data may 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 may need an index. One way to find this out 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 that have been used frequently in a sequential scan. Those tables will naturally come out on the top of the list to bless us with enormously high seq_tup_read values, which can be mind-blowing.

Work your ...