PostgreSQL 15 preview - GiST sorted build method 索引性能优化

作者

digoal

日期

2022-02-08

标签

PostgreSQL , gist , sorted build method

《PostgreSQL 14 preview - gist和sp-gist索引AM支持sort接口, 大幅加速GiST和SP-GiST 索引build速度》

这个特性提升了build gist,sp-gist索引的速度, 但是这种方法build的索引容易出现root、branch节点页面数据的overlap, 导致搜索时精准度下降, 需要更多的内部filter, 从而降低查询性能.

PostgreSQL 优化了sorted build method , 解决索引采用sorted build方法后索引性能下降的问题.

+Sorted build method  
+-------------------  
+  
+Sort all input tuples, pack them into GiST leaf pages in the sorted order,  
+and create downlinks and internal pages as we go. This method builds the index  
+from the bottom up, similar to how the B-tree index is built.  
+  
+The sorted method is used if the operator classes for all columns have a  
+"sortsupport" defined. Otherwise, we fall back on inserting tuples one by one  
+with optional buffering.  
+  
+Sorting GiST build requires good linearization of the sort opclass. That is not  
+always the case in multidimensional data. To tackle the anomalies, we buffer  
+index tuples and apply a picksplit function that can be multidimensional-aware.  
+  
复制

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=f1ea98a7975e15cefdb446385880a2f55224ee7d

Reduce non-leaf keys overlap in GiST indexes produced by a sorted build  
author  Alexander Korotkov <akorotkov@postgresql.org>     
Mon, 7 Feb 2022 20:20:42 +0000 (23:20 +0300)  
committer   Alexander Korotkov <akorotkov@postgresql.org>     
Mon, 7 Feb 2022 20:20:42 +0000 (23:20 +0300)  
commit  f1ea98a7975e15cefdb446385880a2f55224ee7d  
tree    8efc57dc7b2480397fc838baa0dd328630f611b6    tree  
parent  42a9e88bf6a809e6023c9d50f58cc6b9446f229d    commit | diff  
Reduce non-leaf keys overlap in GiST indexes produced by a sorted build  
The GiST sorted build currently chooses split points according to the only page  
space utilization.  That may lead to higher non-leaf keys overlap and, in turn,  
slower search query answers.  
This commit makes the sorted build use the opclass's picksplit method.  Once  
four pages at the level are accumulated, the picksplit method is applied until  
each split partition fits the page.  Some of our split algorithms could show  
significant performance degradation while processing 4-times more data at once.  
But those opclasses haven't received the sorted build support and shouldn't  
receive it before their split algorithms are improved.  
Discussion: https://postgr.es/m/CAHqSB9jqtS94e9%3D0vxqQX5dxQA89N95UKyz-%3DA7Y%2B_YJt%2BVW5A%40mail.gmail.com  
Author: Aliaksandr Kalenik, Sergei Shoulbakov, Andrey Borodin  
Reviewed-by: Björn Harrtell, Darafei Praliaskouski, Andres Freund  
Reviewed-by: Alexander Korotkov  
复制

期望 PostgreSQL 增加什么功能?

PolarDB for PostgreSQL云原生分布式开源数据库

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.