pgstattuple.sgml

<!-- doc/src/sgml/pgstattuple.sgml -->

<sect1 id="pgstattuple" xreflabel="pgstattuple">
 <title>pgstattuple</title>

 <indexterm zone="pgstattuple">
  <primary>pgstattuple</primary>
 </indexterm>

 <para>
  The <filename>pgstattuple</filename> module provides various functions to
  obtain tuple-level statistics.
 </para>

 <sect2>
  <title>Functions</title>

  <variablelist>
   <varlistentry>
    <term>
     <function>pgstattuple(text) returns record</>
    </term>

    <listitem>
     <para>
      <function>pgstattuple</function> returns a relation's physical length,
      percentage of <quote>dead</> 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).
      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>

    <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</> if
     <function>HeapTupleSatisfiesNow</> returns false.
    </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term>
     <function>pgstattuple(oid) returns record</>
    </term>

    <listitem>
     <para>
      This is the same as <function>pgstattuple(text)</function>, except
      that the target relation is specified by OID.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term>
     <function>pgstatindex(text) returns record</>
    </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         | 8192
root_block_no      | 1
internal_pages     | 0
leaf_pages         | 1
empty_pages        | 0
deleted_pages      | 0
avg_leaf_density   | 50.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 number of pages in index</entry>
       </row>

       <row>
        <entry><structfield>root_block_no</structfield></entry>
        <entry><type>bigint</type></entry>
        <entry>Location of root block</entry>
       </row>

       <row>
        <entry><structfield>internal_pages</structfield></entry>
        <entry><type>bigint</type></entry>
        <entry>Number of <quote>internal</> (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>
     As with <function>pgstattuple</>, 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>pg_relpages(text) returns bigint</>
    </term>

    <listitem>
     <para>
      <function>pg_relpages</function> returns the number of pages in the
      relation.
     </para>
    </listitem>
   </varlistentry>
  </variablelist>
 </sect2>

 <sect2>
  <title>Authors</title>

  <para>
   Tatsuo Ishii and Satoshi Nagayasu
  </para>
 </sect2>

</sect1>