中启乘数科技专注于PostgreSQL数据库的各项新技术,在这里把PostgreSQL 14在索引方面的7点改进分享给大家。
Btree索引是我们最常用的索引。PostgreSQL 14对Btree索引有很多方便的性能增强:
总之通过上面的这些优化,在一些频繁更新的场景下,Btree减少了膨胀,提升了性能。
原先的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).
当不断插入数据时, 这些范围还可以被合并。
见例子:
CREATE TABLE t (a int);
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,创建的方法如下::
CREATE TABLE t (a int);
CREATE INDEX ON t USING brin (a int4_bloom_ops(false_positive_rate = 0.05, n_distinct_per_range = 100));
比较不同类型的BRIN索引的大小:
create index test_brin_idx on bloom_test using brin (id);
create index test_bloom_idx on bloom_test using brin (id uuid_bloom_ops);
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 |
GiST 索引现在可以在其构建过程中对数据进行预排序,从而可以更快地创建索引并缩小索引。
目前GiST对point类型实现了预排序。这个功能是通过为point_ops类增加了一个sortsupport的函数来实现的。
见下面的例子:
建测试表:
create table x as select point (random(),random()) from generate_series(1,3000000,1);
PostgreSQL 13.3中
postgres=# create index ON x using gist (point );
CREATE INDEX
Time: 49804.780 ms (00:49.805)
大小为:223264768
PostreSQL 14中:
postgres=# create index ON x using gist (point );
CREATE INDEX
Time: 2551.954 ms (00:02.552)
索引的大小为:148955136
可以看到大小从223M减少到148M,创建时间更是直接从49秒减少到2.5秒,性能提升非常明显。
更具体的信息见:
创建测试表和索引:
create table students(p point, addr text, student text);
insert into students select point (random(),random()), seq, seq from generate_series(1,1000000,1) as seq;
create index on students using spgist (p) include(addr,student);
analyze table students;
看执行计划:
postgres=# explain analyze select p,addr,student from students where p >^ '(0.99999,0.99999)'::point;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
---------------------
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
.742 rows=5 loops=1)
Index Cond: (p >^ '(0.99999,0.99999)'::point)
Heap Fetches: 0
Planning Time: 0.056 ms
Execution Time: 0.759 ms
(5 rows)
Time: 1.077 ms
上面的p >^ '(0.99999,0.99999)'::point
是查找在点0.99999,0.99999
上面的点。可以看到走到了Index Only Scan
。
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配置,更详细的信息见:
REINDEX command
增加 tablespace 选项, 支持通过重建索引的方法把索引移动到另一个表空间中。
REINDEX的语法如下:
postgres=# \h reindex
Command: REINDEX
Description: rebuild indexes
Syntax:
REINDEX [ ( option [, ...] ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } [ CONCURRENTLY ] name
where option can be one of:
CONCURRENTLY [ boolean ]
TABLESPACE new_tablespace
VERBOSE [ boolean ]
URL: https://www.postgresql.org/docs/14/sql-reindex.html
可以看到语法中增加了指定表空间的子句。
REINDEX
支持分区表, 用这个命令可以自动重建所有子分区的索引,方便了分区表的管理。