PG13 B-tree索引去重

PG13：btree索引去重

正文

PG13一个重要的特性就是Btree索引去重。使得物理文件大小更小，减小IO，帮助提升select性能。

GIN索引，如果不同行的索引键相同，那么会存储一个索引条目。指向多条行（tuple IDs）的指针存储到行记录的posting list中。B-tree相反，需要对于每条行记录都存储一条索引记录。这样有利于维护但是导致很多重复的索引记录。Commit 0d86bbb70引入了B-tree索引去重。只在索引页分裂的时候去重。这些额外的工作被减少页分裂次数和索引大小平衡掉。

不会影响唯一索引？

每次update都会创建一个新的行，每个行版本都需要被索引。因此一个唯一索引也会包含相同索引记录多次。如果update频繁时，也会减小唯一索引膨胀。

优点

减小索引空间大小，帮助节省磁盘空间。更重的是尽可能在RAM中缓存索引，使得扫描索引更快并减小索引膨胀。

升级注意事项

通过pg_upgrade升级，需要执行REDINDEX。通过pg_dumpall及restore或使用逻辑复制重建索引时，自动去重。

设置deduplicate_items = off，使用老的行为。

测试

CREATE TABLE rel (

   aid bigint NOT NULL,

   bid bigint NOT NULL

);

ALTER TABLE rel

   ADD CONSTRAINT rel_pkey PRIMARY KEY (aid, bid);

CREATE INDEX rel_bid_idx ON rel (bid);

INSERT INTO rel (aid, bid)

   SELECT i, i / 10000

   FROM generate_series(1, 20000000) AS i;

/* set hint bits and calculate statistics */

VACUUM (ANALYZE) rel;

这里关注索引rel_bid_idx，查看REINDEX前后的大小。最后执行多次：

DO $$BEGIN

   PERFORM * FROM rel WHERE bid < 100::bigint;

END;$$;

执行索引扫描，打开timing查看执行时间。比较PG12和PG13以及GIN索引。

测试结果

结论

测试结果显示，PG13的索引大小是PG12的1/3左右，仍比GIN索引大。测试中观察到去重后的索引查询时间执行差异更大，这个目前无法解释。

这个特性是B-tree索引的一大进步。

原文

https://www.cybertec-postgresql.com/en/b-tree-index-deduplication/