PostgreSQL 的部分索引

高峰之巅

熟悉数据库的大家知道，一个属性要不要建立btree索引，判断条件是其键值分布是否够离散，比如主键、唯一键，可以建立索引。如果这个属性有大量重复的值，则建立索引没有意义。

举个例子：一个表含有sex(性别)字段，只包含M、F两个值，假设这张表有10000条记录，可想而知，M或F各占5000左右，此时，你即使在该性别字段建立索引，查询语句select ... where sex='M'也是利用不到此索引的，因为该索引中含有大量重复的M值，与其去搜索索引，还不如来个全盘扫描。所以在有大量重复键值的字段建立索引没有什么意义。

现实例子中，常会碰到键值分布不均匀的属性，如表test一个名为FLAG的属性，有A、B、C三个值，其中值为A的记录占95%，值为B的占3%，C占2%。在FLAG上建立索引，搜索FLAG=‘B’或‘C’可利用到此索引，而搜索FLAG=‘A’则因有大量的重复值而利用不到此索引。也就是说此索引有95%的内容是无效的，白白浪费了存储等资源。

PostgreSQL有种索引，叫Partial Index(部分索引)可以很好的解决以上问题， 它是在表的纵向子集上建立索引，可以认为是带where表达式的索引。
如以上例子，我们在FLAG上建立部分索引flag_index：
create index flag_index on test ( flag ) where flag='B' or flag='C'
这个索引只包含B和C值，不包含A值，大大减少了存储空间，提高了运行效率。

只有匹配where 表达式的查询语句才可利用到此部分索引，如查询语句
select ...where flag='B'   。。。。可利用到索引
select ...where flag='C'   。。。。可利用到索引
select ...where flag='A'   。。。。利用不到索引。

注意一点：由于PostgreSQL在决定是否使用索引，是在SQL语句的分析阶段，而不是在运行阶段，假如在分析阶段还无法确定其值的语句，它无法匹配到where语句，也是无法用到索引的，即使运行结果是匹配到where 语句。

如一个例子 select .... where flag = FUNC(.....)  ， func是个很复杂的表达式，这个表达式结果只有设计者知道只返回B值，然而在分析阶段，Postgresql算不出其值，此时postgresql也是无法利用其索引，结果发现此语句运行得很慢，explain一下发现是全表扫描。

解决的方法是显式地加上索引中的where 语句：
select .... where flag = FUNC(.....)  and FLAG='B'  (假设FUNC只返回B值）

类似的情况还有某些JOIN、LEFT JOIN的右表，请显式的加上索引中的where 语句。