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上建立的(部分)索引。