PostgreSql 部分索引导致ORDER BY扫全表

jmton

　　pgsql挺好玩的，可以创建部分索引



部分索引 是建立在一个表的子集上的索引；该子集是由一个条件表达式定义的(叫做部分索引的谓词 )。该索引只包含表中那些满足这个谓词的行。部分索引是一个特殊的特性，但是在某些场合很有用。


部分索引的主要动机是为了避免对普通数值(大量重复的数值)建立索引。因为在普通数值上的查询就算使用索引也没什么好处，那么还不如从索引中剔除这些大量重复的行。这样可以减小索引尺寸，提高那些真正使用索引的查询的速度。同时它也能提高更新操作的速度，因为不是所有情况都需要更新索引。
　　今天在折腾中发现如果对col使用部分索引在ORDER BY col时可能导致全表扫描该字段。pgsql v8.1, 30w条记录。
　　1、正常创建索引，order by时使用该索引

CREATE INDEX ftype on table (fee_type);
explain SELECT * FROM test_fee ORDER BY fee_type LIMIT 10
Limit  (cost=0.00..0.53 rows=10 width=259)
->  Index Scan using ftype on test_fee  (cost=0.00..15882.07 rows=301596 width=259)
Time: 0.015s
explain analyze SELECT * FROM test_fee ORDER BY fee_type  LIMIT 10
Limit  (cost=0.00..0.53 rows=10 width=259) (actual time=0.049..0.139 rows=10 loops=1)
->  Index Scan using ftype on test_fee  (cost=0.00..15881.26 rows=301596 width=259)
(actual time=0.042..0.077 rows=10 loops=1)
Total runtime: 0.263 ms

 　　2、创建部分索引，结果全表扫描

CREATE INDEX ftype ON table (fee_type) WHERE fee_type > 0;

SELECT * FROM test_fee ORDER BY fee_type LIMIT  10
Limit  (cost=18217.34..18217.37 rows=10 width=259)
->  Sort  (cost=18217.34..18971.33 rows=301596 width=259)
Sort Key: fee_type
->  Seq Scan on test_fee  (cost=0.00..11699.96 rows=301596 width=259)
Time: 0.015s

explain analyze SELECT * FROM test_fee ORDER BY fee_type LIMIT 10
Limit  (cost=18217.34..18217.37 rows=10 width=259) (actual time=1994.620..1994.701 rows=10 loops=1)
->  Sort  (cost=18217.34..18971.33 rows=301596 width=259) (actual time=1994.611..1994.637 rows=10 loops=1)
Sort Key: fee_type
Sort Method:  top-N heapsort  Memory: 19kB
->  Seq Scan on test_fee  (cost=0.00..11699.96 rows=301596 width=259) (actual time=0.060..1022.666 rows=301596 loops=1)
Total runtime: 1994.863 ms

 　　即使fee_type值都大于0（符合部分索引条件），结果一样。
　　总结：对使用了部分索引的栏目进行排序时pgsql可能会全表扫描导致性能骤降。除非你在WHERE条件中排除未被索引值的row，比如创建了一个 (fid > 9) 的部分索引，那么条件为 “ WHERE fid > 9 ORDER BY fid” 或 “ WHERE fid > 10 ORDER BY fid”就会使用fid上建立的（部分）索引。