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 way from top to bottom and look for expensive scans. Keep in mind that sequential scans are not necessarily bad. They appear naturally in backups, analytical statements, and so on without causing any harm. However, if you are running large sequential scans all the time, your performance will go down the drain.

Note that this query is really goldenit will help you spot tables with missing indexes. My practical experience, which is nearly two decades' worth, has shown again and again that missing indexes are the single most important reason for bad performance. Therefore, the query you are looking at is literally gold.

Once you are done looking for potentially missing indexes, consider taking a brief look at the caching behavior of your tables. To facilitate this, pg_statio_user_tables contains information about all kinds of things, such as the caching behavior of the table (heap_blks_), of your indexes (idx_blks_), and of The Oversized-Attribute Storage Technique (TOAST) tables. Finally, you can find out more about TID scans, which are usually irrelevant to the overall performance of the system:

test=# d pg_statio_user_tables 
View "pg_catalog.pg_statio_user_tables"
Column | Type | Collation | Nullable | Default
-----------------+--------+-----------+----------+---------
relid | oid | | |
schemaname | name | | |
relname | name | | |
heap_blks_read | bigint | | |
heap_blks_hit | bigint | | |
idx_blks_read | bigint | | |
idx_blks_hit | bigint | | |
toast_blks_read | bigint | | |
toast_blks_hit | bigint | | |
tidx_blks_read | bigint | | |
tidx_blks_hit | bigint | | |

Although pg_statio_user_tables contains important information, it is usually the case that pg_stat_user_tables is more likely to provide you with a really relevant insight (such as a missing index and so on).

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset