目录
（一）PG数据库众多开放特性描述
1、开放的数据类型接口
这使得PG支持超级丰富的数据类型，除了传统数据库支持的类型，还支持GS，JSON， RANGE，IP，lSBN，图像特征值，化学，DNA等等扩展的类型，用户还可以根据实际业务扩展更多的类型。
2、开放的操作符接口
这使得PG不仅仅支持常见的类型操作符，还支持扩展的操作符，例如距离符，逻辑并、交、差符号，图像相似符号，几何计算符号等等扩展的符号，用户还可以根据实际业务扩展更多的操作符。
3、开放的外部数据源接口
这使得PG支持丰富的外部数据源，例如可以通过FDW读写MySQL，redis，mongo，oracle，sqlserver，hive，www，hbase，ldap，等等只要你能想到的数据源都可以通过FDW接口读写。
4、开放的语言接口
这使得PG支持几乎地球上所有的编程语言作为数据库的函数、存储过程语言，例如python， plperl，pljaya，plR，PICUDA，plshell等等。用户可以通过language handler扩展PG的语言支持。
5、开放的索引接口
这使得PG支持非常丰富的索引方法，例如btree，hash，gin，gist，sp-gist，brin，bloom，rum，zombodb， bitmap(greenplum extend)，用户可以根据不同的数据类型以及查询的场景，选择不同的索引。
PG内部还支持 BitmapAnd，BitmapOr的优化方法，可以合并多个索引的扫描操作，从而提升多个索引数据访问的效率.
（二）索引结构与生长
1、PG索引结构
（1）meta page和root page（一定有）
meta page只需要一个页来存储，表示指向root page的page id；
（2）leaf page（看情况有）
随着记录数的增加，一个root page可能存不下所有的heap item，此时就会有leaf page，甚至branch page，甚至多层的 branch page；
一共有几层 branch和leaf，可以用 btree page元数据的level来表示。
2、PG索引动态创建、增大过程描述
（1）一旦索引被创建，无论索引中有没有数据，一定会有一个数据块：元数据块M（meta page），这个块就是指向根数据块的指针。
（2）假如一开始，表数据量比较少，索引有数据且一个数据块就足够存放下所有数据，那么这个块L1就会同时存在2种身份：根块（root block）&叶块（leaf block），即既包含根信息，也包含叶信息（叶块存放了实际的数据）。
（3）随着数据量的增大，索引的数据也越来越大，一个块存储不下所以信息时，叶块就会增长（即oracle种的分裂），从一个根叶块L1，变为两个叶块L1、L2，且此时会产生一个新的根块B1，元数据块指向根块的指针从指向原本根叶块L1，变为指向新根块B1。
而新的根块B1中，包含指向两个叶块L1、L2（一个是原本的根叶块，现在变为纯叶块；一个是新分裂的叶块）的指针。这两个叶块，属于第0层。
（4）随着数据继续增大，直到一个根块无法存储下全部的叶块指针时，根块就会分裂，产生新的块B2。此时，原本的根块以及新的块B1就会从原本的根块，变为支块（branch）。
同时索引再次涨高，产生一个新的根块A1。
（5）当数据继续增大，索引会继续裂变、层数也会继续增加。但必须注意的是，随着索引层数的增加，索引查询的效率也会越来越弱，这时，需要进行索引重建或其他优化手段，来进行索引的优化。
3、Btree索引的结构
4、索引工具介绍及使用实验
（1）访问索引结构的基本语句
--部署、安装插件 pageinspect
su - postgres
pg_ctl start
cd /data/package/pg/postgresql-14.8/contrib/pageinspect
make
make install
psql
\c wqdb2
CREATE EXTENSION pageinspect；
--查看meta块
SELECT * FROM bt_metap('tab1_pkey');
--查看root page的stats
SELECT * FROM bt_page_stats('tab1_pkey',1);
--root（leaf）页里面的内容
SELECT * FROM bt_page_items('tab1_pkey',1);
--根据ctid来访问表
SELECT * FROM tab1 WHERE ctid='(0,1)';
（2）实验1：一层结构的索引访问
--0、在测试库中安装插件pageinspect
CREATE EXTENSION pageinspect；
--1、创建测试表、索引、数据（此时只有1层0结构，包括meta page、root page）
CREATE TABLE tab1(id int primary key,info text);
INSERT INTO tab1 SELECT generate_series(1,100),md5(random()::text);
VACUUM ANALYZE tab1;
--2、查看meta块。一层结构下，level=0，root块为1
SELECT * FROM bt_metap('tab1_pkey');
--3、根据root page id=1，查看root page的stats
SELECT * FROM bt_page_stats('tab1_pkey',1);
--此时，
btpo=0，说明处于第0层。
btpo_flags=3，说明它既是leaf又是root项。即(root_page=2)+(leaf_page=1)，合计=3。
--注：
root page：表示btpo_flags=2
branch page：表示btpo_flags=0
leaf page：表示btpo_flags=1
--4、查看root（leaf）页里的内容
SELECT * FROM bt_page_items('tab1_pkey',1); //此时，ctid就是指向表的行id，类似于oracle的rowid，pg中为tid。data就是索引列的值，16进制
--5、根据ctid来访问表
SELECT * FROM tab1 WHERE ctid='(0,1)';
--6、查看表的数据来验证
SELECT * FROM tab1 limit 2；
（3）实验2：二层结构的索引访问
2层（0,1）结构包括：meta page，root page，leaf page
--0、准备工作：继续往表中插入数据，让索引生长
INSERT INTO tab1 SELECT generate_series(101,10000),md5(random()::text);
--1、查看meta数据
SELECT * FROM bt_metap('tab1_pkey'); //root=3，说明root块在第3块
--2、根据root page id=3，查看root page的stats
SELECT * FROM bt_page_stats('tab1_pkey',3);
--3、查看root page存储的leaf page items（指向leaf page）
SELECT * FROM bt_page_items('tab1_pkey',3); //一共有28个叶块（leaf page），data列存储的是这个leaf page的最小值
--4、查看第一个叶块统计
SELECT * FROM bt_page_stats('tab1_pkey',1); //btpo_level=0说明是最底层；btpo_flages=1，即叶块
--5、查看其他叶块统计，当查询到第30块（id=29）时，显示超出块的范围
SELECT * FROM bt_page_stats('tab1_pkey',29);
--6、查看第一个叶块统计，获取ctid
SELECT * FROM bt_page_items('tab1_pkey',1);
--7、根据CTID查看表中的行数据
SELECT * FROM t_btree WHERE ctid='(0,1)';
（4）实验3：三层结构的索引访问
记录数超过1层结构的索引所能够存储的记录数时，会分表为2层结构，除了meta page和root page，还可能包含1层branch page以及1层leaf page。
--0、继续往tab1表插入新数据，使btree增长一层
INSERT INTO tab1 SELECT generate_series(10001,100000),md5(random()::text);
vacuum analyze tab1;
--1、查看meta page
SELECT * FROM bt_metap('tab1_pkey'); //可以看到root page id=412,索引的level=2，即包括1级branch和1级leaf。
--2、根据root page id=412查看root page的stats
SELECT * FROM bt_page_stats('tab1_pkey',412); //btpo_level=2说明当前在第2层，另外还表示下层是1；btpo_flages=2，说明是root page。
--3、查看root page存储的leaf page items（指向branch page）
SELECT * FROM bt_page_items('tab1_pkey',412);
--4、根据branch page id查询stats
SELECT * FROM bt_page_stats('tab1_pkey',3);
--5、查看branch page存储的leaf page ctid（指向leaf page）
SELECT * FROM bt_page_items('tab1_pkey',3); //只要不是最右边的页，第一条都代表右页的起始item。第二条才是当前页的起始ctid；注意所有branch page的起始item对应的data都是空的。也就是说它不存储当前branch page包含的所有leaf pages的索引字段内容的最小值。
--6、根据ctid查看leaf page的统计
SELECT * FROM bt_page_stats('tab1_pkey',1); //btpo=0说明是第0层，即最底层，这里存储的事heap ctid；btpo_flages=1，即叶块（leaf page）第0层叶块，第1层枝块，第2层root块。
--7、查看leaf页的指向表的ctid
SELECT * FROM bt_page_items('tab1_pkey',1);
--8、根据CTID查看表中的行数据
SELECT * FROM tab1 WHERE ctid='(287,1)';
（三）Autovacuum自动维护索引
1、索引案例应用
在做接下来的验证实验之前，我们可以先自己思考一下这个问题：证明Autovacuum是否会自动维护索引？
当你在心里有一个猜想后，再通过查看索引数据块的变化，来验证下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、索引信息
--（1）查看索引元数据
SELECT * FROM bt_metap('tbl_test_id_ind');
--（2）查看索引root根统计
SELECT * FROM bt_page_stats('tbl_test_id_ind',3);
--（3）查看索引叶块内容（此时没有发生数据更新）
SELECT * FROM bt_page_items('tbl_test_id_ind',1);
--3、更新表数据
UPDATE tbl_test SET info=md5(random()::text) WHERE id<20060;
--4、查看索引叶块的内容变化
SELECT * FROM bt_page_items('tbl_test_id_ind',1);
观察实验结果可以发现，在update一段时间后，索引块的信息又更新了，也就是说原来的索引行被删除。这说明Autovacuum会自动维护索引信息。你是否猜对了呢。
2、索引维护
--重建索引
reindex index tbl_test_id_ind;
--查看reindex后，索引的relfilenode会发生变化
select relname,oid,relfilenode from pg_class where relname='tbl_test_id_ind';
（四）btree、hash索引应用场景
1、btree索引应用场景
postgresql的b-tree是一种变种（高并发b树管理算法），其应用场景有：
（1）btree适合所有的数据类型，支持排序，支持大于，小于，等于，大于或等于，小于或等于的搜索。
（2）索引与递归查询结合，还能实现快速的稀疏检索。
使用b-tree示例：
--1、环境搭建
create table tbl_btree(id int, info text);
insert into tbl_btree select generate_series(1,10000),md5(random()::text);
create index idx_tbl_hash_1 on tbl_hash using hash (info);
--2、查看执行计划
explain (analyze,verbose,timing,costs,buffers) select * from tbl_btree where id=1;
2、hash索引应用场景
（1）hash索引结构
哈希索引项只存储每个索引项的哈希代码，而不是实际的数据值
（2）应用场景
- hash索引存储的是被索引字段value的哈希值，只支持等值查询。
- hash索引特别适用于字段value非常长（不适合b-tree索引，因为b-tree一个page至少要存储3个索引行，所以不支持特别长的value)的场景，例如很长的字符串，并且用户只需要等值搜索，建议使用hash index。
（3）示例
--1、环境搭建
create table tbl_hash(id int, info text);
insert into tbl_hash select generate_series(1,100),repeat(md5(random()::text),10000);
create index idx_tbl_hash_1 on tbl_hash using btree (info); //此时使用b-tree索引会报错，因为长度超过了1/3索引页的大小
create index idx_tbl_hash_1 on tbl_hash using hash (info); 创建hash索引可以成功
--2、查看执行计划
explain (analyze,verbose,timing,costs,buffers) select * from tbl_hash where info in (select info from tbl_hash limit 1)
