PostgreSQL 多字段任意组合搜索的性能

点击有惊喜

标签

PostgreSQL , 多字段搜索 , 任意字段搜索

---

背景

PostgreSQL 多字段，任意组合搜索，有三种优化手段：

1、gin索引（支持任意字段组合的查询）

《宝剑赠英雄 - 任意组合字段等效查询, 探探PostgreSQL多列展开式B树 (GIN)》

2、bloom索引（支持任意只读组合的等值查询）

《PostgreSQL 9.6 黑科技 bloom 算法索引，一个索引支撑任意列组合查询》

3、每个单列btree索引（支持任意字段组合的查询）

《PostgreSQL bitmapAnd, bitmapOr, bitmap index scan, bitmap heap scan》

例子

create table test(c1 int, c2 int, c3 int, c4 int, c5 int);  

bloom, gin, multi-btree几种索引创建方法

1、bloom

postgres=# create extension bloom ;  CREATE EXTENSION  postgres=# create index idx_test12_1 on test12 using bloom (c1,c2,c3,c4,c5);  CREATE INDEX  postgres=# explain select * from test12 where c1=1;                                   QUERY PLAN                                   ----------------------------------------------------------------------------   Bitmap Heap Scan on test12  (cost=13.95..20.32 rows=8 width=20)     Recheck Cond: (c1 = 1)     ->  Bitmap Index Scan on idx_test12_1  (cost=0.00..13.95 rows=8 width=0)           Index Cond: (c1 = 1)  (4 rows)  postgres=# explain select * from test12 where c1=1 and c2=1;                                   QUERY PLAN                                   ----------------------------------------------------------------------------   Bitmap Heap Scan on test12  (cost=18.20..19.42 rows=1 width=20)     Recheck Cond: ((c1 = 1) AND (c2 = 1))     ->  Bitmap Index Scan on idx_test12_1  (cost=0.00..18.20 rows=1 width=0)           Index Cond: ((c1 = 1) AND (c2 = 1))  (4 rows)  postgres=# explain select * from test12 where c1=1 or c2=1;                                      QUERY PLAN                                      ----------------------------------------------------------------------------------   Bitmap Heap Scan on test12  (cost=27.91..38.16 rows=17 width=20)     Recheck Cond: ((c1 = 1) OR (c2 = 1))     ->  BitmapOr  (cost=27.91..27.91 rows=17 width=0)           ->  Bitmap Index Scan on idx_test12_1  (cost=0.00..13.95 rows=8 width=0)                 Index Cond: (c1 = 1)           ->  Bitmap Index Scan on idx_test12_1  (cost=0.00..13.95 rows=8 width=0)                 Index Cond: (c2 = 1)  (7 rows)  

2、gin

postgres=# create extension btree_gin;  CREATE EXTENSION  postgres=# create index idx_test12_1 on test12 using gin (c1,c2,c3,c4,c5);  CREATE INDEX  postgres=# explain select * from test12 where c1=1 or c2=1;                                     QUERY PLAN                                      ---------------------------------------------------------------------------------   Bitmap Heap Scan on test12  (cost=4.94..15.19 rows=17 width=20)     Recheck Cond: ((c1 = 1) OR (c2 = 1))     ->  BitmapOr  (cost=4.94..4.94 rows=17 width=0)           ->  Bitmap Index Scan on idx_test12_1  (cost=0.00..2.46 rows=8 width=0)                 Index Cond: (c1 = 1)           ->  Bitmap Index Scan on idx_test12_1  (cost=0.00..2.46 rows=8 width=0)                 Index Cond: (c2 = 1)  (7 rows)    postgres=# explain select * from test12 where c1=1 and c2=1;                                  QUERY PLAN                                   ---------------------------------------------------------------------------   Bitmap Heap Scan on test12  (cost=3.60..4.82 rows=1 width=20)     Recheck Cond: ((c1 = 1) AND (c2 = 1))     ->  Bitmap Index Scan on idx_test12_1  (cost=0.00..3.60 rows=1 width=0)           Index Cond: ((c1 = 1) AND (c2 = 1))  (4 rows)  

3、multi-btree

postgres=# drop index idx_test12_1 ;  DROP INDEX  postgres=# create index idx_test12_1 on test12 using btree(c1);  CREATE INDEX  postgres=# create index idx_test12_2 on test12 using btree(c2);  CREATE INDEX  postgres=# create index idx_test12_3 on test12 using btree(c3);  CREATE INDEX  postgres=# create index idx_test12_4 on test12 using btree(c4);  CREATE INDEX  postgres=# create index idx_test12_5 on test12 using btree(c5);  CREATE INDEX    postgres=# explain select * from test12 where c1=1 and c2=1;                                     QUERY PLAN                                      ---------------------------------------------------------------------------------   Bitmap Heap Scan on test12  (cost=3.08..4.29 rows=1 width=20)     Recheck Cond: ((c2 = 1) AND (c1 = 1))     ->  BitmapAnd  (cost=3.08..3.08 rows=1 width=0)           ->  Bitmap Index Scan on idx_test12_2  (cost=0.00..1.41 rows=8 width=0)                 Index Cond: (c2 = 1)           ->  Bitmap Index Scan on idx_test12_1  (cost=0.00..1.41 rows=8 width=0)                 Index Cond: (c1 = 1)  (7 rows)    postgres=# explain select * from test12 where c1=1 or c2=1;                                     QUERY PLAN                                      ---------------------------------------------------------------------------------   Bitmap Heap Scan on test12  (cost=2.83..13.09 rows=17 width=20)     Recheck Cond: ((c1 = 1) OR (c2 = 1))     ->  BitmapOr  (cost=2.83..2.83 rows=17 width=0)           ->  Bitmap Index Scan on idx_test12_1  (cost=0.00..1.41 rows=8 width=0)                 Index Cond: (c1 = 1)           ->  Bitmap Index Scan on idx_test12_2  (cost=0.00..1.41 rows=8 width=0)                 Index Cond: (c2 = 1)  (7 rows)  

gin, bloom, btree bitmap scan的性能如何呢？

1600个列的宽表，任意字段组合搜索性能

1、建表

postgres=# do language plpgsql $$  declare    sql text;  begin    sql := 'create table test1 (';    for i in 1..1600 loop      sql := sql||' c'||i||' int2 default random()*100,';    end loop;    sql := rtrim(sql,',');    sql := sql||')';    execute sql;         for i in 1..1600 loop       execute 'create index idx_test1_'||i||' on test1 (c'||i||')';     end loop;  end;  $$;  DO  

2、写入测试数据

postgres=# insert into test1 (c1)  select generate_series(1,10000);  INSERT 0 10000  

3、测试脚本

vi test.sql    \set c2 random(1,100)  \set c3 random(1,100)  \set c4 random(1,100)  \set c5 random(1,100)  \set c6 random(1,100)  \set c7 random(1,100)  select c1600 from test1 where c2=:c2 and c3=:c3 and c4=:c4 or (c5=:c5 and c6=:c6 and c7=:c7);  

4、测试

pgbench -M prepared -n -r -P 1 -f ./test.sql -c 64 -j 64 -T 120  

5、性能

progress: 33.0 s, 208797.8 tps, lat 0.307 ms stddev 0.016  progress: 34.0 s, 208516.0 tps, lat 0.307 ms stddev 0.032  progress: 35.0 s, 208574.0 tps, lat 0.307 ms stddev 0.050  progress: 36.0 s, 208858.2 tps, lat 0.306 ms stddev 0.013  progress: 37.0 s, 208686.8 tps, lat 0.307 ms stddev 0.043  progress: 38.0 s, 208764.2 tps, lat 0.307 ms stddev 0.013  

注意，使用prepared statement，可以减少硬解析，提高性能。

从测试来看，任意字段的搜索，可以达到0.3毫秒的响应。

点击有惊喜