postgresql-BTREE索引如何查看
BTREE索引如何查看
学习德哥btree笔记
PostgreSQL 的B-Tree索引页分为几种类别(用bt_metap看层级，bt_metap看属于那种page，bt_page_items查看具体存储情况)
meta page
root page # btpo_flags=2
branch page # btpo_flags=0
leaf page # btpo_flags=1
如果即是leaf又是root则 btpo_flags=3。
如果表的记录数在一个索引page能存下（约285条），索引就只需要meta page和root page。branch和leaf page不需要
层次可以从bt_page_stats的btpo得到，代表当前index page所处的层
用到的函数
select * from bt_metap('ind_tt_id');
select * from bt_page_items('ind_tt_id',3);
select * from bt_page_stats('ind_tt_id',1);
blkno | type | live_items | dead_items | avg_item_size | page_size | free_size | btpo_prev | btpo_next | btpo | btpo_flags
-------+------+------------+------------+---------------+-----------+-----------+-----------+-----------+------+------------
1 | l | 100 | 0 | 16 | 8192 | 6148 | 0 | 0 | 0 | 3
btpo_prev和btpo_next分别表示该页的相邻页（branch page是双向链表）。
4个层级的具体分析
0层结构只有meta和root页
1层结构包括meta page, root page, leaf page.
2层结构
记录数超过1层结构的索引可以存储的记录数时，会分裂为2层结构，除了meta page和root page，还可能包含1层branch page以及1层leaf page。
如果是边界页(branch or leaf)，那么其中一个方向没有PAGE，这个方向的链表信息都统一指向meta page。
多层结构，除了meta page，还可能包含多层branch page，以及一层leaf page。
0级结构最多记录285条记录
1级结构最多记录285^2条记录
2级结构最多记录285^3条记录
- 建表（tab0，tab1，tab2，tab3）
postgres=# create extension pageinspect;
postgres=# create table tab0(id int primary key, info text);
CREATE TABLE
postgres=# insert into tab0 select generate_series(1,100), md5(random()::text);
INSERT 0 100
postgres=# vacuum analyze tab1;
VACUUM
postgres=# truncate tab1;
TRUNCATE TABLE
postgres=# insert into tab1 select generate_series(1,1000), md5(random()::text);
INSERT 0 1000
postgres=# vacuum analyze tab1;
VACUUM
create table tbl2(id int primary key, info text);
postgres=# select 285^2;
?column?
----------
81225
(1 row)
postgres=# insert into tbl2 select trunc(random()*10000000), md5(random()::text) from generate_series(1,1000000) on conflict on constraint tbl2_pkey do nothing;
INSERT 0 951713
postgres=# vacuum analyze tbl2;
VACUUM
postgres=# create table tab3(id int primary key, info text);
CREATE TABLE
postgres=# insert into tab3 select generate_series(1, 100000000), md5(random()::text);
-
root =1,level=0:说明没有branch和leaf page
-
查看meta page，可以看到root page id = 3, 索引的level = 1。
level = 1 表示包含了leaf page。
-
查看meta page，可以看到root page id = 412, 索引的level=2，即包括1级 branch 和 1级 leaf。
-
level=3,已经是多层结构了
postgres=# select * from bt_metap('tab0_pkey');
magic | version | root | level | fastroot | fastlevel
--------+---------+------+-------+----------+-----------
340322 | 2 | 1 | 0 | 1 | 0
(1 row)
postgres=# select * from bt_metap('tab1_pkey');
magic | version | root | level | fastroot | fastlevel
--------+---------+------+-------+----------+-----------
340322 | 2 | 3 | 1 | 3 | 1
(1 row)
postgres=# select * from bt_metap('tab2_pkey');
magic | version | root | level | fastroot | fastlevel
--------+---------+------+-------+----------+-----------
340322 | 2 | 412 | 2 | 412 | 2
(1 row)
postgres=# select * from bt_metap('tab3_pkey');
magic | version | root | level | fastroot | fastlevel
--------+---------+--------+-------+----------+-----------
340322 | 2 | 116816 | 3 | 116816 | 3
(1 row)
-
查看root page的stats
btpo=0 说明已经到了最底层
btpo_flags=3，说明它既是leaf又是root页。
-
btpo = 1 说明还没有到最底层（最底层btpo=0, 这种页里面存储的ctid才代表指向heap page的地址）
btpo_flags=2 说明这个页是root page
postgres=# select * from bt_page_stats('tab0_pkey',1);
blkno | type | live_items | dead_items | avg_item_size | page_size | free_size | btpo_prev | btpo_next | btpo | btpo_flags
-------+------+------------+------------+---------------+-----------+-----------+-----------+-----------+------+------------
1 | l | 100 | 0 | 16 | 8192 | 6148 | 0 | 0 | 0 | 3
(1 row)
postgres=# select * from bt_page_stats('tab1_pkey',3);
blkno | type | live_items | dead_items | avg_item_size | page_size | free_size | btpo_prev | btpo_next | btpo | btpo_flags
-------+------+------------+------------+---------------+-----------+-----------+-----------+-----------+------+------------
3 | r | 3 | 0 | 13 | 8192 | 8096 | 0 | 0 | 1 | 2
(1 row)
postgres=# select * from bt_page_stats('tab1_pkey',0);
ERROR: block 0 is a meta page
postgres=# select * from bt_page_stats('tab1_pkey',1);
blkno | type | live_items | dead_items | avg_item_size | page_size | free_size | btpo_prev | btpo_next | btpo | btpo_flags
-------+------+------------+------------+---------------+-----------+-----------+-----------+-----------+------+------------
1 | l | 367 | 0 | 16 | 8192 | 808 | 0 | 2 | 0 | 1
(1 row)
postgres=# select * from bt_page_stats('tab1_pkey',2);
blkno | type | live_items | dead_items | avg_item_size | page_size | free_size | btpo_prev | btpo_next | btpo | btpo_flags
-------+------+------------+------------+---------------+-----------+-----------+-----------+-----------+------+------------
2 | l | 367 | 0 | 16 | 8192 | 808 | 1 | 4 | 0 | 1
(1 row)
postgres=# select * from bt_page_stats('tab1_pkey',3);
blkno | type | live_items | dead_items | avg_item_size | page_size | free_size | btpo_prev | btpo_next | btpo | btpo_flags
-------+------+------------+------------+---------------+-----------+-----------+-----------+-----------+------+------------
3 | r | 3 | 0 | 13 | 8192 | 8096 | 0 | 0 | 1 | 2
(1 row)
postgres=# select * from bt_page_stats('tab1_pkey',4);
blkno | type | live_items | dead_items | avg_item_size | page_size | free_size | btpo_prev | btpo_next | btpo | btpo_flags
-------+------+------------+------------+---------------+-----------+-----------+-----------+-----------+------+------------
4 | l | 268 | 0 | 16 | 8192 | 2788 | 2 | 0 | 0 | 1
(1 row)
查看具体的存储情况
postgres=# select * from bt_page_items('tab1_pkey',3) order by ctid;
itemoffset | ctid | itemlen | nulls | vars | data
------------+-------+---------+-------+------+-------------------------
1 | (1,0) | 8 | f | f |
2 | (2,1) | 16 | f | f | 6f 01 00 00 00 00 00 00
3 | (4,1) | 16 | f | f | dd 02 00 00 00 00 00 00
(3 rows)
postgres=# select * from bt_page_items('tab1_pkey',1) order by ctid limit 1;
itemoffset | ctid | itemlen | nulls | vars | data
------------+-------+---------+-------+------+-------------------------
2 | (0,1) | 16 | f | f | 01 00 00 00 00 00 00 00
(1 row)
postgres=# select * from bt_page_items('tab1_pkey',1) order by ctid desc limit 1;
itemoffset | ctid | itemlen | nulls | vars | data
------------+-------+---------+-------+------+-------------------------
367 | (3,6) | 16 | f | f | 6e 01 00 00 00 00 00 00
(1 row)
postgres=# select * from bt_page_items('tab1_pkey',2) order by ctid limit 1;
itemoffset | ctid | itemlen | nulls | vars | data
------------+-------+---------+-------+------+-------------------------
2 | (3,7) | 16 | f | f | 6f 01 00 00 00 00 00 00
(1 row)
postgres=# select * from bt_page_items('tab1_pkey',2) order by ctid desc limit 1;
itemoffset | ctid | itemlen | nulls | vars | data
------------+--------+---------+-------+------+-------------------------
367 | (6,12) | 16 | f | f | dc 02 00 00 00 00 00 00
(1 row)
postgres=# select * from bt_page_items('tab1_pkey',4) order by ctid limit 1;
itemoffset | ctid | itemlen | nulls | vars | data
------------+--------+---------+-------+------+-------------------------
1 | (6,13) | 16 | f | f | dd 02 00 00 00 00 00 00
(1 row)
postgres=# select * from bt_page_items('tab1_pkey',4) order by ctid desc limit 1;
itemoffset | ctid | itemlen | nulls | vars | data
------------+--------+---------+-------+------+-------------------------
268 | (8,40) | 16 | f | f | e8 03 00 00 00 00 00 00
(1 row)
本文来自博客园，作者：{dyy}，转载请注明原文链接：{https://www.cnblogs.com/ddlearning/}
浙公网安备 33010602011771号