PostgreSQL 10.0 preview 功能增强 - 唯一约束+附加字段组合功能索引 - 覆盖索引 - covering index
背景
如果我们有这样的查询
select * from tbl where c1=? and c2=? and c3=? and c4=?
我们建立了复合索引达到最好的查询性能
create index idx on tbl(c1,c2,c3,c4);
同时还有这样的约束
create unique index idx on tbl (c1,c2);
那么这样的场景中，我们就有两个索引。
PostgreSQL 10.0提供了一个新的功能，可以将这两个索引合并，只有一个索引的体积，同时支持这两个场景。。
create unique index idx on tbl (c1,c2) including (c3,c4);
这便是唯一约束+附加字段组合功能索引
详见
Hi hackers,
I'm working on a patch that allows to combine covering and unique
functionality for btree indexes.
_Previous discussion was here:_
1) Proposal thread
<http://www.postgresql.org/message-id/55F2CCD0.7040608@postgrespro.ru>
2) Message with proposal clarification
<http://www.postgresql.org/message-id/55F84DF4.5030207@postgrespro.ru>
In a nutshell, the feature allows to create index with "key" columns and
"included" columns.
"key" columns can be used as scan keys. Unique constraint relates only
to "key" columns.
"included" columns may be used as scan keys if they have suitable opclass.
Both "key" and "included" columns can be returned from index by
IndexOnlyScan.
Btree is the default index and it's used everywhere. So it requires
properly testing. Volunteers are welcome)
_Use case:_
- We have a table (c1, c2, c3, c4);
- We need to have an unique index on (c1, c2).
- We would like to have a covering index on all columns to avoid reading
of heap pages.
Old way:
CREATE UNIQUE INDEX olduniqueidx ON oldt USING btree (c1, c2);
CREATE INDEX oldcoveringidx ON oldt USING btree (c1, c2, c3, c4);
What's wrong?
Two indexes contain repeated data. Overhead to data manipulation
operations and database size.
New way:
CREATE UNIQUE INDEX newidx ON newt USING btree (c1, c2) INCLUDING (c3, c4);
The patch is attached.
In 'test.sql' you can find a test with detailed comments on each step,
and comparison of old and new indexes.
New feature has following syntax:
CREATE UNIQUE INDEX newidx ON newt USING btree (c1, c2) INCLUDING (c3, c4);
Keyword INCLUDING defines the "included" columns of index. These columns
aren't concern to unique constraint.
Also, them are not stored in index inner pages. It allows to decrease
index size.
_Results:_
1) Additional covering index is not required anymore.
2) New index can use IndexOnlyScan on queries, where old index can't.
For example,
explain analyze select c1, c2 from newt where c1<10000 and c3<20;
*more examples in 'test.sql'
_Future work:_
To do opclasses for "included" columns optional.
CREATE TABLE tbl (c1 int, c4 box);
CREATE UNIQUE INDEX idx ON tbl USING btree (c1) INCLUDING (c4);
If we don't need c4 as an index scankey, we don't need any btree opclass
on it.
But we still want to have it in covering index for queries like
SELECT c4 FROM tbl WHERE c1=1000;
SELECT * FROM tbl WHERE c1=1000;
--
Anastasia Lubennikova
Postgres Professional:http://www.postgrespro.com
The Russian Postgres Company
这个patch的讨论，详见邮件组，本文末尾URL。
PostgreSQL社区的作风非常严谨，一个patch可能在邮件组中讨论几个月甚至几年，根据大家的意见反复的修正，patch合并到master已经非常成熟，所以PostgreSQL的稳定性也是远近闻名的。
参考
https://commitfest.postgresql.org/13/961/
https://www.postgresql.org/message-id/flat/56168952.4010101@postgrespro.ru#56168952.4010101@postgrespro.ru