PostgreSQL 13Beta3：B树索引重复数据消除

PostgreSQL 13开发进展顺利，Postgres 13 Beta3于2020年8月13日发布。Postgres Beta 1和2版本分别于2020年5月和6月发布。我对Postgres 13感兴趣的功能之一是B-Tree重复数据删除功能。B-Tree索引是Postgres中的默认索引方法，也可能是生产环境中最常用的索引。对这部分数据库的任何改进都可能带来广泛的好处。从索引中删除重复项可使其物理大小更小，减少I/O开销，并有助于保持SELECT查询的速度！

在Laurenz Albe的帖子中，有一个很好的总结，从2020年6月初开始，大概使用了Pg13 Beta1，对这种改进的内容和方式进行了很好的总结。哈米德·阿赫塔尔(Hamid Akhtar)在7月份的帖子中报道了使用PG13Beta2的这一功能，以及一种不同的方法，包括使用EXPLAIN查看性能。这篇帖子再次介绍了使用PG13Beta3的这一改进。我打算看看这一改进在我在生产中使用的数据集上效果如何。对于这项任务，我的首选是使用osm2pgsql将OpenStreetMap数据加载到Postgres/PostGIS。

第一步是在一台Ubuntu18主机上安装两个版本的Postgres(12和13beta3)。在过去，当我测试生产前版本时，我会从源代码构建postgres，而不是使用aps，这次我决定使用apt install，所以我将包含这方面的基本过程。

建议安装PostgreSQL的方式是从pgdg(PostgreSQL Global Development Group)存储库中安装，有关更多信息，请参阅Postgres维基。要启用测试版，/etc/apt/Soures.list.d/pgdg.list中需要的行为：

Sudo apt更新#postgres 12sudo apt安装PostgreSQL-12 PostgreSQL-12-postgis-3#postgres 13(当前为Beta版)sudo apt安装PostgreSQL-13 postgreSQL-13-postgis-3。

在Ubuntu上，安装多个版本将创建在不同端口上运行的多个实例。我用来写这篇文章的测试服务器目前安装了三个版本的Postgres，目前只有两个在运行。Postgres 12是第一个安装的，因此获得了默认端口5432。Postgres 11第二个安装，分配给5433，PG13 beta 3最后安装，分配端口5434。在Debian/Ubuntu主机上，pg_lscluster作为pg_ctl包装器的一部分可用。

Sudo-u postgres pg_lsclustersVer群集端口状态所有者数据目录日志文件11 main 5433 down postgres/var/lib/postgresql/11/main/var/log/postgreSQL/postgresql-11-main.log12 main 5432 online postgres/var/lib/postgreSQL/12/main/var/log/postgresql/postgress12-main.log13 main 5434 online postgres/var/lib/postgreSQL/12/main/var/log/postgresql/postgresql/postgresql-12-main.log13 main 5434 online postgres/var/lib/postgreSQL/12/main/。

当使用已安装的多个版本时，验证版本是否与您期望的匹配是很有帮助的。首先，Postgres 12版本的端口5432。

Psql-d pgom-p5432-c"；选择版本()；"；┌────┐│版本│╞══。X86_64-PC-linux-gnu上的══╡│PostgreSQL12.4(Ubuntu12.4-1.pgdg18.04+1)，编译的…。││…。GCC(Ubuntu7.5.0-3ubuntu1~18.04)7.5.0，64位│└────┘。

Psql-d pgom-p5434-c"；选择版本()；"；┌────┐│版本│╞══。X86_64-PC-linux-gnu上的══╡│PostgreSQL13beta3(Ubuntu13~beta3-1.pgdg18.04+1)，C…。││…。编译：GCC(Ubuntu7.5.0-3ubuntu1~18.04)7.5.0，64位│└────┘。

两个版本的Postgres都加载了使用osm2pgsql加载的相同的Colorado OpenStreetMapdata。osm2pgsql本身不创建任何B-Tree索引，只创建几何上的GIST索引。对于本帖子，我们检查在四个列(osm_id、Highway、water way和Natural)上创建的B-Tree索引大小。查看公共数据。Planet_OSM_LINE表我可以猜测我们愿意和不愿意在哪里创建B-Tree索引。自然和水道)具有少量不同的值和不同数量的空值。这三列都是部分索引的候选列，以避免索引NULL值，从而减小创建的索引的大小。我希望在Postgres 13中看到这些列的好处，可能会降低部分索引的使用频率。

从pg_Catalog.pg_stats中选择attname，n_DISTINCT，NULL_FRAC，其中tablename='；Planet_OSM_LINE'；和attname in('；osm_id'；，'；高速公路'；，'；水道'；，'；Natural'；)；┌─┬─┬─┐│Attname│n_DISTINCT│NULL_FRAC│╞═╪═╪═╡│osm_id│-0.833553│0││高速公路│26│0.41616666││Natural│4│0.994││水道。│9│0.6663│└─┴─┴─┘。

首先是osm_id列，这是一组几乎唯一的正值和负值。要在两个版本中创建的索引：

以下查询始终用于报告索引大小。查询本身不会重复，因为只有筛选器会更改。

选择ai.schemaname作为s_name，ai.relname作为t_name，ai.indexrelname作为索引名称，pg_size_pretty(pg_relation_size(quote_ident(ai.schemaname)：：text||'；.'；||QUOTE_IDENT(ai.indexrelname)：：Text))作为索引大小，pg_relation_size(quote_ident(ai.schemaname)：：text||'；.'；|QUOTE_IDENT(ai.indexrelname)：：text)As index_size_bytes from pg_Catalog.pg_stat_all_indedes ai where ai.indexrelname LIKE'；ix_OSM_LINE%'；ORDER BY INDEX_NAME；

由于复制件的数量很少，难怪尺寸只有很小的缩小。仅从INDEX_SIZE列(MB)无法检测到减少，INDEX_SIZE_BYTES显示大小略有减少(29,515,776字节与29,384,704字节)。

┌─[记录1]─┬─┐│s_name│PUBLIC││t_name│PLANET_osm_line││索引名称│ix_osm_line_osm_id││索引大小│28 MB││索引大小字节│29515776│└─┴─┘。

┌─[记录1]─┬─┐│s_name│PUBLIC││t_name│PLANET_osm_line││索引名称│ix_osm_line_osm_id││索引大小│28 MB││索引大小字节│29384704│└─┴─┘。

PLANET_OSM_LINE数据中的高速公路数据就是一个很好的例子，说明部分索引通常是最小化索引大小的好主意。我的预感(也是希望)是，重复数据删除将通过减少索引大量空值所需的大小，使这里的部分索引变得毫无意义。

创建两个索引，一个是覆盖非空值的部分索引，另一个是整个表上的fullindex。

在public_OSM_LINE(HIGHAY)上创建索引ix_OSM_LINE_PRODUTY_PARTIAL，当PRECHAY不为空时创建索引IX_OSM_LINE_WALLE_FULL；

Postgres 12中的两个索引注意，部分索引从完整索引中减少了大约1/3的大小。

┌─┬─┬──┬─┐│s名称│t名称│索引名称│索引大小│╞═╪═╪════。═╪═╡│PUBLIC│PLANET_OSM_LINE│IX_OSM_LINE_HEADY_FULL│30 MB││PUBLIC│PLANET_OSM_LINE│IX_OSM_LINE_HEADY_PARTIAL│19 MB│└─┴─┴─。─┴─┘。

现在看一下Postgres 13中相同的两个索引，哇！Postgres 13中的Fullindex大约是Postgres 12中部分索引大小的一半！有了这种类型的节省，我预计我不会经常费心使用分部索引。

┌─┬─┬──┬─┐│s名称│t名称│索引名称│索引大小│╞═╪═╪════。═╪═╡│PUBLIC│PLANET_OSM_LINE│IX_OSM_LINE_HEADY_FULL│8944kB││PUBLIC│PLANET_OSM_LINE│IX_OSM_LINE_HEADY_PARTIAL│5272 kB│└─┴─┴─。─┴─┘。

我并不认为仅凭这一改进就可以消除对部分索引的需求。相反，它将把对部分索引的需求减少到边缘案例的较小子集。部分索引仍将是一个有用的工具。

最后两个要测试的列与高速路列具有不同的唯一和空比率。创建索引。

创建索引ix_OSM_LINE_WATHWAY_PARTIAL，在public上创建索引ix_OSM_LINE_WATWAY_PARTIAL，其中WATWAY不为NULL；在public上创建索引IX_OSM_LINE_WATWAY_FULL；在public上创建索引IX_OSM_LINE_NATUAL_PARTIAL。Planet_OSM_LINE("；Natural"；)其中"；Natural"；不为空；在public上创建索引IX_OSM_LINE_Natural_FULL。

注意：osm2pgsql创建自然列，这是SQL中的保留关键字。您必须使用双引号引用此列，例如SELECT"；Natural&34；...。

下面是Postgres 12中这四(4)个新索引的索引大小。由于99.4%的行为NULL，自然列上的部分索引在这里大大优于完整索引。水路也节省了更多的部分索引，67%的行是NULL。作为参考，HIGHAYS列是42%的NULL。

┌─┬─┬──┬─┐│s名称│t名称│索引名称│索引大小│╞═╪═╪═══。══╪═╡│PUBLIC│PLANET_OSM_LINE│IX_OSM_LINE_自然_FULL│28 MB││PUBLIC│PLANET_OSM_LINE│IX_OSM_LINE_自然_PARTIAL│264KB││PUBLIC│PLANET_OSM_LINE│IX_OSM_LINE_WATWAY_FULL│28 MB││PUBLIC│PLANET_osm_line│IX_osm_。LINE_WATWAY_PARTIAL│└─┴─┴──┴─┘9680kB│。

同样，Postgres 13中的每个指数都比Postgres 12中的对应指数小很多。这里的胜利随着空值的不同模式发生了一些变化。

┌─┬─┬──┬─┐│s名称│t名称│索引名称│索引大小│╞═╪═╪═══。══╪═╡│PUBLIC│PLANET_OSM_LINE│IX_osm_LINE_Natural_Full│8912kB││PUBLIC│PLANET_osm_LINE│ix_osm_LINE_Natural_Partial│80 kB││PUBLIC│PLANET_OSM_LINE│IX_osm_LINE_WATWAY_FULL│8912kB││PUBLIC│PLANET_osm_LINE│IX_osm_。LINE_WATWAY_PARTIAL│└─┴─┴──┴─┘2968kB│。

Postgres 13的B-Tree重复数据删除对于值严重重复的列的索引来说是一大胜利。我使用复制和空值测试的列的大小减少了69-72%。下表将上述所有测试的结果汇总在一起。

下面是两个图表中相同的数据。首先，图表显示了尺寸缩小的百分比。

到目前为止，PostgreSQL13的B-Tree重复数据删除功能可能是我最喜欢的即将发布的版本。这一改进在磁盘上节省的数量给我留下了相当深刻的印象。它对唯一的B-Tree索引大小无济于事，尽管有大量类似于此处所示的非唯一索引。能够在具有空值的列上创建更紧凑的索引，而不需要部分索引，这是另一个好处。这似乎会使获取正确的索引变得更容易，同时保持较小的索引大小。