PostgreSQL中主键索引为什么不能被查询利用到？---索引使用情况一例

有朋友A问：

where  username in ('张三','李四');，username会不会走索引呢？

有朋友B、C、D等回答：

走不走索引和很多东西有关系的，走索引也不一定就效率高。

in 语句是走索引的， like '%xxx%'这种模糊搜索才不走索引。

其实，A朋友的问题是这样的，“IN谓词指定的有主键列的查询没有使用索引扫描”，如图一：

 所以A朋友潜在的问题是：

   为什么有主键索引的列在进行IN谓词查询（非IN子查询）的时候，索引没有使用上？

 

分析原因如下：

解释一：

A朋友的查询语句，其实涉及的是“等价谓词重写技术”和“索引的使用”2个话题。

看上面的表，PG是不支持IN谓词重写的。但是，name列上如果存在索引，则“可能”利用到索引。

如果有索引列，pg是可以支持利用索引进行优化的，但是对于like和IN谓词，“等价谓词重写”的优化技术，pg不支持”。

比如：

create table AA (a int unique, b varchar(10) unique, c int);

insert into aa values(1, 'a', 1);

insert into aa values(2, 'b', 2);

insert into aa values(3, 'c', 3);

explain select b from aa where b in ('a','ab');

可以得到如下的查询执行计划：

test=# explain select b from aa where b in ('a','ab');

                              QUERY PLAN

-----------------------------------------------------------------------

 Bitmap Heap Scan on aa  (cost=8.52..13.86 rows=2 width=38)

   Recheck Cond: ((b)::text = ANY ('{a,ab}'::text[]))

   ->  Bitmap Index Scan on aa_b_key  (cost=0.00..8.52 rows=2 width=0)

         Index Cond: ((b)::text = ANY ('{a,ab}'::text[]))

(4 行记录)

这表明索引是可以被使用到的。所以PostgreSQL尽管不支持IN谓词等价重写技术，但是索引还是能利用上的。

 

这时，有另外一个朋友给出解释：

如果那个情况发生了，证明你得做vacuum了，你的数据库表的统计值不对，导致pg的优化器做出了错误的判断。

 

这个问题的原因，是不是这样的呢？为了进一步定位问题，给出如下建议：

1.         name是建立了唯一索引的。

2.         如果有唯一索引，则使用索引的可能性很大。

3.         可以explain看一下查询执行计划。

4.         查查表结构、SQL语句、执行计划等等。

5.         通常觉得应该用而没有用上索引的，多是自己的语句存在小问题但不易发现。

6.         不管最终用in会不会走索引-----如果不用索引且数据量大一些，则慢。

7.         可以先看看你的查询执行计划，是否用了索引

8.         如果没有用索引，可以禁掉顺序扫描然后再执行，看查询执行计划是否使用了索引且花费比上一次低。

9.         如果那个情况发生了，证明你得做vacuum了，你的数据库表的统计值不对，导致pg的优化器做出了错误的判断。------这是种可能

10.     但放在主键索引上（前面A兄弟说的情况）下，vacuum执行后有效的可能性有、但不是很大，可以试试

 

其他朋友再问：

如果关闭顺序扫描后，执行计划使用索引。且cost低于顺序扫描。该怎么办呢？

回答：

1.         首先，这种情况（A朋友这起存在主键索引的情况）发生的概率（即不能利用索引的概率）不高。

2.         其次，如果真的发生了，可以在执行本条查询前，先禁掉seqscan，执行后再打开（打开的目的----保证不影响其他SQL）

3.         第三，长期观察这样的情况是否一定发生？可能另外有潜在的原因，需要持续跟踪

 

分析到此，其实最可能的原因，是索引根本没有被使用到。

 

于是，有朋友继续分析，如下图：

 从图上可以看出，索引受到字符集中的collation的影响（d数据库的COLLATE的值是zh_CN.UTF8，不是C），根本没有被使用。

 

所以，问题的原因是：

要查询的数据类型和索引的数据类型不匹配，导致索引没有被使用。

解决问题的方式如下，如图二：

删除原先的索引，重新创建索引的语句为：

CREATE INDEX T3_NAME_I ON t3_name (name text_pattern_ops);

然后再执行同样的查询，从查询执行计划看，索引被使用。

 

 查阅PG的文档，PG是这样解释这个问题的：

The operator classes text_pattern_ops, varchar_pattern_ops, and bpchar_pattern_ops support B-tree indexes on the types text, varchar, and char respectively. The difference from the default operator classes is that the values are compared strictly character by character rather than according to the locale-specific collation rules. This makes these operator classes suitable for use by queries involving pattern matching expressions (LIKE or POSIX regular expressions) when the database does not use the standard “C” locale. As an example, you might index a varchar column like this:

CREATE INDEX test_index ON test_table (col varchar_pattern_ops);

 

至此，这个问题得到解决。

 

另外，有朋友提到：

我觉得in like 只要有索引应该支持吧。其实有时觉得数据库这块的处理好有问题，原则上应该使用，如果能让用户指定索引使用方式就好啦

其实：

多数情况下，优化器已经能优化很多东西了，做得比较成熟了，所以交给优化器优化适用面更广一些。

个别时候，能指定hint则好，但使用者必须完全明确自己定义好的方式就是好的。

再另外，提到了“等价谓词查询”技术，给大家贴了张图，如图三：

 

详情可以参见《数据库查询优化器的艺术》第8章对于PostgreSQL查询优化器的“等价谓词重写”技术的分析：