执行计划不走索引的几种情况总结

优化器不想用索引，主要原因是优化器认为走索引还不如走顺序扫描代价低，因为索引扫描对应的是离散IO，我们可以通过调整random_page_cost告诉优化器随机IO代价值，非特殊情况不建议修改此值。

1.表太小场景

经常有开发问，为什么有索引而不走索引呢？因为优化器认为走索引方式太慢了！

test=# create table sma(id int);
CREATE TABLE
test=# create index on sma (id);
CREATE INDEX
test=# insert into sma values(generate_series(1,100));
INSERT 0 100
test=# analyze sma;
ANALYZE
test=# explain select id from sma where id = 10;
                      QUERY PLAN                       
-------------------------------------------------------
 Seq Scan on sma  (cost=0.00..2.25 rows=1 width=4)
   Filter: (id = 10)
(2 rows)

test=# set enable_seqscan to off;
set
test=# explain select id from sma where id = 10;
                                    QUERY PLAN                                     
-----------------------------------------------------------------------------------
 Index Only Scan using sma_id_idx on sma  (cost=0.14..8.12 rows=1 width=4)
   Index Cond: (id = 10)
(2 rows)

可以看到索引的总成本8.12要比顺序扫描2.25高了4倍左右，所以优化器选择走顺序扫描了。
为什么小表场景中，优化器可能选择顺序扫描而不是索引扫描，因为服务器进程可能在1个数据块里就获得了所需数据，相比索引扫描，在索引块的叶子节点获得对应数据块的指针后还需要去表里去扫描取得行记录，这样扫描的总块数，或者说IO次数就会多余顺序扫描所需的块数。

2.关联度

高效的索引扫描，只需扫描几次就能获取到所需数据，而糟糕的索引扫描往往需要返回大量数据，需要多次扫描，每次都要经历根节点 → 分支节点 → 叶子节点，索引的level越高，需要耗费的IO越大，导致大量的离散IO，因此优化器也会将这个关联度考虑进去。
高效的顺序扫描数据更加紧凑，数据块都集中在一起，这样IO的效率就提高了，只需很少的IO，就可以获取到大部分数据。
而糟糕的顺序扫描则数据相对离散，需要扫描大量数据并过滤才能获取到想要的数据，个就是我在统计信息里面提到的correlation字段的作用，表示列的物理顺序和逻辑顺序的相关性，相关性越高，走索引扫描的离散块扫描更少，代价越低。

例如：

test=# \d tbl
    Table "public.tbl"
  Column  |  Type   | Modifiers 
----------+---------+-----------
 col      | text    | 
 col_asc  | integer | 
 col_desc | integer | 
 col_rand | integer | 
 data     | text    |
Indexes:
    "tbl_asc_idx" btree (col_asc)
    "tbl_desc_idx" btree (col_desc)
    "tbl_rand_idx" btree (col_rand)

test=# SELECT col,col_asc,col_desc,col_rand FROM tbl;
   col    | col_asc | col_desc | col_rand 
----------+---------+----------+----------
 Tuple_1  |       1 |       12 |        3
 Tuple_2  |       2 |       11 |        8
 Tuple_3  |       3 |       10 |        5
 Tuple_4  |       4 |        9 |        9
 Tuple_5  |       5 |        8 |        7
 Tuple_6  |       6 |        7 |        2
 Tuple_7  |       7 |        6 |       10
 Tuple_8  |       8 |        5 |       11
 Tuple_9  |       9 |        4 |        4
 Tuple_10 |      10 |        3 |        1
 Tuple_11 |      11 |        2 |       12
 Tuple_12 |      12 |        1 |        6
(12 rows)

test=# SELECT tablename,attname, correlation FROM sys_stats WHERE tablename = 'tbl';
 tablename | attname  | correlation 
-----------+----------+-------------
 tbl       | col_asc  |      1
 tbl       | col_desc |     -1
 tbl       | col_rand | 0.1258
(3 rows)

表有三个索引，其中
col_asc索引是顺序递增的
col_desc索引是倒序递减的
col_rand索引则是随机分布的
因此如果SQL需要查询2到4的数据
SELECT * FROM tbl WHERE col_asc BETWEEN 2 AND 4;，对于col_asc，只需要读第一个页面即可，因为数据排序是紧凑连续的。

而假如是对随机列进行查询的话，SELECT * FROM tbl WHERE col_rand BETWEEN 2 AND 4;则需要读取所有的页面，因为数据分布是离散的，它们可能存在不同的数据块。这时优化器就会评估顺序扫描和索引扫描的代价，最终选择代价更低的执行计划。

因此在索引扫描的时候也要将correlation考虑进去。
在Kingbase数据库中，我们可以使用cluster命令进行聚簇，对于某些时序类的数据进行范围查询，会有性能提升作用，因为索引排序和数据分布都是连续的。

3.返回的结果集过大
索引会对应大量的离散IO，我想说一下选择率这个词，如果大部分数据都需要查询到，那么索引的作用就微乎其微了，这时候再用索引扫描代价明显高于顺序扫描。
如果一个索引能够在查询中使用索引扫描中过滤掉大部分的行，则这个索引有较好的选择率，选择率更低，这时候索引更高效。反之，选择率更高，使用索引的效率不高，那么优化器就不会使用索引。

test=# create table big_t(id int);
CREATE TABLE
test=# create index on big_t (id);
CREATE INDEX
test=# insert into big_t values(generate_series(1,100000));
INSERT 0 100000
test=# analyze big_t ;
ANALYZE
test=# explain select id from big_t where id > 1;
                                      QUERY PLAN                                      
--------------------------------------------------------------------------------------
 Index Only Scan using big_t_id_idx on big_t  (cost=0.29..3304.28 rows=99999 width=4)
   Index Cond: (id > 1)
(2 rows)

test=# set enable_seqscan to on;
SET
test=# explain select id from big_t where id > 1;
                         QUERY PLAN                         
------------------------------------------------------------
 Seq Scan on big_t  (cost=0.00..1693.00 rows=99999 width=4)
   Filter: (id > 1)
(2 rows)

这个例子说明，当获得大量结果集时候，选择率更高，这时候索引更低效，则优化器优先选择全表扫描。

4.最左原则

test=# create table t(id int,info text);
CREATE TABLE
test=# create index on t(id,info);
CREATE INDEX
test=# insert into t select n,md5(random()::text) from generate_series(1,100000) as n;
INSERT 0 100000
test=# analyze t;
ANALYZE
test=# explain select id,info from t where id = 1 and info = 'hello';
                                 QUERY PLAN                                  
-----------------------------------------------------------------------------
 Index Only Scan using t_id_info_idx on t  (cost=0.42..8.44 rows=1 width=37)
   Index Cond: ((id = 1) AND (info = 'hello'::text))
(2 rows)

test=# explain select id,info from t where info = 'hello';
                     QUERY PLAN                      
-----------------------------------------------------
 Seq Scan on t  (cost=0.00..2084.00 rows=1 width=37)
   Filter: (info = 'hello'::text)
(2 rows)
可以看到，我创建了一个复合索引，假设是（a,b,c）的复合索引，那么索引会先按照a列排序存储，接着按照b列排序，最后是c列，假如SQL直接查询的b或者c，意味着基可能需要访问整棵索引树，这样代价太高了。
test=# set enable_seqscan to off;
SET
强制使用索引扫描，成本要比上面的顺序扫描高，所以对于查询条件中跳过联合索引的索引前导列的情况，优化器默认使用了顺序扫描。
test=# explain select id,info from t where info = 'hello';  
                                   QUERY PLAN                                   
--------------------------------------------------------------------------------
 Index Only Scan using t_id_info_idx on t  (cost=0.42..3630.43 rows=1 width=37)
   Index Cond: (info = 'hello'::text)
(2 rows)

这里运用逆向思维，我们可以从执行计划的cost成本估算中理解优化器选择的检索数据方式，然后结合联合索引的排列原理，理解优化器的估算方式。

5.查询条件模糊

如果查询条件使用了不等于（<>）、LIKE等运算符或者使用了函数等，那么索引可能无法被使用。
因为正常情况下，等于（=）操作符可以直接利用B-tree或哈希索引进行查找。这是因为这些操作符可以在索引结构中找到对应的记录选项。
而不等于（<>）操作符则需要查找所有不符合条件的记录，这会导致需要遍历整个索引树来找到匹配的记录，带来的结果是使用索引的成本比全表扫描成本更高。
LIKE操作符也可能导致无法使用索引。例如’%abc’，则索引将不会被使用，如果通配符是abc%，则可以使用索引。原理同上。

6.糟糕的统计信息

过期的，糟糕的统计信息会让优化器产生误判，可能由于某种原因导致 autovacuum进程没有及时收集统计信息，这时需要做analyze手动收集统计信息，或者修改字段长度、数据类型、dml大量数据变更，也需要手工收集统计信息，否则选择率可能存在误差导致优化器估算不准。