March 7, 2024
Summary: This section of report contains top database objects, using statistics from Statistics Collector views.
Table of Contents
Top tables by estimated sequentially scanned volume
Top database tables sorted by estimated volume, read by sequential scans. Based on pg_stat_all_tables view. Here you can search for tables, possibly lacks some index on it. When there is no relation sizes collected with pg_relation_size(), estimation will be calculated based on pg_class.relpages field. This will be less accurate, so such values will be provided in square brackets.
- DB - database name of the table
- Tablespace - tablespace name, where the table is located
- Schema - schema name of the table
- Table - table name
- ~SeqBytes - estimated volume, read by sequential scans. Calculated as a sum of relation size multiplied by seq_scan for all samples of a report.
- SeqScan - number of sequential scans performed on the table (seq_scan field)
- IxScan - number of index scans initiated on this table (idx_scan field)
- IxFet - number of live rows fetched by index scans (idx_tup_fetch field)
- Ins - number of rows inserted (n_tup_ins field)
- Upd - number of rows updated (including HOT) (n_tup_upd field)
- Del - number of rows deleted (n_tup_del field)
- Upd(HOT) - number of rows HOT updated (n_tup_hot_upd field)
Top tables by blocks fetched
Fetched block is a block being processed from disk (read), or from shared buffers (hit). Tables in this list are sorted by sum of fetched blocks for table relation, its indexes, TOAST of a table (if exists), and TOAST index (if exists). This section can focus your attention on tables with excessive blocks processing. Based on data of pg_statio_all_tables view.
- DB - database name of the table
- Tablespace - tablespace name, where the table is located
- Schema - schema name of the table
- Table - table name
- Heap - statistics for relation blocks fetched (heap_blks_read + heap_blks_hit)
- Ix - statistics for all relation indexes blocks fetched (idx_blks_read + idx_blks_hit)
- TOAST - statistics for TOAST-table blocks fetched (toast_blks_read + toast_blks_hit)
- TOAST-Ix - statistics for TOAST index blocks fetched (tidx_blks_read + tidx_blks_hit)
Each statistic field in this table is divided in two columns:
- Blks - number of blocks fetched for relation heap, index, TOAST or TOAST index
- %Total - blocks fetched for relation heap, index, TOAST or TOAST index as a percentage of all blocks fetched in a whole cluster
Top tables by blocks read
Top tables sorted by block reads. Tables in this list are sorted by sum of block reads for table, its indexes, TOAST of a table (if exists), and TOAST index (if exists). This section can focus your attention on tables with excessive blocks reading. Based on data of pg_statio_all_tables view.
- DB - database name of the table
- Tablespace - tablespace name, where the table is located
- Schema - schema name of the table
- Table - table name
- Heap - statistics for relation block reads (heap_blks_read)
- Ix - statistics for all relation indexes blocks reads (idx_blks_read)
- TOAST - statistics for TOAST-table block reads (toast_blks_read)
- TOAST-Ix - statistics for TOAST index block reads (tidx_blks_read)
- Hit(%) - number of heap, indexes, toast and toast index blocks fetched from shared buffers as a percentage of all their blocks fetched from shared buffers and file system
Each read statistic in this table is divided in two columns:
- Blks - number of block reads for relation heap, index, TOAST or TOAST index
- %Total - block reads for relation heap, index, TOAST or TOAST index as a percentage of all block reads in a whole cluster
Top DML tables
Top tables sorted by amount of DML-affected rows, i.e. sum of n_tup_ins, n_tup_upd and n_tup_del (including TOAST tables).
- DB - database name of the table
- Tablespace - tablespace name, where the table is located
- Schema - schema name of the table
- Table - table name
- Ins - number of rows inserted (n_tup_ins field)
- Upd - number of rows updated (including HOT) (n_tup_upd field)
- Del - number of rows deleted (n_tup_del field)
- Upd(HOT) - number of rows HOT updated (n_tup_hot_upd field)
- SeqScan - number of sequential scans performed on the table (seq_scan field)
- SeqFet - number of live rows fetched by sequential scans (seq_tup_read field)
- IxScan - number of index scans initiated on this table (idx_scan field)
- IxFet - number of live rows fetched by index scans (idx_tup_fetch field)
Top tables by updated/deleted tuples
Top tables sorted by amount of operations, causing autovacuum load, i.e. sum of n_tup_upd and n_tup_del (including TOAST tables). Consider fine-tune of vacuum-related parameters based on provided vacuum and analyze run statistics.
- DB - database name of the table
- Tablespace - tablespace name, where the table is located
- Schema - schema name of the table
- Table - table name
- Upd - number of rows updated (including HOT) (n_tup_upd field)
- Upd(HOT) - number of rows HOT updated (n_tup_hot_upd field)
- Del - number of rows deleted (n_tup_del field)
- Vacuum - number of times this table has been manually vacuumed (not counting VACUUM FULL) (vacuum_count field)
- AutoVacuum - number of times this table has been vacuumed by the autovacuum daemon (autovacuum_count field)
- Analyze - number of times this table has been manually analyzed (analyze_count field)
- AutoAnalyze - number of times this table has been analyzed by the autovacuum daemon (autoanalyze_count field)
Top tables by new-page updated tuples
Top tables by number of rows updated where the successor version goes onto a new heap page, leaving behind an original version with a t_ctid field that points to a different heap page. These are always non-HOT updates.
- DB - database name of the table
- Tablespace - tablespace name, where the table is located
- Schema - schema name of the table
- Table - table name
- NP Upd - number of rows updated to a new heap page
- %Upd - number of new-page updated rows as a percentage of all rows updated
- Upd - number of rows updated (includes HOT updated rows)
- Upd(HOT) - number of rows HOT updated (i.e., with no separate index update required)
Top growing tables
Top tables sorted by growth. When there is no relation sizes collected with pg_relation_size(), table growth will be calculated based on pg_class.relpages field. This will be less accurate, so such values will be provided in square brackets.
- DB - database name of the table
- Tablespace - tablespace name, where the table is located
- Schema - schema name of the table
- Table - table name
- Size - table size, as it was at the moment of last sample in report interval
- Growth - table growth
- Ins - number of rows inserted (n_tup_ins field)
- Upd - number of rows updated (including HOT) (n_tup_upd field)
- Del - number of rows deleted (n_tup_del field)
- Upd(HOT) - number of rows HOT updated (n_tup_hot_upd field)
Top indexes by blocks fetched
Fetched block is a block being processed from disk (read), or from shared buffers (hit). Based on data of pg_statio_all_indexes view.
- DB - database name of the index
- Tablespace - tablespace name, where the index is located
- Schema - schema name of the index
- Table - table name
- Index - index name
- Scans - number of scans, performed on index (idx_scan field)
- Blks - blocks fetched from this index (idx_blks_read + idx_blks_hit)
- %Total - blocks fetched for this index as a percentage of all blocks fetched in a whole cluster
Top indexes by blocks read
Top indexes sorted by block reads. Based on data of pg_statio_all_indexes view.
- DB - database name of the index
- Tablespace - tablespace name, where the index is located
- Schema - schema name of the index
- Table - table name
- Index - index name
- Scans - number of scans, performed on index (idx_scan field)
- Blk Reads - number of disk blocks read from this index (idx_blks_read)
- %Total - block reads from this index as a percentage of all block reads in a whole cluster
- Hits(%) - percentage of index blocks got from buffer cache within all index blocks fetched for this index
Top growing indexes
Top indexes sorted by growth. When there is no relation sizes collected with pg_relation_size(), estimation will be calculated based on pg_class.relpages field. This will be less accurate, so such values will be provided in square brackets.
- DB - database name of the index
- Tablespace - tablespace name, where the index is located
- Schema - schema name of the index
- Table - table name
- Index - index name
- Index - index statistics
- Size - index size, as it was at the moment of last sample in report interval
- Growth - index growth during report interval
- Table - underlying table statistics
- Ins - number of rows inserted into underlying table (n_tup_ins field)
- Upd - number of rows updated in underlying table (without HOT) (n_tup_upd - n_tup_hot_upd)
- Del - number of rows deleted from underlying table (n_tup_del field)
Unused indexes
Non-scanned indexes during report interval sorted by DML operations on underlying tables, causing index support. Constraint indexes are excluded.
- DB - database name of the index
- Tablespace - tablespace name, where the index is located
- Schema - schema name of the index
- Table - table name
- Index - index name
- Index - index statistics
- Size - index size, as it was at the moment of last sample in report interval
- Growth - index growth during report interval
- Table - underlying table statistics
- Ins - number of rows inserted into underlying table (n_tup_ins field)
- Upd - number of rows updated in underlying table (without HOT) (n_tup_upd - n_tup_hot_upd)
- Del - number of rows deleted from underlying table (n_tup_del field)