PostgreSQL btree索引原理和应用场景

PG数据库众多开放特性概述

• 开放的数据类型接口，使得PG支持超级丰富的数据类型，除了传统数据库支持的类型，
  还支持GIS，JSON，RANGE，IP，ISBN，图像特征值，化学，DNA等等扩展的类型，用
  户还可以根据实际业务扩展更多的类型。
• 开放的操作符接口，使得PG不仅仅支持常见的类型操作符，还支持扩展的操作符，例
  如 距离符，逻辑并、交、差符号，图像相似符号，几何计算符号等等扩展的符号，
  用户还可以根据实际业务扩展更多的操作符。
• 开放的外部数据源接口，使得PG支持丰富的外部数据源，例如可以通过FDW读写
  MySQL, redis, mongo, oracle, sqlserver, hive, www, hbase, ldap, 等等只要你能想到的数据
  源都可以通过FDW接口读写。
• 开放的语言接口，使得PG支持几乎地球上所有的编程语言作为数据库的函数、存储过
  程语言，例如plpython , plperl , pljava , plR , plCUDA , plshell等等。用户可以通过
  language handler扩展PG的语言支持。
• 开放的索引接口，使得PG支持非常丰富的索引方法，例如btree , hash , gin , gist , sp-gist ,
  brin , bloom , rum , zombodb , bitmap (greenplum extend)，用户可以根据不同的数据类型，
  以及查询的场景，选择不同的索引。
• PG内部还支持BitmapAnd, BitmapOr的优化方法，可以合并多个索引的扫描操作，从而
  提升多个索引数据访问的效率。

btree索引原理和应用场景

PostgreSQL索引结构：

• meta page和root page是一定有的，meta page需要一个页来存储，表示指向root
  page的page id。
• 随着记录数的增加，一个root page可能存不下所有的heap item，就会有leaf page，
  甚至branch page，甚至多层的branch page。
• 一共有几层branch 和 leaf，可以用btree page元数据的 level 来表示。
  

索引工具介绍

如何访问索引结构
1、create extension pageinspect
2、查看meta块 select * from bt_metap(‘tab1_pkey’);
3、查看root page的stats select * from bt_page_stats(‘tab1_pkey’,1);
4、查看root（leaf）页里面的内容： select * from bt_page_items(‘tab1_pkey’,1);
5、根据ctid来访问表： select * from tab1 where ctid=’(0,1)’;

第一种情况

1、环境准备

postgres=# create extension pageinspect; 
postgres=# create table tab1(id int primary key, info text); 
CREATE TABLE 
postgres=# insert into tab1 select generate_series(1,100), md5(random()::text); 
INSERT 0 100 
postgres=# vacuum analyze tab1; 
VACUUM

第一种情况：
有1层（0）结构，包括meta page, root page
2、查看meta块

此时level 0，root块为1。
3、根据root page id = 1，查看root page的stats
ndx=# select * from bt_page_stats('tab1_pkey',1);

此时：btpo=0，说明处于第0层。
btpo_flags=3，说明它既是leaf又是root页。即：root_page（2）+leaf_page（1）=3
注：
meta page
root page ：表示为btpo_flags=2
branch page ：表示为btpo_flags=0
leaf page ：表示为btpo_flags=1
4、查看root（leaf）页里面的内容
indx=# select * from bt_page_items('tab1_pkey',1);

此时ctid就是指向表的行id，类似于oracle的rowid，PG中为tid。data就是索引列的值，16进制。

5、根据ctid来访问表
indx=# select * from tab1 where ctid='(0,1)';

6、查看表的数据来验证
indx=# select * from tab1 limit 2;

第二种情况

有2层（0，1）结构，包括meta page, root page, leaf page.

create table t_btree(id int, info text); 
insert into t_btree select generate_series(1,10000), md5(random()::text) ; 
create index idx_t_btree_1 on t_btree using btree (id);

1、查看meta数据
indx=# select * from bt_metap(‘idx_t_btree_1’);

root块在第3块
2、根据root page id 查看root page的stats
indx=# select * from bt_page_stats(‘idx_t_btree_1’,3);

3、查看root page存储的 leaf page items (指向leaf page)
indx=# select * from bt_page_items(‘idx_t_btree_1’,3);

一共28个叶块。data存储的是这个leaf page存储的最小值。
4、查看第一个叶块统计
indx=# select * from bt_page_stats(‘idx_t_btree_1’,1);

btpo=0，说明是最底层，btpo_flags=1，即叶块。
5、查看其它叶块统计，当查询到第30块时，显示超出块的范围
indx=# select * from bt_page_stats(‘idx_t_btree_1’,29);

6、查看第一个叶块的内容
indx=# select * from bt_page_items(‘idx_t_btree_1’,1);

7、根据CTID查看表中的行数据
select * from t_btree where ctid=’(0,1)’;

第三种

记录数超过1层结构的索引所能够存储的记录数时，会分裂为2层结构，除了meta page和root
page，还可能包含1层branch page以及1层leaf page。
1、建表

create table tab2 (id int primary key, info text); 
postgres=# insert into tab2 select trunc(random()*10000000), md5(random()::text) from 
generate_series(1,1000000) on conflict on constraint tab2_pkey do nothing; 
INSERT 0 951379 
postgres=# vacuum analyze tab2;

2、查看meta page，可以看到root page id = 412, 索引的level=2，即包括1级 branch 和 1级 leaf。
postgres=# select * from bt_metap(‘tab2_pkey’);

3、根据root page id 查看root page的stats
indx=# select * from bt_page_stats(‘tab2_pkey’, 412);

btpo = 2 当前在第二层，另外还表示下层是1。
btpo_flags = 2 说明是root page
4、查看root page存储的 branch page items (指向branch page)
postgres=# select * from bt_page_items(‘tab2_pkey’, 412);

5、根据branch page id查看stats
indx=# select * from bt_page_stats(‘tab2_pkey’, 3);

6、查看branch page存储的 leaf page ctid (指向leaf page)
indx=# indx=# select * from bt_page_items(‘tab2_pkey’, 3);

6、查看branch page存储的 leaf page ctid (指向leaf page)
indx=# indx=# select * from bt_page_items(‘tab2_pkey’, 3);

只要不是最右边的页，第一条都代表右页的起始item。
第二条才是当前页的起始ctid
注意所有branch page的起始item对应的data都是空的。
也就是说它不存储当前branch page包含的所有leaf pages的索引字段内容的最小值。
7、根据ctid 查看leaf page的统计
indx=# select * from bt_page_stats(‘tab2_pkey’, 1);

btpo = 0 当前在第0层，即最底层，这里存储的是heap ctid
btpo_flags = 1 说明是leaf page
第0层叶块，第1层枝块，第2层root块。
8、查看leaf页的指向表的ctid
indx=# select * from bt_page_items(‘tab2_pkey’, 1);

9、通过ctid查看表的数据
indx=# select * from tab2 where ctid=’(1748,1)’;

索引案例应用

利用查看索引数据块的变化，去证明Autovacuum是否会维护索引。
1、环境搭建

create table tbl_test (id int, info text, c_time timestamp);
insert into tbl_test select generate_series(1,100000),md5(random()::text),clock_timestamp();
create index tbl_test_id_ind on tbl_test (id);

2、索引信息
–查看索引元数据
select * from bt_metap(‘tbl_test_id_ind’);
–查看索引root根统计
select * from bt_page_stats(‘tbl_test_id_ind’,3);
–查看索引叶块内容（此时没有发生数据更新）
indx=# select * from bt_page_items(‘tbl_test_id_ind’,1);

3、更新表数据，导致autovacuum触发
update tbl_test set info=md5(random()::text) where id < 20060;
4、查看索引叶块的内容变化（autovacuum前）
indx=# select * from bt_page_items(‘tbl_test_id_ind’,1);

5、查看索引叶块的内容变化（autovacuum后）
indx=# select * from bt_page_items(‘tbl_test_id_ind’,1);

观察后发现索引块的信息更新了，原来的索引行被删除。说明autovacuum会自动维护索引信息。