PgSQL · 应用案例 · 任意字段组合查询__《PostgreSQL 设计优化case - 大宽表任意字段组合查询索引如何选择(btree, gin, rum) - (含单个索引列数超过32列的方法)》 https://github.com/digoal/blog/blob/master/201808/20180803_01.md 《PostgreSQL 任意字段数组合 AND\OR 条件，指定返回结果条数，构造测试数据算法举例》 https://github.com/digoal/blog/blob/master/201809/20180905_03.md 《PostgreSQL ADHoc(任意字段组合)查询(rums索引加速)

### **背景**
#### 《PostgreSQL 设计优化case - 大宽表任意字段组合查询索引如何选择(btree, gin, rum) - (含单个索引列数超过32列的方法)》
#### https://github.com/digoal/blog/blob/master/201808/20180803_01.md
#### 《PostgreSQL 任意字段数组合 AND\OR 条件，指定返回结果条数，构造测试数据算法举例》
#### bhttps://github.com/digoal/blog/blob/master/201809/20180905_03.md
#### 《PostgreSQL ADHoc(任意字段组合)查询(rums索引加速) - 非字典化，普通、数组等组合字段生成新数组》
#### https://github.com/digoal/blog/blob/master/201805/20180518_02.md
#### 《PostgreSQL 实践 - 实时广告位推荐 2 (任意字段组合、任意维度组合搜索、输出TOP-K)》
#### https://github.com/digoal/blog/blob/master/201804/20180424_04.md
#### 《PostgreSQL 实践 - 实时广告位推荐 1 (任意字段组合、任意维度组合搜索、输出TOP-K)》
#### https://github.com/digoal/blog/blob/master/201804/20180420_03.md
#### 《PostgreSQL ADHoc(任意字段组合)查询 与 字典化 (rum索引加速) - 实践与方案1》
#### https://github.com/digoal/blog/blob/master/201802/20180228_01.md
#### 《PostgreSQL 如何高效解决 按任意字段分词检索的问题 - case 1》
#### https://github.com/digoal/blog/blob/master/201607/20160725_05.md
#### 《HTAP数据库 PostgreSQL 场景与性能测试之 20 - (OLAP) 用户画像圈人场景 - 多个字段任意组合条件筛选与透视》
#### https://github.com/digoal/blog/blob/master/201711/20171107_21.md
#### 《PostgreSQL 多字段任意组合搜索的性能》
#### https://github.com/digoal/blog/blob/master/201711/20171102_01.md
#### 1亿记录，128个字段，任意字段组合查询。性能如何？
#### PG凭什么可以搞定大数据量的任意字段组合实时搜索？
#### 《PostgreSQL 并行计算解说 汇总》
#### https://github.com/digoal/blog/blob/master/201903/20190319_01.md
#### 《PostgreSQL 9种索引的原理和应用场景》
#### https://github.com/digoal/blog/blob/master/201706/20170627_01.md

### **例子**
#### 1、测试表
```
do language plpgsql $$  
declare  
  sql text;  
begin  
  sql := 'create unlogged table test(id serial primary key,';  
  for i in 1..64 loop  
    sql := sql||' c'||i||' int default random()*100,';  
  end loop;  
  for i in 65..128 loop  
    sql := sql||' c'||i||' int default random()*1000000,';  
  end loop;  
  sql := rtrim(sql,',');  
  sql := sql||')';  
  execute sql;  
end;  
$$;
```

#### 2、写入1亿数据
```
vi test.sql  
insert into test (c1) select random()*100 from generate_series(1,100);

nohup pgbench -M prepared -n -r -P 1 -f ./test.sql -c 50 -j 50 -t 20000 >/dev/null 2>&1 &
```

postgres=# select count(*) from test;  
   count     
-----------  
 100000000  
(1 row)
```

#### 4、高效率创建索引
```
vi idx.sql

vacuum (analyze,verbose) test;  
set maintenance_work_mem='8GB';  
set max_parallel_workers=128;  
set max_parallel_workers_per_gather=32;  
set min_parallel_index_scan_size=0;  
set min_parallel_table_scan_size=0;  
set parallel_setup_cost=0;  
set parallel_tuple_cost=0;  
set max_parallel_maintenance_workers=16;  
alter table test set (parallel_workers=64);

do language plpgsql $$  
declare  
  sql text;  
begin  
  for i in 1..128 loop  
    execute format('create index idx_test_%s on test (c%s) %s', i, i, 'tablespace tbs_8001');  
  end loop;  
end;  
$$;

vacuum (analyze,verbose) test;

nohup psql -f ./idx.sql >/dev/null 2>&1 &
```

### **写入性能如何**
#### 当前有129个索引，写入性能如何？
#### 9505行/s。
```
transaction type: ./test.sql
scaling factor: 1
query mode: prepared
number of clients: 24
number of threads: 24
duration: 120 s
number of transactions actually processed: 11433
latency average = 252.195 ms
latency stddev = 70.089 ms
tps = 95.054689 (including connections establishing)
tps = 95.058210 (excluding connections establishing)
statement latencies in milliseconds:
       252.179  insert into test (c1) select random()*100 from generate_series(1,100);
```
#### 瓶颈，磁盘读写5.5GB/s。
```
Total DISK READ :     207.91 K/s | Total DISK WRITE :       3.54 G/s  
Actual DISK READ:     207.91 K/s | Actual DISK WRITE:    2015.64 M/s  
  TID  PRIO  USER     DISK READ  DISK WRITE  SWAPIN     IO>    COMMAND   
55887 be/4 digoal     15.40 K/s  158.54 M/s  0.00 %  1.05 % postgres: postgres postgres [local] INSERT  
55872 be/4 digoal      7.70 K/s  157.62 M/s  0.00 %  0.84 % postgres: postgres postgres [local] INSERT  
55886 be/4 digoal     23.10 K/s  158.78 M/s  0.00 %  0.78 % postgres: postgres postgres [local] INSERT  
55897 be/4 digoal      7.70 K/s  158.79 M/s  0.00 %  0.75 % postgres: postgres postgres [local] INSERT  
55889 be/4 digoal      0.00 B/s  158.72 M/s  0.00 %  0.69 % postgres: postgres postgres [local] INSERT  
55894 be/4 digoal      0.00 B/s  157.25 M/s  0.00 %  0.69 % postgres: postgres postgres [local] INSERT  
55888 be/4 digoal      7.70 K/s  136.26 M/s  0.00 %  0.68 % postgres: postgres postgres [local] INSERT  
55885 be/4 digoal      7.70 K/s  143.24 M/s  0.00 %  0.67 % postgres: postgres postgres [local] INSERT  
55890 be/4 digoal      0.00 B/s  159.07 M/s  0.00 %  0.67 % postgres: postgres postgres [local] INSERT  
55865 be/4 digoal     15.40 K/s  158.27 M/s  0.00 %  0.65 % postgres: postgres postgres [local] INSERT  
55900 be/4 digoal      7.70 K/s  151.00 M/s  0.00 %  0.64 % postgres: postgres postgres [local] INSERT  
55891 be/4 digoal      0.00 B/s  160.40 M/s  0.00 %  0.63 % postgres: postgres postgres [local] INSERT  
55896 be/4 digoal      0.00 B/s  158.79 M/s  0.00 %  0.62 % postgres: postgres postgres [local] INSERT  
55902 be/4 digoal     15.40 K/s  157.65 M/s  0.00 %  0.62 % postgres: postgres postgres [local] INSERT  
55875 be/4 digoal      0.00 B/s  158.52 M/s  0.00 %  0.58 % postgres: postgres postgres [local] INSERT  
55892 be/4 digoal      7.70 K/s  136.20 M/s  0.00 %  0.58 % postgres: postgres postgres [local] INSERT  
55868 be/4 digoal      0.00 B/s  139.10 M/s  0.00 %  0.58 % postgres: postgres postgres [local] INSERT  
55895 be/4 digoal      0.00 B/s  159.75 M/s  0.00 %  0.57 % postgres: postgres postgres [local] INSERT  
55898 be/4 digoal      0.00 B/s  113.43 M/s  0.00 %  0.55 % postgres: postgres postgres [local] INSERT  
55880 be/4 digoal     46.20 K/s  121.68 M/s  0.00 %  0.50 % postgres: postgres postgres [local] INSERT  
55884 be/4 digoal     23.10 K/s  126.35 M/s  0.00 %  0.47 % postgres: postgres postgres [local] INSERT  
55901 be/4 digoal     15.40 K/s  117.46 M/s  0.00 %  0.46 % postgres: postgres postgres [local] INSERT  
55899 be/4 digoal      7.70 K/s  115.13 M/s  0.00 %  0.46 % postgres: postgres postgres [local] INSERT  
```
#### 瓶颈在读写数据文件
```
postgres=# select wait_event_type,wait_event,count(*) from pg_stat_activity where wait_event is not null group by 1,2 order by 3 desc;  
 wait_event_type |     wait_event      | count   
-----------------+---------------------+-------  
 IO              | DataFileWrite       |    15  
 IO              | DataFileRead        |     5  
 Activity        | WalWriterMain       |     1  
 Activity        | LogicalLauncherMain |     1  
 Activity        | CheckpointerMain    |     1  
 Activity        | AutoVacuumMain      |     1  
(6 rows)  
```

#### 任意字段组合查询性能如何
#### 1、
```
postgres=# explain select count(*) from test where c1=2 and c99 between 100 and 1000 and c98 between 100 and 200 and c1=1;  
                                            QUERY PLAN                                               
---------------------------------------------------------------------------------------------------  
 Aggregate  (cost=1201.23..1201.24 rows=1 width=8)  
   ->  Result  (cost=1192.25..1201.22 rows=1 width=0)  
         One-Time Filter: false  
         ->  Bitmap Heap Scan on test  (cost=1192.25..1201.22 rows=1 width=0)  
               Recheck Cond: ((c98 >= 100) AND (c98 <= 200) AND (c99 >= 100) AND (c99 <= 1000))  
               Filter: (c1 = 2)  
               ->  BitmapAnd  (cost=1192.25..1192.25 rows=8 width=0)  
                     ->  Bitmap Index Scan on idx_test_98  (cost=0.00..125.98 rows=9571 width=0)  
                           Index Cond: ((c98 >= 100) AND (c98 <= 200))  
                     ->  Bitmap Index Scan on idx_test_99  (cost=0.00..1066.02 rows=81795 width=0)  
                           Index Cond: ((c99 >= 100) AND (c99 <= 1000))  
(11 rows)

postgres=# select count(*) from test where c1=2 and c99 between 100 and 1000 and c98 between 100 and 200 and c2=1;  
 count   
-------  
     0  
(1 row)

Time: 1.087 ms  
```
#### 2、
```
set min_parallel_index_scan_size=0;  
set min_parallel_table_scan_size=0;  
set parallel_setup_cost=0;  
set parallel_tuple_cost=0;

set work_mem='1GB';  
set max_parallel_workers=128;  
set max_parallel_workers_per_gather=24;  
set random_page_cost =1.1;  
set effective_cache_size ='400GB';  
alter table test set (parallel_workers=64);  
set enable_bitmapscan=off;

postgres=# select count(*) from test where c1=2 and c99 between 100 and 10000;  
 count   
-------  
  9764  
(1 row)

Time: 50.160 ms

postgres=# select count(*) from test where c1=2 and c99 between 100 and 1000 and c98 between 100 and 200 and c2=1;  
 count   
-------  
     0  
(1 row)

Time: 20.969 ms

postgres=# select count(*) from test where c1=2 and c99 between 100 and 10000 and c108 between 100 and 10000;  
 count   
-------  
   102  
(1 row)

Time: 72.359 ms

postgres=# select count(*) from test where c1=2 and c99=1;  
 count   
-------  
     2  
(1 row)

Time: 1.118 ms  
```
#### 3、OR
```
set enable_bitmapscan=on;

postgres=# explain select count(*) from test where c1=2 and c99=1 or c100 between 10 and 100;  
                                         QUERY PLAN                                           
--------------------------------------------------------------------------------------------  
 Aggregate  (cost=10000010781.91..10000010781.92 rows=1 width=8)  
   ->  Bitmap Heap Scan on test  (cost=10000000130.57..10000010758.33 rows=9430 width=0)  
         Recheck Cond: ((c99 = 1) OR ((c100 >= 10) AND (c100 <= 100)))  
         Filter: (((c1 = 2) AND (c99 = 1)) OR ((c100 >= 10) AND (c100 <= 100)))  
         ->  BitmapOr  (cost=130.57..130.57 rows=9526 width=0)  
               ->  Bitmap Index Scan on idx_test_99  (cost=0.00..2.39 rows=96 width=0)  
                     Index Cond: (c99 = 1)  
               ->  Bitmap Index Scan on idx_test_100  (cost=0.00..123.47 rows=9430 width=0)  
                     Index Cond: ((c100 >= 10) AND (c100 <= 100))  
(9 rows)

Time: 1.281 ms  
postgres=#  select count(*) from test where c1=2 and c99=1 or c100 between 10 and 100;  
 count   
-------  
  9174  
(1 row)

Time: 18.785 ms  
```

### **小结**

![CENTER_PostgreSQL_Community]( /images/news/2020/20200916_1651_图片1---2020.9_.16_.png)

#### 性能差异：
#### 1、执行计划
#### 2、扫描量
#### 3、运算量（与结果集大小无直接关系，关键看扫描方法和中间计算量）。
#### 写入能力：129个索引，写入9505行/s。瓶颈在IO侧，通过提升IO能力，加分区可以提高。

### **参考**
#### 《PostgreSQL 设计优化case - 大宽表任意字段组合查询索引如何选择(btree, gin, rum) - (含单个索引列数超过32列的方法)》
#### https://github.com/digoal/blog/blob/master/201808/20180803_01.md
#### 《PostgreSQL 任意字段数组合 AND\OR 条件，指定返回结果条数，构造测试数据算法举例》
#### https://github.com/digoal/blog/blob/master/201809/20180905_03.md
#### 《PostgreSQL ADHoc(任意字段组合)查询(rums索引加速) - 非字典化，普通、数组等组合字段生成新数组》
#### https://github.com/digoal/blog/blob/master/201805/20180518_02.md
#### 《PostgreSQL 实践 - 实时广告位推荐 2 (任意字段组合、任意维度组合搜索、输出TOP-K)》
#### https://github.com/digoal/blog/blob/master/201804/20180424_04.md
#### 《PostgreSQL 实践 - 实时广告位推荐 1 (任意字段组合、任意维度组合搜索、输出TOP-K)》
#### https://github.com/digoal/blog/blob/master/201804/20180420_03.md
#### 《PostgreSQL ADHoc(任意字段组合)查询 与 字典化 (rum索引加速) - 实践与方案1》
#### https://github.com/digoal/blog/blob/master/201802/20180228_01.md
#### 《PostgreSQL 如何高效解决 按任意字段分词检索的问题 - case 1》
#### https://github.com/digoal/blog/blob/master/201607/20160725_05.md
#### 《HTAP数据库 PostgreSQL 场景与性能测试之 20 - (OLAP) 用户画像圈人场景 - 多个字段任意组合条件筛选与透视》
#### https://github.com/digoal/blog/blob/master/201711/20171107_21.md
#### 《PostgreSQL 多字段任意组合搜索的性能》
#### https://github.com/digoal/blog/blob/master/201711/20171102_01.md
#### 《PostgreSQL 并行计算解说 汇总》
#### https://github.com/digoal/blog/blob/master/201903/20190319_01.md
#### 《PostgreSQL 9种索引的原理和应用场景》
#### https://github.com/digoal/blog/blob/master/201706/20170627_01.md

![CENTER_PostgreSQL_Community]( /images/news/2020/20200916_1652_最新底部文章.png)