I have written the documentary and attached the patch.
On 08.06.2024 09:30, Alena Rybakina wrote:
Iam currentlyworkingondividingthispatchintothreepartstosimplifythe reviewprocess:oneofthemwillcontaincodeforcollectingvacuumstatisticsontables,the secondonindexesandthe lastondatabases.I alsowritethe documentation.
-- Regards, Alena Rybakina Postgres Professional:http://www.postgrespro.com The Russian Postgres Company
From ba01e9b5b14a0b46482beea20127062e8ae7e067 Mon Sep 17 00:00:00 2001 From: Alena Rybakina <a.rybak...@postgrespro.ru> Date: Mon, 17 Jun 2024 00:48:45 +0300 Subject: [PATCH] Add documentation about the system views that are used in the machinery of vacuum statistics. --- doc/src/sgml/system-views.sgml | 747 +++++++++++++++++++++++++++++++++ 1 file changed, 747 insertions(+) diff --git a/doc/src/sgml/system-views.sgml b/doc/src/sgml/system-views.sgml index 67a42a1c4d4..dfef8ed44b8 100644 --- a/doc/src/sgml/system-views.sgml +++ b/doc/src/sgml/system-views.sgml @@ -5738,4 +5738,751 @@ ALTER DATABASE template0 ALLOW_CONNECTIONS off; </table> </sect1> +<sect1 id="view-pg-stats-vacuum-database"> + <title><structname>pg_stats_vacuum_database</structname></title> + + <indexterm zone="view-pg-stats-vacuum-database"> + <primary>pg_stats_vacuum_database</primary> + </indexterm> + + <para> + The view <structname>pg_stats_vacuum_database</structname> will contain + one row for each database in the current cluster, showing statistics about + vacuuming that database. + </para> + + <table> + <title><structname>pg_stats_vacuum_database</structname> Columns</title> + <tgroup cols="1"> + <thead> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + Column Type + </para> + <para> + Description + </para></entry> + </row> + </thead> + + <tbody> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>dbid</structfield> <type>oid</type> + </para> + <para> + OID of a database + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>total_blks_read</structfield> <type>int8</type> + </para> + <para> + Number of database blocks read by vacuum operations + performed on this database + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>total_blks_hit</structfield> <type>int8</type> + </para> + <para> + Number of times database blocks were found in the + buffer cache by vacuum operations + performed on this database + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>total_blks_dirtied</structfield> <type>int8</type> + </para> + <para> + Number of database blocks dirtied by vacuum operations + performed on this database + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>total_blks_written</structfield> <type>int8</type> + </para> + <para> + Number of database blocks written by vacuum operations + performed on this database + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>wal_records</structfield> <type>int8</type> + </para> + <para> + Total number of WAL records generated by vacuum operations + performed on this database + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>wal_fpi</structfield> <type>int8</type> + </para> + <para> + Total number of WAL full page images generated by vacuum operations + performed on this database + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>wal_bytes</structfield> <type>numeric</type> + </para> + <para> + Total amount of WAL bytes generated by vacuum operations + performed on this database + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>blk_read_time</structfield> <type>float8</type> + </para> + <para> + Time spent reading database blocks by vacuum operations performed on + this database, in milliseconds (if <xref linkend="guc-track-io-timing"/> is enabled, + otherwise zero) + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>blk_write_time</structfield> <type>float8</type> + </para> + <para> + Time spent writing database blocks by vacuum operations performed on + this database, in milliseconds (if <xref linkend="guc-track-io-timing"/> is enabled, + otherwise zero) + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>delay_time</structfield> <type>float8</type> + </para> + <para> + Time spent sleeping in a vacuum delay point by vacuum operations performed on + this database, in milliseconds (see <xref linkend="runtime-config-resource-vacuum-cost"/> + for details) + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>system_time</structfield> <type>float8</type> + </para> + <para> + System CPU time of vacuuming this database, in milliseconds + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>user_time</structfield> <type>float8</type> + </para> + <para> + User CPU time of vacuuming this database, in milliseconds + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>total_time</structfield> <type>float8</type> + </para> + <para> + Total time of vacuuming this database, in milliseconds + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>interrupts</structfield> <type>int4</type> + </para> + <para> + Number of times vacuum operations performed on this database + were interrupted on any errors + </para></entry> + </row> + </tbody> + </tgroup> + </table> + </sect1> + + <sect1 id="view-pg-stats-vacuum-indexes"> + <title><structname>pg_stats_vacuum_indexes</structname></title> + + <indexterm zone="view-pg-stats-vacuum-indexes"> + <primary>pg_stats_vacuum_indexes</primary> + </indexterm> + + <para> + The view <structname>pg_stats_vacuum_indexes</structname> will contain + one row for each index in the current database (including TOAST + table indexes), showing statistics about vacuuming that specific index. + </para> + + <table> + <title><structname>pg_stats_vacuum_indexes</structname> Columns</title> + <tgroup cols="1"> + <thead> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + Column Type + </para> + <para> + Description + </para></entry> + </row> + </thead> + + <tbody> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>relid</structfield> <type>oid</type> + </para> + <para> + OID of an index + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>schema</structfield> <type>name</type> + </para> + <para> + Name of the schema this index is in + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>relname</structfield> <type>name</type> + </para> + <para> + Name of this index + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>total_blks_read</structfield> <type>int8</type> + </para> + <para> + Number of database blocks read by vacuum operations + performed on this index + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>total_blks_hit</structfield> <type>int8</type> + </para> + <para> + Number of times database blocks were found in the + buffer cache by vacuum operations + performed on this index + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>total_blks_dirtied</structfield> <type>int8</type> + </para> + <para> + Number of database blocks dirtied by vacuum operations + performed on this index + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>total_blks_written</structfield> <type>int8</type> + </para> + <para> + Number of database blocks written by vacuum operations + performed on this index + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>rel_blks_read</structfield> <type>int8</type> + </para> + <para> + Number of blocks vacuum operations read from this + index + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>rel_blks_hit</structfield> <type>int8</type> + </para> + <para> + Number of times blocks of this index were already found + in the buffer cache by vacuum operations, so that a read was not necessary + (this only includes hits in the + &project; buffer cache, not the operating system's file system cache) + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>pages_deleted</structfield> <type>int8</type> + </para> + <para> + Number of pages deleted by vacuum operations + performed on this index + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>tuples_deleted</structfield> <type>int8</type> + </para> + <para> + Number of dead tuples vacuum operations deleted from this index + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>wal_records</structfield> <type>int8</type> + </para> + <para> + Total number of WAL records generated by vacuum operations + performed on this index + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>wal_fpi</structfield> <type>int8</type> + </para> + <para> + Total number of WAL full page images generated by vacuum operations + performed on this index + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>wal_bytes</structfield> <type>numeric</type> + </para> + <para> + Total amount of WAL bytes generated by vacuum operations + performed on this index + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>blk_read_time</structfield> <type>int8</type> + </para> + <para> + Time spent reading database blocks by vacuum operations performed on + this index, in milliseconds (if <xref linkend="guc-track-io-timing"/> is enabled, + otherwise zero) + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>blk_write_time</structfield> <type>int8</type> + </para> + <para> + Time spent writing database blocks by vacuum operations performed on + this index, in milliseconds (if <xref linkend="guc-track-io-timing"/> is enabled, + otherwise zero) + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>delay_time</structfield> <type>float8</type> + </para> + <para> + Time spent sleeping in a vacuum delay point by vacuum operations performed on + this index, in milliseconds (see <xref linkend="runtime-config-resource-vacuum-cost"/> + for details) + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>system_time</structfield> <type>float8</type> + </para> + <para> + System CPU time of vacuuming this index, in milliseconds + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>user_time</structfield> <type>float8</type> + </para> + <para> + User CPU time of vacuuming this index, in milliseconds + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>total_time</structfield> <type>float8</type> + </para> + <para> + Total time of vacuuming this index, in milliseconds + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>interrupts</structfield> <type>float8</type> + </para> + <para> + Number of times vacuum operations performed on this index + were interrupted on any errors + </para></entry> + </row> + </tbody> + </tgroup> + </table> + </sect1> + + <sect1 id="view-pg-stats-vacuum-tables"> + <title><structname>pg_stats_vacuum_tables</structname></title> + + <indexterm zone="view-pg-stats-vacuum-tables"> + <primary>pg_stats_vacuum_tables</primary> + </indexterm> + + <para> + The view <structname>pg_stats_vacuum_tables</structname> will contain + one row for each table in the current database (including TOAST + tables), showing statistics about vacuuming that specific table. + </para> + + <table> + <title><structname>pg_stats_vacuum_tables</structname> Columns</title> + <tgroup cols="1"> + <thead> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + Column Type + </para> + <para> + Description + </para></entry> + </row> + </thead> + + <tbody> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>relid</structfield> <type>oid</type> + </para> + <para> + OID of a table + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>schema</structfield> <type>name</type> + </para> + <para> + Name of the schema this table is in + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>relname</structfield> <type>name</type> + </para> + <para> + Name of this table + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>total_blks_read</structfield> <type>int8</type> + </para> + <para> + Number of database blocks read by vacuum operations + performed on this table + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>total_blks_hit</structfield> <type>int8</type> + </para> + <para> + Number of times database blocks were found in the + buffer cache by vacuum operations + performed on this table + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>total_blks_dirtied</structfield> <type>int8</type> + </para> + <para> + Number of database blocks dirtied by vacuum operations + performed on this table + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>total_blks_written</structfield> <type>int8</type> + </para> + <para> + Number of database blocks written by vacuum operations + performed on this table + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>rel_blks_read</structfield> <type>int8</type> + </para> + <para> + Number of blocks vacuum operations read from this + table + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>rel_blks_hit</structfield> <type>int8</type> + </para> + <para> + Number of times blocks of this table were already found + in the buffer cache by vacuum operations, so that a read was not necessary + (this only includes hits in the + &project; buffer cache, not the operating system's file system cache) + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>pages_scanned</structfield> <type>int8</type> + </para> + <para> + Number of pages examined by vacuum operations + performed on this table + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>pages_removed</structfield> <type>int8</type> + </para> + <para> + Number of pages removed from the physical storage by vacuum operations + performed on this table + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>pages_frozen</structfield> <type>int8</type> + </para> + <para> + Number of times vacuum operations marked pages of this table + as all-frozen in the visibility map + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>pages_all_visible</structfield> <type>int8</type> + </para> + <para> + Number of times vacuum operations marked pages of this table + as all-visible in the visibility map + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>tuples_deleted</structfield> <type>int8</type> + </para> + <para> + Number of dead tuples vacuum operations deleted from this table + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>tuples_frozen</structfield> <type>int8</type> + </para> + <para> + Number of tuples of this table that vacuum operations marked as + frozen + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>dead_tuples</structfield> <type>int8</type> + </para> + <para> + Number of dead tuples vacuum operations left in this table due + to their visibility in transactions + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>index_vacuum_count</structfield> <type>int8</type> + </para> + <para> + Number of times indexes on this table were vacuumed + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>rev_all_frozen_pages</structfield> <type>int8</type> + </para> + <para> + Number of times the all-frozen mark in the visibility map + was removed for pages of this table + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>rev_all_visible_pages</structfield> <type>int8</type> + </para> + <para> + Number of times the all-visible mark in the visibility map + was removed for pages of this table + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>wal_records</structfield> <type>int8</type> + </para> + <para> + Total number of WAL records generated by vacuum operations + performed on this table + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>wal_fpi</structfield> <type>int8</type> + </para> + <para> + Total number of WAL full page images generated by vacuum operations + performed on this table + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>wal_bytes</structfield> <type>numeric</type> + </para> + <para> + Total amount of WAL bytes generated by vacuum operations + performed on this table + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>blk_read_time</structfield> <type>int8</type> + </para> + <para> + Time spent reading database blocks by vacuum operations performed on + this table, in milliseconds (if <xref linkend="guc-track-io-timing"/> is enabled, + otherwise zero) + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>blk_write_time</structfield> <type>int8</type> + </para> + <para> + Time spent writing database blocks by vacuum operations performed on + this table, in milliseconds (if <xref linkend="guc-track-io-timing"/> is enabled, + otherwise zero) + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>delay_time</structfield> <type>float8</type> + </para> + <para> + Time spent sleeping in a vacuum delay point by vacuum operations performed on + this table, in milliseconds (see <xref linkend="runtime-config-resource-vacuum-cost"/> + for details) + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>system_time</structfield> <type>float8</type> + </para> + <para> + System CPU time of vacuuming this table, in milliseconds + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>user_time</structfield> <type>float8</type> + </para> + <para> + User CPU time of vacuuming this table, in milliseconds + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>total_time</structfield> <type>float8</type> + </para> + <para> + Total time of vacuuming this table, in milliseconds + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>interrupts</structfield> <type>float8</type> + </para> + <para> + Number of times vacuum operations performed on this table + were interrupted on any errors + </para></entry> + </row> + </tbody> + </tgroup> + </table> + <para>Columns <structfield>total_*</structfield>, <structfield>wal_*</structfield> + and <structfield>blk_*</structfield> include data on vacuuming indexes on this table, while columns + <structfield>system_time</structfield> and <structfield>user_time</structfield> only include data + on vacuuming the heap.</para> + </sect1> + </chapter> -- 2.34.1
