Making sense of pg_stat_user_tables

Reading all of 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 in regards to 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 that 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 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 golden—it will help you spot tables with missing indexes. Practical experience of close to two decades 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 contain information about all kinds of things, such as 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