diff options
Diffstat (limited to 'doc/src/sgml/pgstattuple.sgml')
| -rw-r--r-- | doc/src/sgml/pgstattuple.sgml | 629 |
1 files changed, 629 insertions, 0 deletions
diff --git a/doc/src/sgml/pgstattuple.sgml b/doc/src/sgml/pgstattuple.sgml new file mode 100644 index 0000000..4071da4 --- /dev/null +++ b/doc/src/sgml/pgstattuple.sgml @@ -0,0 +1,629 @@ +<!-- doc/src/sgml/pgstattuple.sgml --> + +<sect1 id="pgstattuple" xreflabel="pgstattuple"> + <title>pgstattuple — obtain tuple-level statistics</title> + + <indexterm zone="pgstattuple"> + <primary>pgstattuple</primary> + </indexterm> + + <para> + The <filename>pgstattuple</filename> module provides various functions to + obtain tuple-level statistics. + </para> + + <para> + Because these functions return detailed page-level information, access is + restricted by default. By default, only the + role <literal>pg_stat_scan_tables</literal> has <literal>EXECUTE</literal> + privilege. Superusers of course bypass this restriction. After the + extension has been installed, users may issue <command>GRANT</command> + commands to change the privileges on the functions to allow others to + execute them. However, it might be preferable to add those users to + the <literal>pg_stat_scan_tables</literal> role instead. + </para> + + <sect2 id="pgstattuple-funcs"> + <title>Functions</title> + + <variablelist> + <varlistentry> + <term> + <indexterm> + <primary>pgstattuple</primary> + </indexterm> + <function>pgstattuple(regclass) returns record</function> + </term> + + <listitem> + <para> + <function>pgstattuple</function> returns a relation's physical length, + percentage of <quote>dead</quote> tuples, and other info. This may help users + to determine whether vacuum is necessary or not. The argument is the + target relation's name (optionally schema-qualified) or OID. + For example: +<programlisting> +test=> SELECT * FROM pgstattuple('pg_catalog.pg_proc'); +-[ RECORD 1 ]------+------- +table_len | 458752 +tuple_count | 1470 +tuple_len | 438896 +tuple_percent | 95.67 +dead_tuple_count | 11 +dead_tuple_len | 3157 +dead_tuple_percent | 0.69 +free_space | 8932 +free_percent | 1.95 +</programlisting> + The output columns are described in <xref linkend="pgstattuple-columns"/>. + </para> + + <table id="pgstattuple-columns"> + <title><function>pgstattuple</function> Output Columns</title> + <tgroup cols="3"> + <thead> + <row> + <entry>Column</entry> + <entry>Type</entry> + <entry>Description</entry> + </row> + </thead> + + <tbody> + <row> + <entry><structfield>table_len</structfield></entry> + <entry><type>bigint</type></entry> + <entry>Physical relation length in bytes</entry> + </row> + <row> + <entry><structfield>tuple_count</structfield></entry> + <entry><type>bigint</type></entry> + <entry>Number of live tuples</entry> + </row> + <row> + <entry><structfield>tuple_len</structfield></entry> + <entry><type>bigint</type></entry> + <entry>Total length of live tuples in bytes</entry> + </row> + <row> + <entry><structfield>tuple_percent</structfield></entry> + <entry><type>float8</type></entry> + <entry>Percentage of live tuples</entry> + </row> + <row> + <entry><structfield>dead_tuple_count</structfield></entry> + <entry><type>bigint</type></entry> + <entry>Number of dead tuples</entry> + </row> + <row> + <entry><structfield>dead_tuple_len</structfield></entry> + <entry><type>bigint</type></entry> + <entry>Total length of dead tuples in bytes</entry> + </row> + <row> + <entry><structfield>dead_tuple_percent</structfield></entry> + <entry><type>float8</type></entry> + <entry>Percentage of dead tuples</entry> + </row> + <row> + <entry><structfield>free_space</structfield></entry> + <entry><type>bigint</type></entry> + <entry>Total free space in bytes</entry> + </row> + <row> + <entry><structfield>free_percent</structfield></entry> + <entry><type>float8</type></entry> + <entry>Percentage of free space</entry> + </row> + + </tbody> + </tgroup> + </table> + + <note> + <para> + The <literal>table_len</literal> will always be greater than the sum + of the <literal>tuple_len</literal>, <literal>dead_tuple_len</literal> + and <literal>free_space</literal>. The difference is accounted for by + fixed page overhead, the per-page table of pointers to tuples, and + padding to ensure that tuples are correctly aligned. + </para> + </note> + + <para> + <function>pgstattuple</function> acquires only a read lock on the + relation. So the results do not reflect an instantaneous snapshot; + concurrent updates will affect them. + </para> + + <para> + <function>pgstattuple</function> judges a tuple is <quote>dead</quote> if + <function>HeapTupleSatisfiesDirty</function> returns false. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term> + <function>pgstattuple(text) returns record</function> + </term> + + <listitem> + <para> + This is the same as <function>pgstattuple(regclass)</function>, except + that the target relation is specified as TEXT. This function is kept + because of backward-compatibility so far, and will be deprecated in + some future release. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term> + <indexterm> + <primary>pgstatindex</primary> + </indexterm> + <function>pgstatindex(regclass) returns record</function> + </term> + + <listitem> + <para> + <function>pgstatindex</function> returns a record showing information + about a B-tree index. For example: +<programlisting> +test=> SELECT * FROM pgstatindex('pg_cast_oid_index'); +-[ RECORD 1 ]------+------ +version | 2 +tree_level | 0 +index_size | 16384 +root_block_no | 1 +internal_pages | 0 +leaf_pages | 1 +empty_pages | 0 +deleted_pages | 0 +avg_leaf_density | 54.27 +leaf_fragmentation | 0 +</programlisting> + </para> + + <para> + The output columns are: + + <informaltable> + <tgroup cols="3"> + <thead> + <row> + <entry>Column</entry> + <entry>Type</entry> + <entry>Description</entry> + </row> + </thead> + + <tbody> + <row> + <entry><structfield>version</structfield></entry> + <entry><type>integer</type></entry> + <entry>B-tree version number</entry> + </row> + + <row> + <entry><structfield>tree_level</structfield></entry> + <entry><type>integer</type></entry> + <entry>Tree level of the root page</entry> + </row> + + <row> + <entry><structfield>index_size</structfield></entry> + <entry><type>bigint</type></entry> + <entry>Total index size in bytes</entry> + </row> + + <row> + <entry><structfield>root_block_no</structfield></entry> + <entry><type>bigint</type></entry> + <entry>Location of root page (zero if none)</entry> + </row> + + <row> + <entry><structfield>internal_pages</structfield></entry> + <entry><type>bigint</type></entry> + <entry>Number of <quote>internal</quote> (upper-level) pages</entry> + </row> + + <row> + <entry><structfield>leaf_pages</structfield></entry> + <entry><type>bigint</type></entry> + <entry>Number of leaf pages</entry> + </row> + + <row> + <entry><structfield>empty_pages</structfield></entry> + <entry><type>bigint</type></entry> + <entry>Number of empty pages</entry> + </row> + + <row> + <entry><structfield>deleted_pages</structfield></entry> + <entry><type>bigint</type></entry> + <entry>Number of deleted pages</entry> + </row> + + <row> + <entry><structfield>avg_leaf_density</structfield></entry> + <entry><type>float8</type></entry> + <entry>Average density of leaf pages</entry> + </row> + + <row> + <entry><structfield>leaf_fragmentation</structfield></entry> + <entry><type>float8</type></entry> + <entry>Leaf page fragmentation</entry> + </row> + + </tbody> + </tgroup> + </informaltable> + </para> + + <para> + The reported <literal>index_size</literal> will normally correspond to one more + page than is accounted for by <literal>internal_pages + leaf_pages + + empty_pages + deleted_pages</literal>, because it also includes the + index's metapage. + </para> + + <para> + As with <function>pgstattuple</function>, the results are accumulated + page-by-page, and should not be expected to represent an + instantaneous snapshot of the whole index. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term> + <function>pgstatindex(text) returns record</function> + </term> + + <listitem> + <para> + This is the same as <function>pgstatindex(regclass)</function>, except + that the target index is specified as TEXT. This function is kept + because of backward-compatibility so far, and will be deprecated in + some future release. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term> + <indexterm> + <primary>pgstatginindex</primary> + </indexterm> + <function>pgstatginindex(regclass) returns record</function> + </term> + + <listitem> + <para> + <function>pgstatginindex</function> returns a record showing information + about a GIN index. For example: +<programlisting> +test=> SELECT * FROM pgstatginindex('test_gin_index'); +-[ RECORD 1 ]--+-- +version | 1 +pending_pages | 0 +pending_tuples | 0 +</programlisting> + </para> + + <para> + The output columns are: + + <informaltable> + <tgroup cols="3"> + <thead> + <row> + <entry>Column</entry> + <entry>Type</entry> + <entry>Description</entry> + </row> + </thead> + + <tbody> + <row> + <entry><structfield>version</structfield></entry> + <entry><type>integer</type></entry> + <entry>GIN version number</entry> + </row> + + <row> + <entry><structfield>pending_pages</structfield></entry> + <entry><type>integer</type></entry> + <entry>Number of pages in the pending list</entry> + </row> + + <row> + <entry><structfield>pending_tuples</structfield></entry> + <entry><type>bigint</type></entry> + <entry>Number of tuples in the pending list</entry> + </row> + + </tbody> + </tgroup> + </informaltable> + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term> + <indexterm> + <primary>pgstathashindex</primary> + </indexterm> + <function>pgstathashindex(regclass) returns record</function> + </term> + + <listitem> + <para> + <function>pgstathashindex</function> returns a record showing information + about a HASH index. For example: +<programlisting> +test=> select * from pgstathashindex('con_hash_index'); +-[ RECORD 1 ]--+----------------- +version | 4 +bucket_pages | 33081 +overflow_pages | 0 +bitmap_pages | 1 +unused_pages | 32455 +live_items | 10204006 +dead_items | 0 +free_percent | 61.8005949100872 +</programlisting> + </para> + + <para> + The output columns are: + + <informaltable> + <tgroup cols="3"> + <thead> + <row> + <entry>Column</entry> + <entry>Type</entry> + <entry>Description</entry> + </row> + </thead> + + <tbody> + <row> + <entry><structfield>version</structfield></entry> + <entry><type>integer</type></entry> + <entry>HASH version number</entry> + </row> + + <row> + <entry><structfield>bucket_pages</structfield></entry> + <entry><type>bigint</type></entry> + <entry>Number of bucket pages</entry> + </row> + + <row> + <entry><structfield>overflow_pages</structfield></entry> + <entry><type>bigint</type></entry> + <entry>Number of overflow pages</entry> + </row> + + <row> + <entry><structfield>bitmap_pages</structfield></entry> + <entry><type>bigint</type></entry> + <entry>Number of bitmap pages</entry> + </row> + + <row> + <entry><structfield>unused_pages</structfield></entry> + <entry><type>bigint</type></entry> + <entry>Number of unused pages</entry> + </row> + + <row> + <entry><structfield>live_items</structfield></entry> + <entry><type>bigint</type></entry> + <entry>Number of live tuples</entry> + </row> + + <row> + <entry><structfield>dead_tuples</structfield></entry> + <entry><type>bigint</type></entry> + <entry>Number of dead tuples</entry> + </row> + + <row> + <entry><structfield>free_percent</structfield></entry> + <entry><type>float</type></entry> + <entry>Percentage of free space</entry> + </row> + + </tbody> + </tgroup> + </informaltable> + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term> + <indexterm> + <primary>pg_relpages</primary> + </indexterm> + <function>pg_relpages(regclass) returns bigint</function> + </term> + + <listitem> + <para> + <function>pg_relpages</function> returns the number of pages in the + relation. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term> + <function>pg_relpages(text) returns bigint</function> + </term> + + <listitem> + <para> + This is the same as <function>pg_relpages(regclass)</function>, except + that the target relation is specified as TEXT. This function is kept + because of backward-compatibility so far, and will be deprecated in + some future release. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term> + <indexterm> + <primary>pgstattuple_approx</primary> + </indexterm> + <function>pgstattuple_approx(regclass) returns record</function> + </term> + + <listitem> + <para> + <function>pgstattuple_approx</function> is a faster alternative to + <function>pgstattuple</function> that returns approximate results. + The argument is the target relation's name or OID. + For example: +<programlisting> +test=> SELECT * FROM pgstattuple_approx('pg_catalog.pg_proc'::regclass); +-[ RECORD 1 ]--------+------- +table_len | 573440 +scanned_percent | 2 +approx_tuple_count | 2740 +approx_tuple_len | 561210 +approx_tuple_percent | 97.87 +dead_tuple_count | 0 +dead_tuple_len | 0 +dead_tuple_percent | 0 +approx_free_space | 11996 +approx_free_percent | 2.09 +</programlisting> + The output columns are described in <xref linkend="pgstatapprox-columns"/>. + </para> + + <para> + Whereas <function>pgstattuple</function> always performs a + full-table scan and returns an exact count of live and dead tuples + (and their sizes) and free space, <function>pgstattuple_approx</function> + tries to avoid the full-table scan and returns exact dead tuple + statistics along with an approximation of the number and + size of live tuples and free space. + </para> + + <para> + It does this by skipping pages that have only visible tuples + according to the visibility map (if a page has the corresponding VM + bit set, then it is assumed to contain no dead tuples). For such + pages, it derives the free space value from the free space map, and + assumes that the rest of the space on the page is taken up by live + tuples. + </para> + + <para> + For pages that cannot be skipped, it scans each tuple, recording its + presence and size in the appropriate counters, and adding up the + free space on the page. At the end, it estimates the total number of + live tuples based on the number of pages and tuples scanned (in the + same way that VACUUM estimates pg_class.reltuples). + </para> + + <table id="pgstatapprox-columns"> + <title><function>pgstattuple_approx</function> Output Columns</title> + <tgroup cols="3"> + <thead> + <row> + <entry>Column</entry> + <entry>Type</entry> + <entry>Description</entry> + </row> + </thead> + + <tbody> + <row> + <entry><structfield>table_len</structfield></entry> + <entry><type>bigint</type></entry> + <entry>Physical relation length in bytes (exact)</entry> + </row> + <row> + <entry><structfield>scanned_percent</structfield></entry> + <entry><type>float8</type></entry> + <entry>Percentage of table scanned</entry> + </row> + <row> + <entry><structfield>approx_tuple_count</structfield></entry> + <entry><type>bigint</type></entry> + <entry>Number of live tuples (estimated)</entry> + </row> + <row> + <entry><structfield>approx_tuple_len</structfield></entry> + <entry><type>bigint</type></entry> + <entry>Total length of live tuples in bytes (estimated)</entry> + </row> + <row> + <entry><structfield>approx_tuple_percent</structfield></entry> + <entry><type>float8</type></entry> + <entry>Percentage of live tuples</entry> + </row> + <row> + <entry><structfield>dead_tuple_count</structfield></entry> + <entry><type>bigint</type></entry> + <entry>Number of dead tuples (exact)</entry> + </row> + <row> + <entry><structfield>dead_tuple_len</structfield></entry> + <entry><type>bigint</type></entry> + <entry>Total length of dead tuples in bytes (exact)</entry> + </row> + <row> + <entry><structfield>dead_tuple_percent</structfield></entry> + <entry><type>float8</type></entry> + <entry>Percentage of dead tuples</entry> + </row> + <row> + <entry><structfield>approx_free_space</structfield></entry> + <entry><type>bigint</type></entry> + <entry>Total free space in bytes (estimated)</entry> + </row> + <row> + <entry><structfield>approx_free_percent</structfield></entry> + <entry><type>float8</type></entry> + <entry>Percentage of free space</entry> + </row> + + </tbody> + </tgroup> + </table> + + <para> + In the above output, the free space figures may not match the + <function>pgstattuple</function> output exactly, because the free + space map gives us an exact figure, but is not guaranteed to be + accurate to the byte. + </para> + + </listitem> + </varlistentry> + + </variablelist> + </sect2> + + <sect2 id="pgstattuple-authors"> + <title>Authors</title> + + <para> + Tatsuo Ishii, Satoshi Nagayasu and Abhijit Menon-Sen + </para> + </sect2> + +</sect1> |