Home • Explore • Blog • CI
☰



postgresql
2nd chunk of `doc/src/sgml/pgstattuple.sgml`
36e5f0c11849e08a8892c8a35c76ad4446bd486b71025eb10000000100000fb8
 </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>
Title: pgstattuple Functions: Details and Usage
Summary
The pgstattuple module provides functions to analyze tuple-level statistics in PostgreSQL. The 'pgstattuple(regclass)' function returns a record with details like physical relation length, live and dead tuple counts, and free space. The table_len is always greater than the sum of tuple_len, dead_tuple_len, and free_space due to overhead. It acquires a read lock, so results may be affected by concurrent updates. A tuple is considered 'dead' if HeapTupleSatisfiesDirty returns false. An older function 'pgstattuple(text)' serves the same purpose but is deprecated. Additionally, 'pgstatindex(regclass)' returns information about a B-tree index, including version, tree level, size, root block location, and page counts.