PostgreSQL btree-gin用于范围查询的奇怪现象

kient88

1. 现象
之前做多字段索引测试的时候发现一个奇怪的现象，btree-gin提供的gin索引在处理1个比较操作的范围查询时性能还行，但处理有2个比较操作的范围查询时性能就很糟糕了。下面是例子。
2. 测试环境
测试环境在一个PC的虚拟机上
宿主机
- CPU:AMD Athlon II X4 640 3.0GHz
- MEM:6G
- OS:Win7 64bit
- 虚拟机所在存储:Apacer A S510S 128GB
虚拟机
- CPU：4 core
- MEM: 2G
- OS:CentOS release 6.5 (Final)
- PostgreSQL:9.4.2(shared_buffers = 128MB,其它都是默认值)
3. 测试
3.1 准备测试数据

chenhj=# create table tb1(c1 int,c2 int);
CREATE TABLE
chenhj=# insert into tb1 select round(random()*100),round(random()*1000) from generate_series(1,10000000);
INSERT 0 10000000
chenhj=# select pg_size_pretty(pg_table_size('tb1'));
pg_size_pretty
----------------
346 MB
(1 row)
3.2 创建c1+c2多字段gin索引

chenhj=# create extension btree_gin;
CREATE EXTENSION
chenhj=# create index tb1_idx_c1c2gin on tb1 using gin(c1,c2);
CREATE INDEX
chenhj=# select pg_size_pretty(pg_relation_size('tb1_idx_c1c2gin'));
pg_size_pretty
----------------
47 MB
(1 row)
3.3 只有1个比较操作的范围查询

chenhj=# explain (analyze,buffers) select count(*) from tb1 where c1>97 and c2=999;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Aggregate  (cost=1790.59..1790.60 rows=1 width=0) (actual time=72.172..72.172 rows=1 loops=1)
Buffers: shared hit=341
->  Bitmap Heap Scan on tb1  (cost=750.82..1789.90 rows=275 width=0) (actual time=71.794..72.138 rows=278 loops=1)
Recheck Cond: ((c1 > 97) AND (c2 = 999))
Heap Blocks: exact=278
Buffers: shared hit=341
->  Bitmap Index Scan on tb1_idx_c1c2gin  (cost=0.00..750.75 rows=275 width=0) (actual time=71.744..71.744 rows=278 loops=1)
Index Cond: ((c1 > 97) AND (c2 = 999))
Buffers: shared hit=63
Planning time: 0.257 ms
Execution time: 72.234 ms
(11 rows)
3.4 有2个比较操作的范围查询

chenhj=# explain (analyze,buffers) select count(*) from tb1 where c1>97 and c1<=100 and c2=999;
QUERY PLAN                                                                 
-------------------------------------------------------------------------------------------------------------------------------------------
Aggregate  (cost=2523.96..2523.97 rows=1 width=0) (actual time=1459.645..1459.645 rows=1 loops=1)
Buffers: shared hit=2347
->  Bitmap Heap Scan on tb1  (cost=1483.50..2523.28 rows=275 width=0) (actual time=1459.234..1459.599 rows=278 loops=1)
Recheck Cond: ((c1 > 97) AND (c1 <= 100) AND (c2 = 999))
Heap Blocks: exact=278
Buffers: shared hit=2347
->  Bitmap Index Scan on tb1_idx_c1c2gin  (cost=0.00..1483.43 rows=275 width=0) (actual time=1459.175..1459.175 rows=278 loops=1)
Index Cond: ((c1 > 97) AND (c1 <= 100) AND (c2 = 999))
Buffers: shared hit=2069
Planning time: 0.178 ms
Execution time: 1460.071 ms
(11 rows)
因为在构造数据时，c1的最大值就是100，所以上述两个查询匹配的数据是完全相同的，但结果却相差很大。
4. 和btree索引的比较
建一个对等的btree(c1,c2)索引，然后作个比较。

chenhj=# drop index tb1_idx_c1c2gin;
DROP INDEX
chenhj=# create index tb1_idx_c1c2gin on tb1 using btree(c1,c2);
CREATE INDEX
chenhj=# select pg_size_pretty(pg_relation_size('tb1_idx_c1c2btree'));
pg_size_pretty
----------------
214 MB
(1 row)
chenhj=# explain (analyze,buffers) select count(*) from tb1 where c1>97 and c2=999;
QUERY PLAN                                                               
------------------------------------------------------------------------------------------------------------------------------------------
Aggregate  (cost=7081.60..7081.61 rows=1 width=0) (actual time=10.740..10.740 rows=1 loops=1)
Buffers: shared hit=962
->  Index Only Scan using tb1_idx_c1c2btree on tb1  (cost=0.43..7080.91 rows=275 width=0) (actual time=3.946..10.684 rows=278 loops=1)
Index Cond: ((c1 > 97) AND (c2 = 999))
Heap Fetches: 278
Buffers: shared hit=962
Planning time: 0.104 ms
Execution time: 10.780 ms
(8 rows)

chenhj=# explain (analyze,buffers) select count(*) from tb1 where c1>97 and c1<=100 and c2=999;
QUERY PLAN                                                               
------------------------------------------------------------------------------------------------------------------------------------------
Aggregate  (cost=7794.04..7794.05 rows=1 width=0) (actual time=13.119..13.121 rows=1 loops=1)
Buffers: shared hit=962
->  Index Only Scan using tb1_idx_c1c2btree on tb1  (cost=0.43..7793.36 rows=275 width=0) (actual time=5.319..13.072 rows=278 loops=1)
Index Cond: ((c1 > 97) AND (c1 <= 100) AND (c2 = 999))
Heap Fetches: 278
Buffers: shared hit=962
Planning time: 0.133 ms
Execution time: 13.255 ms
(8 rows)
btree在处理比较查询时效率明显比btree-gin好的多。
5. 原因
在gin处理"c1>97 and c1<=100"时，将其分解为两个部分匹配，"c1>97"和"c1<=100",然后再把它们的结果通过bitmap与的逻辑取交集。 由于"c1<=100"匹配了所有记录，也就要为所有记录做bitmap与操作，所以效率很低。 btree索引则不同，btree理解比较操作符的含义，因此做了优化，通过一个(97,100]的很窄的范围扫描就能搞定。
关于btree-gin如何处理比较操作，可以参考 http://blog.iyunv.com/uid-20726500-id-5099605.html
6. 其它问题
在这次测试中还发现一个问题，走btree-gin索引的执行计划的代价估计值过小，严重偏离实际，所以如果同时定义了btree-gin索引和btree索引，优化器是一定会选择btree-gin的。