作者
digoal
日期
2019-03-31
标签
PostgreSQL , include , columns , 覆盖索引 , index only scan
背景
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=f2e403803fe6deb8cff59ea09dff42c6163b2110
在索引中植入非索引（KEY）字段的内容，用于index only scan。达到类似聚簇表效果。
例如:
```
create table t (id int, info text, crt_time timestamp);
create index idx_t_1 on t (id) include (info, crt_time);
postgres=# \d+ t
Table "public.t"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
----------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
id | integer | | | | plain | |
info | text | | | | extended | |
crt_time | timestamp without time zone | | | | plain | |
Indexes:
"idx_t_1" btree (id) INCLUDE (info, crt_time)
Access method: heap
postgres=# \d+ idx_t_1
Index "public.idx_t_1"
Column | Type | Key? | Definition | Storage | Stats target
----------+-----------------------------+------+------------+----------+--------------
id | integer | yes | id | plain |
info | text | no | info | extended |
crt_time | timestamp without time zone | no | crt_time | plain |
btree, for table "public.t"
select id,crt_time
id
id,info
id,info, crt_time
都支持index only scan
```
《PostgreSQL IoT，车联网 - 实时轨迹、行程实践 2 - (含index only scan类聚簇表效果)》
PG 12 include功能支持GiST，BTREE两种索引接口。
```
Support for INCLUDE attributes in GiST indexes
Similarly to B-tree, GiST index access method gets support of INCLUDE
attributes. These attributes aren't used for tree navigation and aren't
present in non-leaf pages. But they are present in leaf pages and can be
fetched during index-only scan.
The point of having INCLUDE attributes in GiST indexes is slightly different
from the point of having them in B-tree. The main point of INCLUDE attributes
in B-tree is to define UNIQUE constraint over part of attributes enabled for
index-only scan. In GiST the main point of INCLUDE attributes is to use
index-only scan for attributes, whose data types don't have GiST opclasses.
Discussion: https://postgr.es/m/73A1A452-AD5F-40D4-BD61-978622FF75C1%40yandex-team.ru
Author: Andrey Borodin, with small changes by me
Reviewed-by: Andreas Karlsson
```
例子
CREATE INDEX tbl_gist_idx ON public.tbl_gist USING gist (c4) INCLUDE (c1, c2, c3)
1 /*
2 * 1.1. test CREATE INDEX with buffered build
3 */
4 -- Regular index with included columns
5 CREATE TABLE tbl_gist (c1 int, c2 int, c3 int, c4 box);
6 -- size is chosen to exceed page size and trigger actual truncation
7 INSERT INTO tbl_gist SELECT x, 2*x, 3*x, box(point(x,x+1),point(2*x,2*x+1)) FROM generate_series(1,8000) AS x;
8 CREATE INDEX tbl_gist_idx ON tbl_gist using gist (c4) INCLUDE (c1,c2,c3);
9 SELECT pg_get_indexdef(i.indexrelid)
10 FROM pg_index i JOIN pg_class c ON i.indexrelid = c.oid
11 WHERE i.indrelid = 'tbl_gist'::regclass ORDER BY c.relname;
12 pg_get_indexdef
13 -----------------------------------------------------------------------------------
14 CREATE INDEX tbl_gist_idx ON public.tbl_gist USING gist (c4) INCLUDE (c1, c2, c3)
15 (1 row)
16
17 SELECT * FROM tbl_gist where c4 <@ box(point(1,1),point(10,10));
18 c1 | c2 | c3 | c4
19 ----+----+----+-------------
20 1 | 2 | 3 | (2,3),(1,2)
21 2 | 4 | 6 | (4,5),(2,3)
22 3 | 6 | 9 | (6,7),(3,4)
23 4 | 8 | 12 | (8,9),(4,5)
24 (4 rows)
25
26 SET enable_bitmapscan TO off;
27 EXPLAIN (costs off) SELECT * FROM tbl_gist where c4 <@ box(point(1,1),point(10,10));
28 QUERY PLAN
29 ------------------------------------------------
30 Index Only Scan using tbl_gist_idx on tbl_gist
31 Index Cond: (c4 <@ '(10,10),(1,1)'::box)
32 (2 rows)
33
参考
《PostgreSQL 10.0 preview 功能增强 - 唯一约束+附加字段组合功能索引 - 覆盖索引 - covering index》
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=f2e403803fe6deb8cff59ea09dff42c6163b2110
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
