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

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.chinaunix.net/uid-20726500-id-5099605.html

6. 其它问题

在这次测试中还发现一个问题，走btree-gin索引的执行计划的代价估计值过小，严重偏离实际，所以如果同时定义了btree-gin索引和btree索引，优化器是一定会选择btree-gin的。