Covering Indexes for B-trees (INCLUDE)从11开始引入
Covering indexes for GiST (INCLUDE) 从12开始引入
目前仅支持以上类型的索引(B-trees,GiST)。
为什么使用覆盖索引呢?
覆盖索引可以不需要回表,即减少了IO,使用index-only scan扫描即可拿到需要的数据。
通过下图,我们可以比较和普通的B-tree的区别
正常B-tree如图:左边是索引,后面是table,最底层的叶子节点,是一个双向链表。
查找数据的时候,我们就会遍历这颗树,然后到表中找到我们匹配的数据,如下图:
再来看index-only scan,如果我们需要的数据列恰好在索引中就可以检索到,那么我们就不需要访问表的heap了,这样就很明显的减少了IO,就叫index-only scan。
B-tree索引不仅在where条件中的字段有帮助,而且在order by, group by, select这些操作中也会有所帮助。
如下图,只扫描索引键即可拿到相应的数据,不需要通过ctid再回表拿数据:
include选项的作用:
include子句允许把我们需要的相关列的数据也放在叶子节点中,这样,我们直接从索引中就可以拿到数据,不需要回表,可以走index-only scan。
实例:
#建测试表,插入测试数据
=$ create table test (id serial primary key, some_rand int4, larger text);
CREATE TABLE
=$ insert into test (some_rand, larger) select random() * 500000, substr(md5(i::text), 1, 10) from generate_series(1,10000000) i;
INSERT 0 10000000
=$ create index small_idx on test (some_rand, id);
CREATE INDEX
=$ vacuum freeze analyze test;
VACUUM
#可以走Index Only Scan
=$ explain select some_rand, id from test where some_rand > 30000 order by some_rand, id limit 20;
QUERY PLAN
--------------------------------------------------------------------------------------------
Limit (cost=0.43..1.00 rows=20 width=8)
-> Index Only Scan using small_idx on test (cost=0.43..267048.14 rows=9380326 width=8)
Index Cond: (some_rand > 30000)
(3 rows)
#加了larger列,无法走Index Only Scan
=$ explain select some_rand, id, larger from test where some_rand > 30000 order by some_rand, id limit 20;
QUERY PLAN
----------------------------------------------------------------------------------------
Limit (cost=0.43..1.55 rows=20 width=19)
-> Index Scan using small_idx on test (cost=0.43..521828.01 rows=9380326 width=19)
Index Cond: (some_rand > 30000)
(3 rows)
#加上larger字段的组合索引,也可以走Index Only Scan
=$ create index large_idx on test (some_rand, id, larger);
=$ explain select some_rand, id, larger from test where some_rand > 30000 order by some_rand, id limit 20;
QUERY PLAN
---------------------------------------------------------------------------------------------
Limit (cost=0.56..1.31 rows=20 width=19)
-> Index Only Scan using large_idx on test (cost=0.56..350178.38 rows=9380218 width=19)
Index Cond: (some_rand > 30000)
(3 rows)
#比较两个索引大小
=$ select relname, pg_size_pretty( pg_relation_size(oid)) from pg_class where relname in ('small_idx','large_idx');
relname | pg_size_pretty
-----------+----------------
large_idx | 387 MB
small_idx | 214 MB
(2 rows)
#删除3个字段的组合索引,创建inlcude索引
=$ drop index large_idx;
DROP INDEX
=$ create index magic_idx on test (some_rand, id) include (larger);
CREATE INDEX
#同样可以走Index Only Scan
=$ explain select some_rand, id, larger from test where some_rand > 30000 order by some_rand, id limit 20;
QUERY PLAN
---------------------------------------------------------------------------------------------
Limit (cost=0.43..1.18 rows=20 width=19)
-> Index Only Scan using magic_idx on test (cost=0.43..349650.25 rows=9380218 width=19)
Index Cond: (some_rand > 30000)
(3 rows)
#相关尺寸可以比较一下
=$ select pg_size_pretty( pg_relation_size('magic_idx'::regclass));
pg_size_pretty
----------------
386 MB
(1 row)
这里需要注意,include的列,不会在相关的索引中排序,如果建一个唯一约束,并带有include列,但是include列不会有唯一性,如下id有唯一约束,payload没有唯一约束:
CREATE UNIQUE INDEX …
ON … ( id )
INCLUDE ( payload )
inlcude图示如下:
与组合索引相比,带有include的索引有以下优点:
- 树的层级可能会更低,因为由于双链表上面的分支节点不包含include列,数据库可以在每个块中存储更多的分支,从而树的层级更低。
- 索引尺寸更小,由于树的非叶子节点不包含include列,因此索引的总大小略小一些。相比较,索引的叶子节点需要最多的空间。
参考:
https://use-the-index-luke.com/blog/2019-04/include-columns-in-btree-indexes#unique
https://blog.crunchydata.com/blog/why-covering-indexes-are-incredibly-helpful
https://www.postgresql.org/docs/current/indexes-index-only-scans.html