630 lines
18 KiB
Plaintext
630 lines
18 KiB
Plaintext
<!-- 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>
|