Home Explore Blog CI



postgresql

6th chunk of `doc/src/sgml/pageinspect.sgml`
769e02bc1d1752ba41d0953d30236696594e28d79e7cd0a10000000100000fa2
 list tuples (all of which store a total of
      100 6 byte TIDs).  There is also a <quote>high key</quote> tuple
      at <literal>itemoffset</literal> number 1.
      <structfield>ctid</structfield> is used to store encoded
      information about each tuple in this example, though leaf page
      tuples often store a heap TID directly in the
      <structfield>ctid</structfield> field instead.
      <structfield>tids</structfield> is the list of TIDs stored as a
      posting list.
     </para>
     <para>
      In an internal page (not shown), the block number part of
      <structfield>ctid</structfield> is a <quote>downlink</quote>,
      which is a block number of another page in the index itself.
      The offset part (the second number) of
      <structfield>ctid</structfield> stores encoded information about
      the tuple, such as the number of columns present (suffix
      truncation may have removed unneeded suffix columns).  Truncated
      columns are treated as having the value <quote>minus
       infinity</quote>.
     </para>
     <para>
      <structfield>htid</structfield> shows a heap TID for the tuple,
      regardless of the underlying tuple representation.  This value
      may match <structfield>ctid</structfield>, or may be decoded
      from the alternative representations used by posting list tuples
      and tuples from internal pages.  Tuples in internal pages
      usually have the implementation level heap TID column truncated
      away, which is represented as a NULL
      <structfield>htid</structfield> value.
     </para>
     <para>
      Note that the first item on any non-rightmost page (any page with
      a non-zero value in the <structfield>btpo_next</structfield> field) is the
      page's <quote>high key</quote>, meaning its <structfield>data</structfield>
      serves as an upper bound on all items appearing on the page, while
      its <structfield>ctid</structfield> field does not point to
      another block.  Also, on internal pages, the first real data
      item (the first item that is not a high key) reliably has every
      column truncated away, leaving no actual value in its
      <structfield>data</structfield> field.  Such an item does have a
      valid downlink in its <structfield>ctid</structfield> field,
      however.
     </para>
     <para>
      For more details about the structure of B-tree indexes, see
      <xref linkend="btree-structure"/>.  For more details about
      deduplication and posting lists, see <xref
       linkend="btree-deduplication"/>.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term>
     <function>bt_page_items(page bytea) returns setof record</function>
     <indexterm>
      <primary>bt_page_items</primary>
     </indexterm>
    </term>

    <listitem>
     <para>
      It is also possible to pass a page to <function>bt_page_items</function>
      as a <type>bytea</type> value.  A page image obtained
      with <function>get_raw_page</function> should be passed as argument.  So
      the last example could also be rewritten like this:
<screen>
test=# SELECT itemoffset, ctid, itemlen, nulls, vars, data, dead, htid, tids[0:2] AS some_tids
        FROM bt_page_items(get_raw_page('tenk2_hundred', 5));
 itemoffset |   ctid    | itemlen | nulls | vars |          data           | dead |  htid  |      some_tids
------------+-----------+---------+-------+------+-------------------------+------+--------+---------------------
          1 | (16,1)    |      16 | f     | f    | 30 00 00 00 00 00 00 00 |      |        |
          2 | (16,8292) |     616 | f     | f    | 24 00 00 00 00 00 00 00 | f    | (1,6)  | {"(1,6)","(10,22)"}
          3 | (16,8292) |     616 | f     | f    | 25 00 00 00 00 00 00 00 | f    | (1,18) | {"(1,18)","(4,22)"}
          4 | (16,8292) |     616 | f     | f    | 26 00 00 00 00 00 00 00 | f    | (4,18) | {"(4,18)","(6,17)"}
          5 | (16,8292) |     616 | f     | f    | 27 00 00 00 00 00 00 00

Title: B-Tree Page Item Details
Summary
The bt_page_items function provides detailed information about B-tree index pages, including item offset, CTID, item length, nulls, variables, data, and TIDs, with additional details on high keys, downlinks, and heap TIDs, and can be used with page images obtained from get_raw_page.