Home • Explore • Blog • CI
☰



postgresql
3rd chunk of `doc/src/sgml/pgstattuple.sgml`
f518c8c38ee48d7cfe3e8cbfe8407e3be779ddd9d66a5c390000000100000fbc
  <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>
Title: pgstatindex Function: Detailed Output and pgstatginindex Function
Summary
The pgstatindex function provides detailed information about B-tree indexes, returning a record with columns like version, tree level, index size, root block location, number of internal/leaf/empty/deleted pages, average leaf density, and leaf fragmentation. The index_size generally exceeds the sum of page types by one page, which accounts for the index's metapage. Results are accumulated page by page and are not an instantaneous snapshot. A text-based version, pgstatindex(text), exists for backward compatibility but is deprecated. The pgstatginindex function provides information about GIN indexes, including version, pending pages, and pending tuples.