Shulou(Shulou.com)05/31 Report--

This article focuses on "what are the physical storage contents of the B-Tree index in PostgreSQL", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Now let the editor take you to learn "what are the physical storage contents of the B-Tree index in PostgreSQL?"

1. Test data

Create a data table, insert data, and create an index.

Testdb=#-create a table and insert several rows of data testdb=# drop table if exists tweak index; t_index values (16); create an index alter table t_index add constraint pk_t_index primary key (id); DROP TABLEtestdb=# create table t_index (id int,c1 char (8), c2 index (16)); CREATE TABLEtestdb=# insert into t_index values (2) INSERT 0 1testdb=# insert into t_index values, INSERT 0 1testdb=# testdb=#-create index testdb=# alter table t_index add constraint pk_t_index primary key (id), ALTER TABLEtestdb=#-index physical file testdb=# SELECT pg_relation_filepath ('pk_t_index') Pg_relation_filepath-base/16477/26637 (1 row)

Index file raw data

[xdb@localhost utf8db] $hexdump-C base/16477/2663700000000 01 000000 20 5d 0e db 00000000 4000 f01f |. ]. @. | 00000010 f01f 04 20 000000 00 62 31 05 0003 000000 |. B1. | 00000020 01 000000 000 000 000 f0 bf |. | 00000040 000 000 000 00 0000 0000 0000 00 |. | * 00001ff0 0000 0000 0000 08 000000 |. | 00002000 01 0000 00 98 5c 0000 db 0000 0000 28 00 00 b01f |. (. | 00002010 f01f 04 2000 000000e09f 2000 d09f 2000 |. . | 00002020 c09f 2000 b09f 2000 b09f 20 0000 0000 00 |.. . | 00002030 0000 0000 0000 |. | * 00003fb0 0000 0000 04 00 10 00 100 000 0000 0000 0000 00 |. | 00003fc0 0000 0000 03 00 10 00 08 0000 0000 0000 00 |. | 00003fd0 0000 0000 02 00 10 000400 0000 0000 0000 |. | 00003fe0 0000 0000 01 00 10 00 02 0000 0000 000 00 | 00003ff0 0000 0000 0000 2. B-Tree index physical storage

We can view the storage structure of the index through the pageinspect plug-in.

Page 0 is the index metadata page:

Testdb=#-View index header data testdb=# select * from page_header (get_raw_page ('pk_t_index',0)) Lsn | checksum | flags | lower | upper | special | pagesize | version | prune_xid-+-1/DB0E5D20 | 0 | 0 | 64 | 8176 | 8176 | 8192 | 4 | 0 (1 row) testdb=#-View the index metadata page testdb=# select * from bt_metap ('pk_t_index') Magic | version | root | level | fastroot | fastlevel | oldest_xact | last_cleanup_num_tuples-+-340322 | | 3 | 1 | 0 | 1 | 0 | 0 |-1 (1 row) |

Root=1 prompts that the root page is on page 1, and view the header data through page_header:

Testdb=# select * from page_header (get_raw_page ('pk_t_index',1)) Lsn | checksum | flags | lower | upper | special | pagesize | version | prune_xid-+-1/DB0E5C98 | 0 | 0 | 40 | 8112 | 8176 | 8192 | 4 | 0 (1 row)

Each index entries structure is IndexTupleData+Bitmap+Value, with 8 bytes for IndexTupleData, 4 bytes for Bitmap, and 4 bytes for Value, totaling 16 bytes. The data structure is as follows:

/ * Index tuple header structure * * All index tuples start with IndexTupleData. If the HasNulls bit is set, * this is followed by an IndexAttributeBitMapData. The index attribute * values follow, beginning at a MAXALIGN boundary. * * Note that the space allocated for the bitmap does not vary with the number * of attributes; that is because we don't have room to store the number of * attributes in the header. Given the MAXALIGN constraint there's no space * savings to be had anyway, for usual values of INDEX_MAX_KEYS. * / typedef struct IndexTupleData {ItemPointerData t_tid / * reference TID to heap tuple * / / *-* t_info is laid out in the following fashion: * * 15th (high) bit: has nulls * 14th bit: has var-width attributes * 13th bit: AM-defined meaning * 12-0 bit: size of tuple *- * / unsigned short t_info / * various info about tuple * /} IndexTupleData; / * MORE DATA FOLLOWS AT END OF STRUCT * / typedef IndexTupleData * IndexTuple; typedef struct IndexAttributeBitMapData {bits8 bits [(INDEX_MAX_KEYS + 8-1) / 8];} IndexAttributeBitMapData; typedef IndexAttributeBitMapData * IndexAttributeBitMap

View the index entries through the bt_page_items function:

Testdb=# select * from bt_page_items ('pk_t_index',1) Itemoffset | ctid | itemlen | nulls | vars | data-+-1 | (0Magne1) | 16 | f | f | 02 00 00 00 00 2 | (0Jue 2) | 16 | f | f | 04 00 00 00 3 | (0Jue 3) | 16 | f | f | 08 00 00 00 4 | (0Jing 4) | 16 | f | f | 10 00 00 00 (4 rows)

Contents of the corresponding physical index file:

[xdb@localhost utf8db] $hexdump-C base/16477/2663700000000 01 000000 20 5d 0e db 00000000 4000 f01f |. ]. @. | 00000010 f01f 04 20 000000 00 62 31 05 0003 000000 |. B1. | 00000020 01 000000 000 000 000 f0 bf |. | 00000040 000 000 000 00 0000 0000 0000 00 |. |-- above are the header data of the metadata page * 00001ff0 0000 0000 0000 08 000000 98 5c 0e db 0000 0000 28 00 b01f |. (. | 00002010 f01f 04 200000000 E09f 2000 d09f 2000 |. . | 00002020 c09f 2000 b09f 2000 b09f 20 0000 0000 00 |.. . | 00002030 0000 0000 0000 |. |-- above are the header data of index data Page 0 * 00003fb0 0000 0000 04 00 10 00 100 000 0000 0000 000 00 |. | 00003fc0 0000 0000 03 00 10 00 08 0000 0000 0000 00 |. | | 00003fd0 0000 0000 02 00 10 000400 0000 0000 0000 |. | 00003fe0 0000 0000 01 00 10 00 02 0000 0000 0000 00 | 00003ff0 0000 0000 0000- The above is the index data of index data Page 0

ItemPointerData

[xdb@localhost utf8db] $hexdump-C base/16477/26637-s 16304-n 600003fb0 0000 0000 04 00 |. | 00003fb6muri-blockid=\ x0000line offset =\ x0004

T_info

[xdb@localhost utf8db] $hexdump-C base/16477/26637-s 16310-n 200003fb6 100 |. | 00003fb8t indexinfo =\ x0010, that is, 16, indicating that the size of the tuple (index item) is 16 bytes. I believe that you have a deeper understanding of "what is the physical storage content of the B-Tree index in PostgreSQL". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.