首页
解决方案
数据库专业技术服务全栈式PostgreSQL解决方案Oracle分布式存储化数据库云PolarDB一体化解决方案
产品
CLup:PostgreSQL高可用集群平台 CMiner: PostgreSQL中的CDC CData高性能数据库云一体机 CBackup数据库备份恢复云平台 CPDA高性能双子星数据库机 CSYun超融合虚拟机产品 ZQPool数据库连接池 ConshGuard数据保护产品 APCC: Greenplum管理平台
文档
文章
客户及伙伴
中启开源
关于我们
公司简介 联系我们
中启开源

1. 前言

中启乘数科技专注于PostgreSQL数据库的各项新技术,在这里把PostgreSQL 14在索引方面的7点改进分享给大家。

2. 频繁更新时Btree数减少了膨胀

Btree索引是我们最常用的索引。PostgreSQL 14对Btree索引有很多方便的性能增强:

总之通过上面的这些优化,在一些频繁更新的场景下,Btree减少了膨胀,提升了性能。

3. BRIN 索引支持多区间和布隆过滤器

原先的BRIN索引特别适合边界清晰的堆存储数据, 例如BLOCK 1到8 存储的id范围是100-10000, 9到16 存储的id范围是100001到200000, 检索id=1000时, 只需要扫描1到8号数据块。然而经常可能会在固定的值范围内插入了一个非常大的值,导致BRIN索引失去过滤性。为了解决这个问题, PostgreSQL 14 支持多区间的BRIN,即multi range brin, 1到8号块存储的ID范围可能是1-199, 10000-10019, 20000-20000, 占用5个value(1,199,10000,10019,20000), 一个blocks区间存储多少个value取决于values_per_range参数(8到256).
当不断插入数据时, 这些范围还可以被合并。

见例子:

  1. CREATE TABLE t (a int);
  2. CREATE INDEX ON t USING brin (a int4_minmax_multi_ops(values_per_range=16));

上面的索引子句中int4_minmax_multi_ops(values_per_range=16))就是指定建立的一个多区间的BRIN索引。

更详细的信息可见:

在PostgreSQL 14实现了基于布隆过滤器的BRIN索引,每个连续heap blocks, 存储一个占位bits, 被索引字段的hash value经过bloom hash填充占位bit,创建的方法如下::

  1. CREATE TABLE t (a int);
  2. CREATE INDEX ON t USING brin (a int4_bloom_ops(false_positive_rate = 0.05, n_distinct_per_range = 100));

比较不同类型的BRIN索引的大小:

  1. create index test_brin_idx on bloom_test using brin (id);
  2. create index test_bloom_idx on bloom_test using brin (id uuid_bloom_ops);
  3. create index test_btree_idx on bloom_test (id);

大小统计如下:

Schema Name Type Owner Table Size
public test_bloom_idx index tomas bloom_test 12 MB
public test_brin_idx index tomas bloom_test 832 kB
public test_btree_idx index tomas bloom_test 6016 MB

4. 更快更小的GiST 索引

GiST 索引现在可以在其构建过程中对数据进行预排序,从而可以更快地创建索引并缩小索引。

目前GiST对point类型实现了预排序。这个功能是通过为point_ops类增加了一个sortsupport的函数来实现的。
见下面的例子:

建测试表:

  1. create table x as select point (random(),random()) from generate_series(1,3000000,1);

PostgreSQL 13.3中

  1. postgres=# create index ON x using gist (point );
  2. CREATE INDEX
  3. Time: 49804.780 ms (00:49.805)

大小为:223264768

PostreSQL 14中:

  1. postgres=# create index ON x using gist (point );
  2. CREATE INDEX
  3. Time: 2551.954 ms (00:02.552)

索引的大小为:148955136

可以看到大小从223M减少到148M,创建时间更是直接从49秒减少到2.5秒,性能提升非常明显。

更具体的信息见:

5. SP-GiST 索引也支持覆盖索引(index only scan)

创建测试表和索引:

  1. create table students(p point, addr text, student text);
  2. insert into students select point (random(),random()), seq, seq from generate_series(1,1000000,1) as seq;
  3. create index on students using spgist (p) include(addr,student);
  4. analyze table students;

看执行计划:

  1. postgres=# explain analyze select p,addr,student from students where p >^ '(0.99999,0.99999)'::point;
  2. QUERY PLAN
  3. -------------------------------------------------------------------------------------------------------------------------------
  4. ---------------------
  5. Index Only Scan using students_p_addr_student_idx on students (cost=0.29..4986.28 rows=100000 width=28) (actual time=0.212..0
  6. .742 rows=5 loops=1)
  7. Index Cond: (p >^ '(0.99999,0.99999)'::point)
  8. Heap Fetches: 0
  9. Planning Time: 0.056 ms
  10. Execution Time: 0.759 ms
  11. (5 rows)
  12. Time: 1.077 ms

上面的p >^ '(0.99999,0.99999)'::point是查找在点0.99999,0.99999上面的点。可以看到走到了Index Only Scan

更详细的信息可见:https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=09c1c6ab4bc5764dd69c53ccfd43b2060b1fd090

6. 索引的VACUUM方面上的增强

PostgreSQL 14可以防止长时间创建索引导致VACUUM不能回收垃圾:当create index concurrently时, 只要不是表达式索引, partial index, 不是rc或ssi隔离级别, 那么这个操作的snapshot xmin就不会用做计算oldestxmin,从而它运行多长时间都不会导致vacuum无法回收某些垃圾而导致膨胀,具体可以建:

为了避免每次vacuum都要清理index, PostgreSQL 14进行了优化, 当vacuum一个table时, 如果低于2%的PAGE有dead LP(例如一个表占用了100个page, 如果只有2个page里面有dead LP), 那么VACUUM将跳过索引,并保留这些索引项。当table中的垃圾行(dead lp)积累到超过2% page时, 才需要对索引执行垃圾回收。
因为LP 只占用4字节, 所以不清理也影响不大, 但是大幅降低了因对索引的vacuum导致的vacuum负担。
目前阈值2%是在 代码中写死的, 未来也许会支持索引级别配置, 或者支持GUC配置,更详细的信息见:

7. 可以通过reindex 命令把索引移动到另一个表空间中

REINDEX command 增加 tablespace 选项, 支持通过重建索引的方法把索引移动到另一个表空间中。

REINDEX的语法如下:

  1. postgres=# \h reindex
  2. Command: REINDEX
  3. Description: rebuild indexes
  4. Syntax:
  5. REINDEX [ ( option [, ...] ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } [ CONCURRENTLY ] name
  6. where option can be one of:
  7. CONCURRENTLY [ boolean ]
  8. TABLESPACE new_tablespace
  9. VERBOSE [ boolean ]
  10. URL: https://www.postgresql.org/docs/14/sql-reindex.html

可以看到语法中增加了指定表空间的子句。

8. reindex 命令支持分区表

REINDEX支持分区表, 用这个命令可以自动重建所有子分区的索引,方便了分区表的管理。