Home Explore Blog CI



postgresql

5th chunk of `doc/src/sgml/pageinspect.sgml`
4a3e1fa5d76da635ef33b24137d17bcb1397646fd59c3d9d0000000100000fa2

      from <parameter>blkno</parameter> to the end of the index are reported
      on.  For example:
<screen>
test=# SELECT * FROM bt_multi_page_stats('pg_proc_oid_index', 5, 2);
-[ RECORD 1 ]-+-----
blkno         | 5
type          | l
live_items    | 367
dead_items    | 0
avg_item_size | 16
page_size     | 8192
free_size     | 808
btpo_prev     | 4
btpo_next     | 6
btpo_level    | 0
btpo_flags    | 1
-[ RECORD 2 ]-+-----
blkno         | 6
type          | l
live_items    | 367
dead_items    | 0
avg_item_size | 16
page_size     | 8192
free_size     | 808
btpo_prev     | 5
btpo_next     | 7
btpo_level    | 0
btpo_flags    | 1
</screen>
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term>
     <function>bt_page_items(relname text, blkno bigint) returns setof record</function>
     <indexterm>
      <primary>bt_page_items</primary>
     </indexterm>
    </term>

    <listitem>
     <para>
      <function>bt_page_items</function> returns detailed information about
      all of the items on a B-tree index page.  For example:
<screen>
test=# SELECT itemoffset, ctid, itemlen, nulls, vars, data, dead, htid, tids[0:2] AS some_tids
        FROM bt_page_items('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 | f    | (1,2)  | {"(1,2)","(1,19)"}
          6 | (16,8292) |     616 | f     | f    | 28 00 00 00 00 00 00 00 | f    | (2,24) | {"(2,24)","(4,11)"}
          7 | (16,8292) |     616 | f     | f    | 29 00 00 00 00 00 00 00 | f    | (2,17) | {"(2,17)","(11,2)"}
          8 | (16,8292) |     616 | f     | f    | 2a 00 00 00 00 00 00 00 | f    | (0,25) | {"(0,25)","(3,20)"}
          9 | (16,8292) |     616 | f     | f    | 2b 00 00 00 00 00 00 00 | f    | (0,10) | {"(0,10)","(0,14)"}
         10 | (16,8292) |     616 | f     | f    | 2c 00 00 00 00 00 00 00 | f    | (1,3)  | {"(1,3)","(3,9)"}
         11 | (16,8292) |     616 | f     | f    | 2d 00 00 00 00 00 00 00 | f    | (6,28) | {"(6,28)","(11,1)"}
         12 | (16,8292) |     616 | f     | f    | 2e 00 00 00 00 00 00 00 | f    | (0,27) | {"(0,27)","(1,13)"}
         13 | (16,8292) |     616 | f     | f    | 2f 00 00 00 00 00 00 00 | f    | (4,17) | {"(4,17)","(4,21)"}
(13 rows)
</screen>
      This is a B-tree leaf page.  All tuples that point to the table
      happen to be posting 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

Title: B-Tree Page Item Inspection
Summary
The bt_page_items function returns detailed information about all items on a B-tree index page, including item offset, CTID, item length, nulls, variables, data, and TIDs, allowing for inspection of leaf and internal pages, including posting list tuples and downlinks.