doc/src/sgml/pgstattuple.sgml

   1 <!-- $PostgreSQL$ -->
   2
   3 <sect1 id="pgstattuple">
   4  <title>pgstattuple</title>
   5
   6  <indexterm zone="pgstattuple">
   7   <primary>pgstattuple</primary>
   8  </indexterm>
   9
  10  <para>
  11   The <filename>pgstattuple</filename> module provides various functions to
  12   obtain tuple-level statistics.
  13  </para>
  14
  15  <sect2>
  16   <title>Functions</title>
  17
  18   <variablelist>
  19    <varlistentry>
  20     <term>
  21      <function>pgstattuple(text) returns record</>
  22     </term>
  23
  24     <listitem>
  25      <para>
  26       <function>pgstattuple</function> returns a relation's physical length,
  27       percentage of <quote>dead</> tuples, and other info. This may help users
  28       to determine whether vacuum is necessary or not.  The argument is the
  29       target relation's name (optionally schema-qualified).
  30       For example:
  31 <programlisting>
  32 test=> SELECT * FROM pgstattuple('pg_catalog.pg_proc');
  33 -[ RECORD 1 ]------+-------
  34 table_len          | 458752
  35 tuple_count        | 1470
  36 tuple_len          | 438896
  37 tuple_percent      | 95.67
  38 dead_tuple_count   | 11
  39 dead_tuple_len     | 3157
  40 dead_tuple_percent | 0.69
  41 free_space         | 8932
  42 free_percent       | 1.95
  43 </programlisting>
  44      The output columns are described in <xref linkend="pgstattuple-columns">.
  45     </para>
  46
  47     <table id="pgstattuple-columns">
  48      <title><function>pgstattuple</function> output columns</title>
  49      <tgroup cols="3">
  50       <thead>
  51        <row>
  52         <entry>Column</entry>
  53         <entry>Type</entry>
  54         <entry>Description</entry>
  55        </row>
  56       </thead>
  57
  58       <tbody>
  59        <row>
  60         <entry><structfield>table_len</structfield></entry>
  61         <entry><type>bigint</type></entry>
  62         <entry>Physical relation length in bytes</entry>
  63        </row>
  64        <row>
  65         <entry><structfield>tuple_count</structfield></entry>
  66         <entry><type>bigint</type></entry>
  67         <entry>Number of live tuples</entry>
  68        </row>
  69        <row>
  70         <entry><structfield>tuple_len</structfield></entry>
  71         <entry><type>bigint</type></entry>
  72         <entry>Total length of live tuples in bytes</entry>
  73        </row>
  74        <row>
  75         <entry><structfield>tuple_percent</structfield></entry>
  76         <entry><type>float8</type></entry>
  77         <entry>Percentage of live tuples</entry>
  78        </row>
  79        <row>
  80         <entry><structfield>dead_tuple_count</structfield></entry>
  81         <entry><type>bigint</type></entry>
  82         <entry>Number of dead tuples</entry>
  83        </row>
  84        <row>
  85         <entry><structfield>dead_tuple_len</structfield></entry>
  86         <entry><type>bigint</type></entry>
  87         <entry>Total length of dead tuples in bytes</entry>
  88        </row>
  89        <row>
  90         <entry><structfield>dead_tuple_percent</structfield></entry>
  91         <entry><type>float8</type></entry>
  92         <entry>Percentage of dead tuples</entry>
  93        </row>
  94        <row>
  95         <entry><structfield>free_space</structfield></entry>
  96         <entry><type>bigint</type></entry>
  97         <entry>Total free space in bytes</entry>
  98        </row>
  99        <row>
 100         <entry><structfield>free_percent</structfield></entry>
 101         <entry><type>float8</type></entry>
 102         <entry>Percentage of free space</entry>
 103        </row>
 104
 105       </tbody>
 106      </tgroup>
 107     </table>
 108
 109     <para>
 110      <function>pgstattuple</function> acquires only a read lock on the
 111      relation. So the results do not reflect an instantaneous snapshot;
 112      concurrent updates will affect them.
 113     </para>
 114
 115     <para>
 116      <function>pgstattuple</function> judges a tuple is <quote>dead</> if
 117      <function>HeapTupleSatisfiesNow</> returns false.
 118     </para>
 119     </listitem>
 120    </varlistentry>
 121
 122    <varlistentry>
 123     <term>
 124      <function>pgstattuple(oid) returns record</>
 125     </term>
 126
 127     <listitem>
 128      <para>
 129       This is the same as <function>pgstattuple(text)</function>, except
 130       that the target relation is specified by OID.
 131      </para>
 132     </listitem>
 133    </varlistentry>
 134
 135    <varlistentry>
 136     <term>
 137      <function>pgstatindex(text) returns record</>
 138     </term>
 139
 140     <listitem>
 141      <para>
 142       <function>pgstatindex</function> returns a record showing information
 143       about a btree index.  For example:
 144      </para>
 145      <programlisting>
 146 test=> SELECT * FROM pgstatindex('pg_cast_oid_index');
 147 -[ RECORD 1 ]------+------
 148 version            | 2
 149 tree_level         | 0
 150 index_size         | 8192
 151 root_block_no      | 1
 152 internal_pages     | 0
 153 leaf_pages         | 1
 154 empty_pages        | 0
 155 deleted_pages      | 0
 156 avg_leaf_density   | 50.27
 157 leaf_fragmentation | 0
 158     </programlisting>
 159
 160     <para>
 161      The output columns are:
 162     </para>
 163
 164     <table>
 165      <title><function>pgstatindex</function> output columns</title>
 166      <tgroup cols="3">
 167       <thead>
 168        <row>
 169         <entry>Column</entry>
 170         <entry>Type</entry>
 171         <entry>Description</entry>
 172        </row>
 173       </thead>
 174
 175       <tbody>
 176        <row>
 177         <entry><structfield>version</structfield></entry>
 178         <entry><type>integer</type></entry>
 179         <entry>Btree version number</entry>
 180        </row>
 181
 182        <row>
 183         <entry><structfield>tree_level</structfield></entry>
 184         <entry><type>integer</type></entry>
 185         <entry>Tree level of the root page</entry>
 186        </row>
 187
 188        <row>
 189         <entry><structfield>index_size</structfield></entry>
 190         <entry><type>bigint</type></entry>
 191         <entry>Total number of pages in index</entry>
 192        </row>
 193
 194        <row>
 195         <entry><structfield>root_block_no</structfield></entry>
 196         <entry><type>bigint</type></entry>
 197         <entry>Location of root block</entry>
 198        </row>
 199
 200        <row>
 201         <entry><structfield>internal_pages</structfield></entry>
 202         <entry><type>bigint</type></entry>
 203         <entry>Number of <quote>internal</> (upper-level) pages</entry>
 204        </row>
 205
 206        <row>
 207         <entry><structfield>leaf_pages</structfield></entry>
 208         <entry><type>bigint</type></entry>
 209         <entry>Number of leaf pages</entry>
 210        </row>
 211
 212        <row>
 213         <entry><structfield>empty_pages</structfield></entry>
 214         <entry><type>bigint</type></entry>
 215         <entry>Number of empty pages</entry>
 216        </row>
 217
 218        <row>
 219         <entry><structfield>deleted_pages</structfield></entry>
 220         <entry><type>bigint</type></entry>
 221         <entry>Number of deleted pages</entry>
 222        </row>
 223
 224        <row>
 225         <entry><structfield>avg_leaf_density</structfield></entry>
 226         <entry><type>float8</type></entry>
 227         <entry>Average density of leaf pages</entry>
 228        </row>
 229
 230        <row>
 231         <entry><structfield>leaf_fragmentation</structfield></entry>
 232         <entry><type>float8</type></entry>
 233         <entry>Leaf page fragmentation</entry>
 234        </row>
 235
 236       </tbody>
 237      </tgroup>
 238     </table>
 239
 240     <para>
 241      As with <function>pgstattuple</>, the results are accumulated
 242      page-by-page, and should not be expected to represent an
 243      instantaneous snapshot of the whole index.
 244     </para>
 245     </listitem>
 246    </varlistentry>
 247
 248    <varlistentry>
 249     <term>
 250      <function>pg_relpages(text) returns bigint</>
 251     </term>
 252
 253     <listitem>
 254      <para>
 255       <function>pg_relpages</function> returns the number of pages in the
 256       relation.
 257      </para>
 258     </listitem>
 259    </varlistentry>
 260   </variablelist>
 261  </sect2>
 262
 263  <sect2>
 264   <title>Authors</title>
 265
 266   <para>
 267    Tatsuo Ishii and Satoshi Nagayasu
 268   </para>
 269  </sect2>
 270
 271 </sect1>