PostgreSQL 14 Bottom-up index tuple deletion 大大减少btree索引膨胀
2021/10/29 19:12:45
本文主要是介绍PostgreSQL 14 Bottom-up index tuple deletion 大大减少btree索引膨胀,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
作者:金涛
准备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大小小的多得多。 升级!升级!升级!
这篇关于PostgreSQL 14 Bottom-up index tuple deletion 大大减少btree索引膨胀的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-01-05快速清空 PostgreSQL 数据库中的所有表格,让你的数据库重新焕然一新!
- 2024-01-04在PostgreSQL中创建角色:判断角色是否存在并创建
- 2023-05-16PostgreSQL一站式插件推荐 -- pg_enterprise_views
- 2022-11-22PostgreSQL 实时位置跟踪
- 2022-11-22如何将PostgreSQL插件移植到openGauss
- 2022-11-11PostgreSQL:修改数据库用户的密码
- 2022-11-06Windows 环境搭建 PostgreSQL 物理复制高可用架构数据库服务
- 2022-10-27Windows 环境搭建 PostgreSQL 逻辑复制高可用架构数据库服务
- 2022-10-11PostgreSql安装(Windows10版本)
- 2022-09-13PostgreSQL-Network Address类型操作和函数