PostgreSQL 14 Bottom-up index tuple deletion 大大减少btree索引膨胀

开源软件联盟PostgreSQL分会 2021-10-30

873

作者：金涛

准备PG12和PG14两个环境，主要参数保持一致，并执行如下SQL初始化测试表：

CREATE TABLE testtab (   id        bigint      CONSTRAINT testtab_pkey PRIMARY KEY,   unchanged integer,   changed   integer);INSERT INTO testtab   SELECT i, i, 0   FROM generate_series(1, 10000) AS i;CREATE INDEX testtab_unchanged_idx ON testtab (unchanged);CREATE INDEX testtab_changed_idx ON testtab (changed);vacuum ANALYZE testtab;

PG12查看表索引大小：

postgres=# \dt+ testtab                     List of relations Schema |  Name   | Type  |  Owner   |  Size  | Description --------+---------+-------+----------+--------+------------- public | testtab | table | postgres | 576 kB | (1 row)postgres=# \di+ testtab*                                 List of relations Schema |         Name          | Type  |  Owner   |  Table  |  Size  | Description --------+-----------------------+-------+----------+---------+--------+------------- public | testtab_changed_idx   | index | postgres | testtab | 288 kB |  public | testtab_pkey          | index | postgres | testtab | 288 kB |  public | testtab_unchanged_idx | index | postgres | testtab | 288 kB | (3 rows)

PG14查看表索引大小：

postgres=# \di+ testtab*                                                List of relations Schema |         Name          | Type  |  Owner   |  Table  | Persistence | Access method |  Size  | Description --------+-----------------------+-------+----------+---------+-------------+---------------+--------+------------- public | testtab_changed_idx   | index | postgres | testtab | permanent   | btree         | 128 kB |  public | testtab_pkey          | index | postgres | testtab | permanent   | btree         | 288 kB |  public | testtab_unchanged_idx | index | postgres | testtab | permanent   | btree         | 288 kB | (3 rows)

新建pgbench.sql文件并写入如下SQL:

\set id random_gaussian(1, 10000, 10)UPDATE testtab SET changed = changed + 1 WHERE id = :id;UPDATE testtab SET changed = changed + 1 WHERE id = :id;UPDATE testtab SET changed = changed + 1 WHERE id = :id;UPDATE testtab SET changed = changed + 1 WHERE id = :id;UPDATE testtab SET changed = changed + 1 WHERE id = :id;UPDATE testtab SET changed = changed + 1 WHERE id = :id;UPDATE testtab SET changed = changed + 1 WHERE id = :id;UPDATE testtab SET changed = changed + 1 WHERE id = :id;UPDATE testtab SET changed = changed + 1 WHERE id = :id;UPDATE testtab SET changed = changed + 1 WHERE id = :id;

分别在PG12和PG14运行如下pgbench基准测试：

time pgbench -M prepared -n -c 6 -f pgbench.sql -t 10000  postgres

查看索引及表大小：

PG12

postgres=# \di+ testtab*                                  List of relations Schema |         Name          | Type  |  Owner   |  Table  |  Size   | Description --------+-----------------------+-------+----------+---------+---------+------------- public | testtab_changed_idx   | index | postgres | testtab | 14 MB   |  public | testtab_pkey          | index | postgres | testtab | 1472 kB |  public | testtab_unchanged_idx | index | postgres | testtab | 14 MB   | (3 rows)

PG14

postgres=# \di+ testtab*                                                 List of relations Schema |         Name          | Type  |  Owner   |  Table  | Persistence | Access method |  Size   | Description --------+-----------------------+-------+----------+---------+-------------+---------------+---------+------------- public | testtab_changed_idx   | index | postgres | testtab | permanent   | btree         | 5248 kB |  public | testtab_pkey          | index | postgres | testtab | permanent   | btree         | 288 kB  |  public | testtab_unchanged_idx | index | postgres | testtab | permanent   | btree         | 512 kB  | (3 rows)

可以看到明显PG14中的索引比PG12大小小的多得多。升级！升级！升级！