作者
digoal
日期
2018-05-19
标签
PostgreSQL , 索引 , 增强 , 11
背景
PostgreSQL 11 索引特性的增强。
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评估》
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索引支持压缩。
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
