Postgresql:Covering Indexes

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的索引有以下优点:

1. 树的层级可能会更低，因为由于双链表上面的分支节点不包含include列，数据库可以在每个块中存储更多的分支,从而树的层级更低。
2. 索引尺寸更小，由于树的非叶子节点不包含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

这篇好文章是转载于：学新通技术网