btree-gin用于范围查询的奇怪现象
##1. 现象 之前做多字段索引测试的时候发现一个奇怪的现象，bree-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的。