其他
聊一聊索引失效
前言
昨天有位同事找到我，咨询为何有条 SQL 无法使用索引。索引失效也是一个老生常谈的话题了，正好借着在酒店隔离的期间，也梳理一下在 PostgreSQL 中哪些情况会导致索引失效。
按照大的分类，索引失效又可以细分为两类：
建的索引无法使用，不能用 优化器不选择走索引，不想用
Either it can’t use the index, or it doesn’t think using the index will be faster.
不能用
那么哪些情况下会导致索引无法使用？整理了以下几个原因 👇🏻
索引类型不匹配
首先最常见的情形便是索引的类型不匹配，比如
postgres=# create table test(id int,info text);
CREATE TABLE
postgres=# create index on test(id);
CREATE INDEX
postgres=# create index on test(info);
CREATE INDEX
postgres=# insert into test select n,md5(random()::text) from generate_series(1,100000) as n;
INSERT 0 100000
postgres=# analyze test;
ANALYZE
postgres=# \d test
Table "public.test"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | |
info | text | | |
Indexes:
"test_id_idx" btree (id)
"test_info_idx" btree (info)
两列上各有一个索引，假如过滤条件带有函数，那么索引便无法使用
postgres=# explain select * from test where info = 'hello';
QUERY PLAN
---------------------------------------------------------------------------
Index Scan using test_info_idx on test (cost=0.42..8.44 rows=1 width=37)
Index Cond: (info = 'hello'::text)
(2 rows)
postgres=# explain select * from test where lower(info) = 'hello'; ---无法使用
QUERY PLAN
----------------------------------------------------------
Seq Scan on test (cost=0.00..2334.00 rows=500 width=37)
Filter: (lower(info) = 'hello'::text)
(2 rows)
一个可行的方式是建一个函数索引，不过函数索引要求对应的函数必须是 immutable 的，可以选择自己套一层，函数三态自行了解。
postgres=# create index on test(lower(info));
CREATE INDEX
postgres=# analyze test;
ANALYZE
postgres=# explain select * from test where lower(info) = 'hello';
QUERY PLAN
----------------------------------------------------------------------------
Index Scan using test_lower_idx on test (cost=0.42..8.44 rows=1 width=37)
Index Cond: (lower(info) = 'hello'::text)
(2 rows)
与函数索引类似的是，过滤条件（谓词）必须位于右侧，否则也会导致索引失效
postgres=# explain select * from test where id = 99;
QUERY PLAN
-------------------------------------------------------------------------
Index Scan using test_id_idx on test (cost=0.29..8.31 rows=1 width=37)
Index Cond: (id = 99)
(2 rows)
postgres=# explain select * from test where id = 98 + 1;
QUERY PLAN
-------------------------------------------------------------------------
Index Scan using test_id_idx on test (cost=0.29..8.31 rows=1 width=37)
Index Cond: (id = 99)
(2 rows)
postgres=# explain select * from test where id -1 = 98; ---无法使用索引
QUERY PLAN
----------------------------------------------------------
Seq Scan on test (cost=0.00..2334.00 rows=500 width=37)
Filter: ((id - 1) = 98)
(2 rows)
可以看到虽然这三条SQL的逻辑和结果集一模一样，但是第三条SQL就无法走索引，而第二条SQL优化器则自动做了转化——常量折叠，这个在之前的优化器篇章里面有介绍过优化器逻辑推理，此处不再过多解释。
collate不一致
当我们在使用 initdb 初始化数据库时提供了一个叫做“本地化”的参数 locale，不幸的是，locale与encoding的默认配置取决于操作系统的配置，因此假如不指定的话会沿用操作系统的配置。locale是根据计算机用户所使用的语言，所在国家或者地区，以及当地的文化传统所定义的一个软件运行时的语言环境。locale把按照所涉及到的文化传统的各个方面分成12个大类，这12个大类分别是：
语言符号及其分类(LC_CTYPE) 数字(LC_NUMERIC) 比较和排序习惯(LC_COLLATE) 时间显示格式(LC_TIME) 货币单位(LC_MONETARY) ...
所以说，locale就是某一个地域内的人们的语言习惯和文化传统和生活习惯。本地化设置对以下SQL特性有影响:
排序和比较操作 : Sort order in queries using ORDER BY or the standard comparison operators on textual data 内置函数 : The upper, lower, and initcap functions 模式匹配 : Pattern matching operators (LIKE, SIMILAR TO, and POSIX-style regular expressions); locales affect both case insensitive matching and the classification of characters by character-class regular expressions to_char相关函数 : The to_char family of functions LIKE能否使用索引 : The ability to use indexes with LIKE clauses
其中 collate 是最容易被忽略的一点，简而言之，collate会影响到order by语句的顺序，会影响到where条件中大于小于号筛选出来的结果等，比如下面我这个实例，encoding是UTF8，collate是en_US.UTF-8 👇🏻
postgres=# select datname,pg_encoding_to_char(encoding) as encoding,datcollate from pg_database ;
datname | encoding | datcollate
-----------+----------+-------------
template1 | UTF8 | en_US.UTF-8
template0 | UTF8 | en_US.UTF-8
postgres | UTF8 | en_US.UTF-8
(3 rows)
那么影响何如？看个栗子
postgres=# select 'a' > 'A' collate "en_US";
?column?
----------
f
(1 row)
postgres=# select 'a' > 'A' collate "C";
?column?
----------
t
(1 row)
postgres=# select 'a' > 'A' collate "zh_CN";
?column?
----------
f
(1 row)
可以看到不同的collate会影响字符的排序比较，另外就是索引失效了
postgres=# \d test
Table "public.test"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | |
info | text | | |
Indexes:
"test_id_idx" btree (id)
"test_info_idx" btree (info)
"test_lower_idx" btree (lower(info))
postgres=# explain select info from test where info like 'hello%';
QUERY PLAN
---------------------------------------------------------
Seq Scan on test (cost=0.00..2084.00 rows=10 width=33)
Filter: (info ~~ 'hello%'::text)
(2 rows)
postgres=# create index on test(info collate "C"); ---指定collate
CREATE INDEX
postgres=# analyze test;
ANALYZE
postgres=# \d test
Table "public.test"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | |
info | text | | |
Indexes:
"test_id_idx" btree (id)
"test_info_idx" btree (info)
"test_info_idx1" btree (info COLLATE "C") ---新建索引
"test_lower_idx" btree (lower(info))
postgres=# explain select info from test where info like 'hello%';
QUERY PLAN
----------------------------------------------------------------------------------
Index Only Scan using test_info_idx1 on test (cost=0.42..4.44 rows=10 width=33)
Index Cond: ((info >= 'hello'::text) AND (info < 'hellp'::text))
Filter: (info ~~ 'hello%'::text)
(3 rows)
因为locale关于字符串的等价规则有一套自己的定义，比如在MySQL里面，德语字母“ß”，在utf8mb4_unicode_ci中是等价于”ss”两个字母的，而在utf8mb4_general_ci中，它却和字母“s”等价。因此只有最朴素的数据库自身提供的C LOCALE，才能够正常地进行模式匹配。"C" LOCALE的比较规则非常简单，就是挨个比较字符码位。
当然还有一种方式就是指定额外的操作符了，不过这会增加额外的维护成本，也意味着原来主键/唯一约束自带的索引无法使用
postgres=# drop index test_info_idx1;
DROP INDEX
postgres=# create index on test(info varchar_pattern_ops);
CREATE INDEX
postgres=# analyze test;
ANALYZE
postgres=# explain select info from test where info like 'hello%';
QUERY PLAN
----------------------------------------------------------------------------------
Index Only Scan using test_info_idx1 on test (cost=0.42..4.44 rows=10 width=33)
Index Cond: ((info ~>=~ 'hello'::text) AND (info ~<~ 'hellp'::text))
Filter: (info ~~ 'hello%'::text)
(3 rows)
因此建议在初始化的时候就直接指定 C，目前还没有后悔药，无法直接通过 alter database 的语法进行修改，只能采用导出再导入的方式。
数据类型不一致
这个很好理解，column类型和过滤条件类型不一致
postgres=# explain select id from test where id = 100::numeric; ---无法使用索引
QUERY PLAN
---------------------------------------------------------
Seq Scan on test (cost=0.00..2334.00 rows=500 width=4)
Filter: ((id)::numeric = '100'::numeric)
(2 rows)
postgres=# explain select id from test where id = 100::int;
QUERY PLAN
-----------------------------------------------------------------------------
Index Only Scan using test_id_idx on test (cost=0.29..4.31 rows=1 width=4)
Index Cond: (id = 100)
(2 rows)
postgres=# explain select id from test where id = '100'::text;
ERROR: operator does not exist: integer = text
LINE 1: explain select id from test where id = '100'::text;
^
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
第三条SQL更加干脆，直接报错，那么int与text类型比较，为何会报错？在示例中，‘=’为二元操作符，左操作数类型为int，右操作数类型为text，在pg_operator系统目录中，不存在该operator和相应操作数类型的定义：
postgres=# select oprname,oprleft::regtype,oprright::regtype,oprcode from pg_operator where oprname='=' and oprleft::regtype='int'::regtype;
oprname | oprleft | oprright | oprcode
---------+---------+----------+---------
= | integer | bigint | int48eq
= | integer | integer | int4eq
= | integer | smallint | int42eq
(3 rows)
testdb=# select oprname,oprleft::regtype,oprright::regtype,oprcode from pg_operator where oprname='=' and oprright::regtype='text'::regtype;
oprname | oprleft | oprright | oprcode
---------+---------+----------+------------
= | text | text | texteq
= | name | text | nameeqtext
(2 rows)
而且int和text并没有在pg_cast系统目录中定义为可相互转换，这种情况可以考虑自己创建一个cast转换。
old_snapshot_threshold作祟
假如有一直关注的小伙伴，应该对这个参数十分了解，真是又爱又恨呀，不熟悉的可以参照之前的文章《生产案例 | 费解的索引失效》，看个例子（此处开启了old_snapshot_threshold参数）
postgres=# create table t1(id int);
CREATE TABLE
postgres=# insert into t1 values(generate_series(1,100000));
INSERT 0 100000
然后新开一个会话，使其获取一个事务ID
postgres=# begin;
BEGIN
postgres=*# select txid_current(),pg_backend_pid();
txid_current | pg_backend_pid
--------------+----------------
650127 | 22694
(1 row)
然后第一个会话创建索引，可以看到即使设置了disable cost，也无法走索引
postgres=# create index on t1(id);
CREATE INDEX
postgres=# analyze t1;
ANALYZE
postgres=# \d t1
Table "public.t1"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | |
Indexes:
"t1_id_idx" btree (id)
postgres=# explain select id from t1 where id = 99;
QUERY PLAN
-----------------------------------------------------
Seq Scan on t1 (cost=0.00..1693.00 rows=1 width=4)
Filter: (id = 99)
(2 rows)
postgres=# set enable_seqscan to off; ---禁用顺序扫描
SET
postgres=# explain select id from t1 where id = 99;
QUERY PLAN
----------------------------------------------------------------------
Seq Scan on t1 (cost=10000000000.00..10000001693.00 rows=1 width=4)
Filter: (id = 99)
(2 rows)
因为此时数据库认为这个索引是 unsafe 的，不安全，所以无法使用该索引
postgres=# select indcheckxmin from pg_index where indexrelid = 't1_id_idx'::regclass;
indcheckxmin
--------------
t
(1 row)
“If true, queries must not use the index until the
xminof thispg_indexrow is below theirTransactionXminevent horizon, because the table may contain broken HOT chains with incompatible rows that they can see如果为真，直到此pg_index行的xmin低于查询的TransactionXmin视界之前，查询都不能使用此索引，因为表可能包含具有它们可见的不相容行的损坏HOT链。
当然解决办法也很简单，第一个会话提交之后即可正常使用
postgres=# explain select id from t1 where id = 99;
QUERY PLAN
-------------------------------------------------------------------------
Index Only Scan using t1_id_idx on t1 (cost=0.29..4.31 rows=1 width=4)
Index Cond: (id = 99)
(2 rows)
broken hot chain
让我们回过头来再看一下设置indcheckxmin的逻辑，这里截取部分代码
/*
* If we found any potentially broken HOT chains, mark the index as not
* being usable until the current transaction is below the event horizon.
* See src/backend/access/heap/README.HOT for discussion. Also set this
* if early pruning/vacuuming is enabled for the heap relation. While it
* might become safe to use the index earlier based on actual cleanup
* activity and other active transactions, the test for that would be much
* more complex and would require some form of blocking, so keep it simple
* and fast by just using the current transaction.
*
如果我们发现任何可能损坏的HOT链，就将索引标记为不可用，直到当前事务低于视界范围。
如果为堆表启用了早期修剪/清理，也会设置此项。
* However, when reindexing an existing index, we should do nothing here.
* Any HOT chains that are broken with respect to the index must predate
* the index's original creation, so there is no need to change the
* index's usability horizon. Moreover, we *must not* try to change the
* index's pg_index entry while reindexing pg_index itself, and this
* optimization nicely prevents that. The more complex rules needed for a
* reindex are handled separately after this function returns.
*
在concurrent create index的时候也不需要设置indcheckxmin，因为有indisvalid
* We also need not set indcheckxmin during a concurrent index build,
* because we won't set indisvalid true until all transactions that care
* about the broken HOT chains or early pruning/vacuuming are gone.
*
* Therefore, this code path can only be taken during non-concurrent
* CREATE INDEX. Thus the fact that heap_update will set the pg_index
* tuple's xmin doesn't matter, because that tuple was created in the
* current transaction anyway. That also means we don't need to worry
* about any concurrent readers of the tuple; no other transaction can see
* it yet.
*/
if ((indexInfo->ii_BrokenHotChain || EarlyPruningEnabled(heapRelation)) &&
!isreindex &&
!indexInfo->ii_Concurrent)
{
Oid indexId = RelationGetRelid(indexRelation);
Relation pg_index;
HeapTuple indexTuple;
Form_pg_index indexForm;
pg_index = table_open(IndexRelationId, RowExclusiveLock);
indexTuple = SearchSysCacheCopy1(INDEXRELID,
ObjectIdGetDatum(indexId));
if (!HeapTupleIsValid(indexTuple))
elog(ERROR, "cache lookup failed for index %u", indexId);
indexForm = (Form_pg_index) GETSTRUCT(indexTuple);
/* If it's a new index, indcheckxmin shouldn't be set ... */
Assert(!indexForm->indcheckxmin);
indexForm->indcheckxmin = true;
CatalogTupleUpdate(pg_index, &indexTuple->t_self, indexTuple);
heap_freetuple(indexTuple);
table_close(pg_index, RowExclusiveLock);
}
可以看到，源码注释里，我们可以得出如下一些结论
如果我们发现任何可能损坏的HOT链，就将索引标记为不可用，直到当前事务低于视界范围。 如果为堆表启用了早期修剪/清理，也会设置此项。 在concurrent create index的时候也不需要设置indcheckxmin，因为有indisvalid控制
而EarlyPruningEnabled，则很清晰了，判断old_snapshot_threshold >= 0，同时判断是否是普通表（即unlogged、临时表）、是否是系统表等。
/*
* Common definition of relation properties that allow early pruning/vacuuming
* when old_snapshot_threshold >= 0.
*/
#define RelationAllowsEarlyPruning(rel) \
( \
RelationIsPermanent(rel) && !IsCatalogRelation(rel) \
&& !RelationIsAccessibleInLogicalDecoding(rel) \
)
#define EarlyPruningEnabled(rel) (old_snapshot_threshold >= 0 && RelationAllowsEarlyPruning(rel))
那么到这，我们可以有一些系统性的结论了：
开启了old_snapshot_threshold之后，会设置indcheckxmin为true 在CIC的时候，不需要设置indcheckxmin，但是也要控制长事务，即使不是同一个对象，也会阻塞索引的创建，可以参考之前的文章，需要预防HOT unsafe的问题 存在HOT Broken chain的时候，会设置indcheckxmin为true reindex的时候，不需要设置indcheckxmin
那什么是broken hot chain？在HOT的README里面有解释：
“Broken HOT Chain：A HOT chain in which the key value for an index has changed.This is not allowed to occur normally but if a new index is created it can happen. In that case various strategies are used to ensure that no transaction for which the older tuples are visible can use the index.
断开的HOT链: 其中索引的键值已更改的HOT链。这是不允许正常发生的，但是如果创建了一个新的索引，它就会发生。在这种情况下，将使用各种策略来确保不会有可见旧元组的事务使用索引。
postgres=# show old_snapshot_threshold ;
old_snapshot_threshold
------------------------
-1
(1 row)
postgres=# create table t1(id int) with (fillfactor=60);
CREATE TABLE
postgres=# insert into t1 values(generate_series(1,100000));
INSERT 0 100000
postgres=# begin;
BEGIN
postgres=*# update t1 set id = 99 where id = 1; ---HOT更新
UPDATE 1
postgres=*# create index on t1(id);
CREATE INDEX
postgres=*# select indcheckxmin from pg_index where indexrelid = 't1_id_idx'::regclass;
indcheckxmin
--------------
t
(1 row)
postgres=*# explain select id from t1 where id = 99;
QUERY PLAN
-----------------------------------------------------
Seq Scan on t1 (cost=0.00..1991.00 rows=1 width=4)
Filter: (id = 99)
(2 rows)
postgres=*# set enable_seqscan to off;
SET
postgres=*# explain select id from t1 where id = 99; ---依旧顺序扫描
QUERY PLAN
------------------------------------------------------------------------
Seq Scan on t1 (cost=10000000000.00..10000001991.00 rows=500 width=4)
Filter: (id = 99)
(2 rows)
postgres=*# commit ;
COMMIT
postgres=# explain select id from t1 where id = 99;
QUERY PLAN
-------------------------------------------------------------------------
Index Only Scan using t1_id_idx on t1 (cost=0.29..8.31 rows=1 width=4)
Index Cond: (id = 99)
(2 rows)
可以看到，发生了 HOT 更新之后，索引在事务内也是无法使用的。
条件不支持
每种索引有自己特定的场景，也有不支持的操作符，这一点我们可以通过查询系统表来判断
postgres=# select a.amname, p.name, pg_indexam_has_property(a.oid,p.name)
from pg_am a,
unnest(array['can_order','can_unique','can_multi_col','can_exclude']) p(name)
where a.amname = 'gin';
amname | name | pg_indexam_has_property
--------+---------------+-------------------------
gin | can_order | f
gin | can_unique | f
gin | can_multi_col | t
gin | can_exclude | f
(4 rows)
postgres=# SELECT
amop.amopopr::regoperator AS opfamily_operator,
amop.amopstrategy
FROM
pg_am am,
pg_opfamily opf,
pg_amop amop
WHERE
opf.opfmethod = am.oid
AND amop.amopfamily = opf.oid
AND am.amname = 'gin'
ORDER BY
amopstrategy;
opfamily_operator | amopstrategy
-----------------------+--------------
&&(anyarray,anyarray) | 1
@@(tsvector,tsquery) | 1
@>(anyarray,anyarray) | 2
@@@(tsvector,tsquery) | 2
<@(anyarray,anyarray) | 3
=(anyarray,anyarray) | 4
@>(jsonb,jsonb) | 7
@>(jsonb,jsonb) | 7
?(jsonb,text) | 9
?|(jsonb,text[]) | 10
?&(jsonb,text[]) | 11
@?(jsonb,jsonpath) | 15
@?(jsonb,jsonpath) | 15
@@(jsonb,jsonpath) | 16
@@(jsonb,jsonpath) | 16
(15 rows)
postgres=# select p.name,
pg_index_column_has_property('t_a_idx'::regclass,1,p.name)
from unnest(array[
'asc','desc','nulls_first','nulls_last','orderable','distance_orderable',
'returnable','search_array','search_nulls'
]) p(name);
name | pg_index_column_has_property
--------------------+------------------------------
asc | t
desc | f
nulls_first | f
nulls_last | t
orderable | t
distance_orderable | f
returnable | t
search_array | t
search_nulls | t
(9 rows)
通过以上查询，可以知道 GIN 索引不支持排序、唯一、排除约束，也不支持大于小于等，因此假如有这些查询，GIN 索引当然会失效了，臣妾做不到啊。
| Name | Description |
|---|---|
clusterable | Can the index be used in a CLUSTER command? |
index_scan | Does the index support plain (non-bitmap) scans? |
bitmap_scan | Does the index support bitmap scans? |
backward_scan | Can the scan direction be changed in mid-scan (to support FETCH BACKWARD on a cursor without needing materialization)? |
| Name | Description |
|---|---|
asc | Does the column sort in ascending order on a forward scan? |
desc | Does the column sort in descending order on a forward scan? |
nulls_first | Does the column sort with nulls first on a forward scan? |
nulls_last | Does the column sort with nulls last on a forward scan? |
orderable | Does the column possess any defined sort ordering? |
distance_orderable | Can the column be scanned in order by a “distance” operator, for example ORDER BY col <-> constant ? |
returnable | Can the column value be returned by an index-only scan? |
search_array | Does the column natively support col = ANY(array) searches? |
search_nulls | Does the column support IS NULL and IS NOT NULL searches? |
不想用
不想用，顾名思义，优化器认为走索引还不如走顺序扫描来得快，因为索引扫描对应的是离散IO，我们可以通过调整 random_page_cost 以告诉优化器，随机IO和顺序IO的比值。看几个例子：
表太小
这个是开发经常容易犯的错误，为什么我建了索引不走呢？还是那句话，优化器认为索引太慢了！
postgres=# create table small_t(id int);
CREATE TABLE
postgres=# create index on small_t (id);
CREATE INDEX
postgres=# insert into small_t values(generate_series(1,100));
INSERT 0 100
postgres=# analyze small_t ;
ANALYZE
postgres=# explain select id from small_t where id = 10;
QUERY PLAN
-------------------------------------------------------
Seq Scan on small_t (cost=0.00..2.25 rows=1 width=4)
Filter: (id = 10)
(2 rows)
postgres=# set enable_seqscan to off;
SET
postgres=# explain select id from small_t where id = 10;
QUERY PLAN
-----------------------------------------------------------------------------------
Index Only Scan using small_t_id_idx on small_t (cost=0.14..8.16 rows=1 width=4)
Index Cond: (id = 10)
(2 rows)
可以看到索引的总成本8.16要比顺序扫描2.25高了4倍左右，优化器当然选择走顺序扫描了。
关联度
参照此图 👆🏻，高效的索引扫描 (绿色部分)，只需扫描几次 (traverse) 就能获取到所需数据，而糟糕的索引扫描往往需要返回大量数据，如图中红色部分，需要多次扫描，每次都要经历树根 → 树干 → 树枝 → 树叶，导致大量的离散IO，因此优化器也会将这个关联度考虑进去。
好的顺序扫描数据更加紧凑，都集中在一块，这样磁头扫过去，只需扫描有限个数据块，就可以获取到大部分所需数据。而糟糕的顺序扫描则数据相对离散，需要扫描大量数据并过滤才能获取到想要的数据，这个也就是我在统计信息里面提到的correlation字段的作用，表示列的物理顺序和逻辑顺序的相关性，相关性越高，走索引扫描的离散块扫描更少，走索引扫描的离散块扫描代价越低。还是老例子，假如现在有一个表如下
testdb=# \d tbl_corr
Table "public.tbl_corr"
Column | Type | Modifiers
----------+---------+-----------
col | text |
col_asc | integer |
col_desc | integer |
col_rand | integer |
data | text |
Indexes:
"tbl_corr_asc_idx" btree (col_asc)
"tbl_corr_desc_idx" btree (col_desc)
"tbl_corr_rand_idx" btree (col_rand)
testdb=# SELECT col,col_asc,col_desc,col_rand FROM tbl_corr;
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)
testdb=# SELECT tablename,attname, correlation FROM pg_stats WHERE tablename = 'tbl_corr';
tablename | attname | correlation
-----------+----------+-------------
tbl_corr | col_asc | 1
tbl_corr | col_desc | -1
tbl_corr | col_rand | 0.125874
(3 rows)
表上有三个索引，其中
col_asc索引是顺序递增的 col_desc索引是倒序递减的 col_rand索引则是随机分布的
因此假如有个SQL需要查询2到4的数据 SELECT * FROM tbl_corr WHERE col_asc BETWEEN 2 AND 4;，对于col_asc，只需要读第一个页面即可
而假如是对随机列进行查询的话，SELECT * FROM tbl_corr WHERE col_rand BETWEEN 2 AND 4;则需要读取所有的页面
因此在索引扫描的时候也会将correlation考虑进去 'table IO cost'=max_IO_cost+indexCorrelation2×(min_IO_cost−max_IO_cost).，更多细节请参考 https://www.interdb.jp/pg/pgsql03.html。
“This way, the index correlation is a statistical correlation that reflects the influence of random access caused by the twist between the index ordering and the physical tuple ordering in the table in estimating the index scan cost.
在PostgreSQL中，我们可以使用cluster命令进行聚簇，对于某些时序类的数据进行范围查询，会有性能提升。
返回的数据集过大
这个也是类似的原理，索引会对应大量的离散IO，选择率这个词想必各位也耳熟能详，就是这个道理。
postgres=# create table big_t(id int);
CREATE TABLE
postgres=# create index on big_t (id);
CREATE INDEX
postgres=# insert into big_t values(generate_series(1,100000));
INSERT 0 100000
postgres=# analyze big_t ;
ANALYZE
postgres=# 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..3300.28 rows=99999 width=4)
Index Cond: (id > 1)
(2 rows)
postgres=# set enable_seqscan to on;
SET
postgres=# 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)
最左原则
索引的最左原则？非也非也，看个栗子👇🏻
postgres=# create table t(id int,info text);
CREATE TABLE
postgres=# create index on t(id,info);
CREATE INDEX
postgres=# insert into t select n,md5(random()::text) from generate_series(1,100000) as n;
INSERT 0 100000
postgres=# analyze t;
ANALYZE
postgres=# 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)
postgres=# 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，基本也需要访问整棵索引树了。
postgres=# set enable_seqscan to off;
SET
postgres=# 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)
postgres=# \d t
Table "public.t"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | |
info | text | | |
Indexes:
"t_id_info_idx" btree (id, info)
另外不是所有这种例子都不会选择走索引，我曾经看过greenplum的一篇文章，让我的印象十分深刻，👉🏻 《巧用复合索引，优化查询性能》，究其原因，也还是成本cost的比较，让优化器选择了复合索引。
“由查询计划可以看到，尽管id并不是索引(score,class,id)的某一个前缀，但是依然可以使用到这个复合索引。笔者早年在Mysql上具有多年工作经验，深知Mysql索引的最左前缀原则，因此对于Greenplum中的这个索引优化效果非常惊奇，那是不是查询计划有误呢？我们来对比一下这个查询分别使用顺序扫描和索引扫描的实际运行时间...
冗余索引
顾名思义，存在同类型功能相同的索引，我们可以使用如下SQL检查：
postgres=# \d t1
Table "public.t1"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | |
Indexes:
"t1_id_idx" btree (id)
"t1_id_idx1" btree (id)
"t1_id_idx2" btree (id)
postgres=# SELECT
indrelid::regclass AS TableName
,array_agg(indexrelid::regclass) AS Indexes
FROM pg_index
GROUP BY
indrelid
,indkey
HAVING COUNT(*) > 1;
tablename | indexes
-----------+-----------------------------------
t1 | {t1_id_idx,t1_id_idx1,t1_id_idx2}
(1 row)
postgres=# SELECT pg_size_pretty(sum(pg_relation_size(idx))::bigint) as size,
(array_agg(idx))[1] as idx1, (array_agg(idx))[2] as idx2,
(array_agg(idx))[3] as idx3, (array_agg(idx))[4] as idx4
FROM (
SELECT indexrelid::regclass as idx, (indrelid::text ||E'\n'|| indclass::text ||E'\n'|| indkey::text ||E'\n'||
coalesce(indexprs::text,'')||E'\n' || coalesce(indpred::text,'')) as key
FROM pg_index) sub
GROUP BY key HAVING count(*)>1
ORDER BY sum(pg_relation_size(idx)) DESC;
size | idx1 | idx2 | idx3 | idx4
---------+-----------+------------+------------+------
6624 kB | t1_id_idx | t1_id_idx1 | t1_id_idx2 |
(1 row)
postgres=# SELECT s.schemaname,
s.relname AS tablename,
s.indexrelname AS indexname,
pg_relation_size(s.indexrelid) AS index_size
FROM pg_catalog.pg_stat_user_indexes s
JOIN pg_catalog.pg_index i ON s.indexrelid = i.indexrelid
WHERE s.idx_scan = 0 -- has never been scanned
AND 0 <>ALL (i.indkey) -- no index column is an expression
AND NOT i.indisunique -- is not a UNIQUE index
AND NOT EXISTS -- does not enforce a constraint
(SELECT 1 FROM pg_catalog.pg_constraint c
WHERE c.conindid = s.indexrelid)
AND NOT EXISTS -- is not an index partition
(SELECT 1 FROM pg_catalog.pg_inherits AS inh
WHERE inh.inhrelid = s.indexrelid)
ORDER BY pg_relation_size(s.indexrelid) DESC;
schemaname | tablename | indexname | index_size
------------+-----------+----------------+------------
public | t | t_id_idx | 92848128
public | test | test_info_idx | 7946240
public | test | test_id_idx | 2260992
public | small_t | small_t_id_idx | 16384
(4 rows)
更多SQL可以参考wiki：https://wiki.postgresql.org/wiki/Index_Maintenance
优化器刺客
没错，还是limit！俗称优化器刺客，这是一个很 tricky 的问题，你可能会发现比如像 select * from abc where col_1 = 5 limit 1; 没有走索引，但是却发现 select * from abc where col_1 = 5;使用了索引，当优化器认为 pg_stats 中 col_1 列在表中有足够多的随机值时，就会发生这种情况，因为优化器认为从数据库中获取所需数量的连续块的总成本将小于先获取索引块然后再获取相应的数据块的成本，优化器认为数据分布均匀，只需扫描有限数据即可获得所需数据，中止继续扫描，但是一旦数据分布不均，全部挤在了一坨，就会很糟糕，最糟糕的情况莫过于需要扫描大量的数据。
关于limit导致问题的案例太多了，还是那个来自ken师傅的经典案例：一个有趣的SQL优化案例，优化器刺客。
糟糕的统计信息
顾名思义，过时的、糟糕的统计信息会让优化器产生误判，甚至没有统计信息，比如修改字段长度、类型后，需要做analyze手动收集统计信息，不然会选择默认选择率。
小结
知其然，知其所以然。对于一条SQL，优化器判断能否走索引是需要经过多方面权衡和比较的，另外对于broken hot chain和old_snapshot_threshold，多加小心长事务！
参考
https://github.com/digoal/blog/blob/master/202211/20221111_02.md
https://www.gojek.io/blog/the-case-s-of-postgres-not-using-index
https://www.pgmustard.com/blog/why-isnt-postgres-using-my-index