Adding upstream version 16.2.upstream/16.2

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

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 &mdash; 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=&gt; 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=&gt; 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=&gt; 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=&gt; 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=&gt; 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>