PostgreSQL 11 preview - 索引 增強 彙總

标簽

PostgreSQL , 索引 , 增強 , 11

https://github.com/digoal/blog/blob/master/201805/20180519_03.md#%E8%83%8C%E6%99%AF 背景

PostgreSQL 11 索引特性的增強。

https://github.com/digoal/blog/blob/master/201805/20180519_03.md#e1313-indexes E.1.3.1.3. Indexes

• Allow indexes to  INCLUDE

   columns that are not part of the unique constraint but are available for index-only scans (Anastasia Lubennikova, Alexander Korotkov, Teodor Sigaev)

  This is also useful for including columns that don't have btree support.

  《PostgreSQL 11 preview 功能增強 - 唯一限制 + 附加字段組合功能索引》 允許唯一限制索引中，使用INCLUDE包含非唯一限制的字段的内容。甚至對于無法使用BTREE建構的字段類型，也可以将它的字段内容用include包含進來。類似b+tree的效果。
• Remember the highest btree index page to optimize future monotonically increasing index additions (Pavan Deolasee, Peter Geoghegan)

• Allow entire hash index pages to be scanned (Ashutosh Sharma)

  Previously for each hash index entry, we need to refind the scan position within the page. This cuts down on lock/unlock traffic.

• Add predicate locking for hash, GiST and GIN indexes (Shubham Barai)

  This reduces the likelihood of serialization conflicts. ACCURATE?

• Allow heap-only-tuple (HOT) updates for expression indexes when the values of the expressions are unchanged (Konstantin Knizhnik) 《PostgreSQL 11 preview - Surjective indexes - 索引HOT增強（表達式）update評估》

https://github.com/digoal/blog/blob/master/201805/20180519_03.md#e13131-sp-gist E.1.3.1.3.1.  SP-Gist

• Add TEXT prefix operator ^@ which is supported by SP-GiST (Ildus Kurbangaliev)

  This is similar to using LIKE 'word%' with btree indexes, but is more efficient.

  允許SP-GiST索引支援字首模糊查詢( 

  ^@

   與 

  LIKE 'word%'

   效果一緻 )，并且效率比btree索引更高。

• Allow polygons to be indexed with SP-GiST (Nikita Glukhov, Alexander Korotkov)

  允許polygon類型使用SP-GiST索引 (空間分區索引)。

• Allow SP-GiST indexes to optionally use compression (Teodor Sigaev, Heikki Linnakangas, Alexander Korotkov, Nikita Glukhov)

  允許SP-GiST索引支援壓縮。