1. 文章
  2. 基础运维

PostgreSQL14新特性：索引方面的增强

osdba · 2021-08-06 15:11:44发表 · 181253 次点击
   0    0   

1. 前言

当前PostgreSQL 14beta2已经发布，中启乘数科技专注于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                                                
  4. -------------------------------------------------------------------------------------------------------------------------------
  5. ---------------------
  6.  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
  7. .742 rows=5 loops=1)
  8.    Index Cond: (p >^ '(0.99999,0.99999)'::point)
  9.    Heap Fetches: 0
  10.  Planning Time: 0.056 ms
  11.  Execution Time: 0.759 ms
  12. (5 rows)
  14. 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
  7. where option can be one of:
  9.     CONCURRENTLY [ boolean ]
  10.     TABLESPACE new_tablespace
  11.     VERBOSE [ boolean ]
  13. URL: https://www.postgresql.org/docs/14/sql-reindex.html

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

8. reindex 命令支持分区表

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

本站文章，未经作者同意，请勿转载，如需转载，请邮件customer@csudata.com.
0 评论  
添加一条新评论

用户登录

个人首页
立即登录