Objects

pg_stat_all_tables

Use pg_stat_all_tables to understand how each table is being accessed—whether rows are retrieved by sequential scans or index scans, and how often rows are inserted, updated, or deleted. This helps identify tables that lack appropriate indexes or need more frequent vacuuming.

n_tup_upd counts all updated rows, including HOT updates. n_tup_hot_upd counts only HOT updates—rows updated in place without modifying index entries. A high n_tup_hot_upd relative to n_tup_upd indicates that HOT updates are working efficiently.

pg_statio_all_tables

Use pg_statio_all_tables to determine the effectiveness of the buffer cache for each table. A low cache hit ratio indicates that the database is reading frequently from disk, which can degrade performance.

pg_stat_all_indexes

Use pg_stat_all_indexes to determine which indexes are being used and how effective they are. Indexes with a low idx_scan count relative to the number of rows in the table may not be worth the overhead.

Indexes can be accessed by simple index scans, bitmap scans, or the query optimizer. In a bitmap scan, output from multiple indexes can be combined using AND or OR rules. Because individual rows in a bitmap scan cannot be associated with specific indexes, a bitmap scan increases idx_tup_read for the participating indexes and increases pg_stat_all_tables.idx_tup_fetch for the table—but does not affect idx_tup_fetch for the indexes themselves. If constant values fall outside the range of optimizer statistics, the optimizer may still access indexes to verify the values.

idx_tup_read and idx_tup_fetch can differ even without bitmap scans. idx_tup_read counts index entries retrieved from the index, while idx_tup_fetch counts live rows fetched from the table. idx_tup_fetch is lower when dead rows or not-yet-committed rows are encountered during the scan, or when index-only scans avoid fetching heap rows entirely.

pg_statio_all_indexes

Use pg_statio_all_indexes to calculate the cache hit ratio for each index. This helps identify indexes that are read from disk frequently, which can slow down query execution.

pg_statio_all_sequences

pg_statio_all_sequences shows I/O statistics for each sequence in the current database. Use it to identify sequences with high disk read counts, which can indicate buffer cache pressure on sequence blocks.

pg_stat_user_functions

pg_stat_user_functions shows execution statistics for tracked functions. Use it to identify functions with high call counts or excessive total execution time.