PgSQL · 应用案例 · 任意字段组合查询

PostgreSQL中文社区 2020-09-17

1197

背景

《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

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 &

3、写完后的大小

postgres=# \dt+ test  
                   List of relations  
 Schema | Name | Type  |  Owner   | Size  | Description   
--------+------+-------+----------+-------+-------------  
 public | test | table | postgres | 55 GB |   
(1 row)  


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 &

5、建完索引后

postgres=# \d+ test  
                                               Unlogged table "public.test"  
 Column |  Type   | Collation | Nullable |                 Default                  | Storage | Stats target | Description   
--------+---------+-----------+----------+------------------------------------------+---------+--------------+-------------  
 id     | integer |           | not null | nextval('test_id_seq'::regclass)         | plain   |              |   
 c1     | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c2     | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c3     | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c4     | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c5     | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c6     | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c7     | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c8     | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c9     | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c10    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c11    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c12    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c13    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c14    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c15    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c16    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c17    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c18    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c19    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c20    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c21    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c22    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c23    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c24    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c25    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c26    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c27    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c28    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c29    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c30    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c31    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c32    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c33    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c34    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c35    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c36    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c37    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c38    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c39    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c40    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c41    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c42    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c43    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c44    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c45    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c46    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c47    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c48    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c49    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c50    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c51    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c52    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c53    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c54    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c55    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c56    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c57    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c58    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c59    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c60    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c61    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c62    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c63    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c64    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
 c65    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c66    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c67    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c68    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c69    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c70    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c71    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c72    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c73    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c74    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c75    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c76    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c77    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c78    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c79    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c80    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c81    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c82    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c83    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c84    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c85    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c86    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c87    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c88    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c89    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c90    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c91    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c92    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c93    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c94    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c95    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c96    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c97    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c98    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c99    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c100   | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c101   | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c102   | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c103   | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c104   | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c105   | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c106   | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c107   | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c108   | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c109   | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c110   | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c111   | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c112   | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c113   | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c114   | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c115   | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c116   | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c117   | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c118   | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c119   | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c120   | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c121   | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c122   | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c123   | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c124   | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c125   | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c126   | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c127   | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
 c128   | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
Indexes:  
    "test_pkey" PRIMARY KEY, btree (id)  
    "idx_test_1" btree (c1), tablespace "tbs_8001"  
    "idx_test_10" btree (c10), tablespace "tbs_8001"  
    "idx_test_100" btree (c100), tablespace "tbs_8001"  
    "idx_test_101" btree (c101), tablespace "tbs_8001"  
    "idx_test_102" btree (c102), tablespace "tbs_8001"  
    "idx_test_103" btree (c103), tablespace "tbs_8001"  
    "idx_test_104" btree (c104), tablespace "tbs_8001"  
    "idx_test_105" btree (c105), tablespace "tbs_8001"  
    "idx_test_106" btree (c106), tablespace "tbs_8001"  
    "idx_test_107" btree (c107), tablespace "tbs_8001"  
    "idx_test_108" btree (c108), tablespace "tbs_8001"  
    "idx_test_109" btree (c109), tablespace "tbs_8001"  
    "idx_test_11" btree (c11), tablespace "tbs_8001"  
    "idx_test_110" btree (c110), tablespace "tbs_8001"  
    "idx_test_111" btree (c111), tablespace "tbs_8001"  
    "idx_test_112" btree (c112), tablespace "tbs_8001"  
    "idx_test_113" btree (c113), tablespace "tbs_8001"  
    "idx_test_114" btree (c114), tablespace "tbs_8001"  
    "idx_test_115" btree (c115), tablespace "tbs_8001"  
    "idx_test_116" btree (c116), tablespace "tbs_8001"  
    "idx_test_117" btree (c117), tablespace "tbs_8001"  
    "idx_test_118" btree (c118), tablespace "tbs_8001"  
    "idx_test_119" btree (c119), tablespace "tbs_8001"  
    "idx_test_12" btree (c12), tablespace "tbs_8001"  
    "idx_test_120" btree (c120), tablespace "tbs_8001"  
    "idx_test_121" btree (c121), tablespace "tbs_8001"  
    "idx_test_122" btree (c122), tablespace "tbs_8001"  
    "idx_test_123" btree (c123), tablespace "tbs_8001"  
    "idx_test_124" btree (c124), tablespace "tbs_8001"  
    "idx_test_125" btree (c125), tablespace "tbs_8001"  
    "idx_test_126" btree (c126), tablespace "tbs_8001"  
    "idx_test_127" btree (c127), tablespace "tbs_8001"  
    "idx_test_128" btree (c128), tablespace "tbs_8001"  
    "idx_test_13" btree (c13), tablespace "tbs_8001"  
    "idx_test_14" btree (c14), tablespace "tbs_8001"  
    "idx_test_15" btree (c15), tablespace "tbs_8001"  
    "idx_test_16" btree (c16), tablespace "tbs_8001"  
    "idx_test_17" btree (c17), tablespace "tbs_8001"  
    "idx_test_18" btree (c18), tablespace "tbs_8001"  
    "idx_test_19" btree (c19), tablespace "tbs_8001"  
    "idx_test_2" btree (c2), tablespace "tbs_8001"  
    "idx_test_20" btree (c20), tablespace "tbs_8001"  
    "idx_test_21" btree (c21), tablespace "tbs_8001"  
    "idx_test_22" btree (c22), tablespace "tbs_8001"  
    "idx_test_23" btree (c23), tablespace "tbs_8001"  
    "idx_test_24" btree (c24), tablespace "tbs_8001"  
    "idx_test_25" btree (c25), tablespace "tbs_8001"  
    "idx_test_26" btree (c26), tablespace "tbs_8001"  
    "idx_test_27" btree (c27), tablespace "tbs_8001"  
    "idx_test_28" btree (c28), tablespace "tbs_8001"  
    "idx_test_29" btree (c29), tablespace "tbs_8001"  
    "idx_test_3" btree (c3), tablespace "tbs_8001"  
    "idx_test_30" btree (c30), tablespace "tbs_8001"  
    "idx_test_31" btree (c31), tablespace "tbs_8001"  
    "idx_test_32" btree (c32), tablespace "tbs_8001"  
    "idx_test_33" btree (c33), tablespace "tbs_8001"  
    "idx_test_34" btree (c34), tablespace "tbs_8001"  
    "idx_test_35" btree (c35), tablespace "tbs_8001"  
    "idx_test_36" btree (c36), tablespace "tbs_8001"  
    "idx_test_37" btree (c37), tablespace "tbs_8001"  
    "idx_test_38" btree (c38), tablespace "tbs_8001"  
    "idx_test_39" btree (c39), tablespace "tbs_8001"  
    "idx_test_4" btree (c4), tablespace "tbs_8001"  
    "idx_test_40" btree (c40), tablespace "tbs_8001"  
    "idx_test_41" btree (c41), tablespace "tbs_8001"  
    "idx_test_42" btree (c42), tablespace "tbs_8001"  
    "idx_test_43" btree (c43), tablespace "tbs_8001"  
    "idx_test_44" btree (c44), tablespace "tbs_8001"  
    "idx_test_45" btree (c45), tablespace "tbs_8001"  
    "idx_test_46" btree (c46), tablespace "tbs_8001"  
    "idx_test_47" btree (c47), tablespace "tbs_8001"  
    "idx_test_48" btree (c48), tablespace "tbs_8001"  
    "idx_test_49" btree (c49), tablespace "tbs_8001"  
    "idx_test_5" btree (c5), tablespace "tbs_8001"  
    "idx_test_50" btree (c50), tablespace "tbs_8001"  
    "idx_test_51" btree (c51), tablespace "tbs_8001"  
    "idx_test_52" btree (c52), tablespace "tbs_8001"  
    "idx_test_53" btree (c53), tablespace "tbs_8001"  
    "idx_test_54" btree (c54), tablespace "tbs_8001"  
    "idx_test_55" btree (c55), tablespace "tbs_8001"  
    "idx_test_56" btree (c56), tablespace "tbs_8001"  
    "idx_test_57" btree (c57), tablespace "tbs_8001"  
    "idx_test_58" btree (c58), tablespace "tbs_8001"  
    "idx_test_59" btree (c59), tablespace "tbs_8001"  
    "idx_test_6" btree (c6), tablespace "tbs_8001"  
    "idx_test_60" btree (c60), tablespace "tbs_8001"  
    "idx_test_61" btree (c61), tablespace "tbs_8001"  
    "idx_test_62" btree (c62), tablespace "tbs_8001"  
    "idx_test_63" btree (c63), tablespace "tbs_8001"  
    "idx_test_64" btree (c64), tablespace "tbs_8001"  
    "idx_test_65" btree (c65), tablespace "tbs_8001"  
    "idx_test_66" btree (c66), tablespace "tbs_8001"  
    "idx_test_67" btree (c67), tablespace "tbs_8001"  
    "idx_test_68" btree (c68), tablespace "tbs_8001"  
    "idx_test_69" btree (c69), tablespace "tbs_8001"  
    "idx_test_7" btree (c7), tablespace "tbs_8001"  
    "idx_test_70" btree (c70), tablespace "tbs_8001"  
    "idx_test_71" btree (c71), tablespace "tbs_8001"  
    "idx_test_72" btree (c72), tablespace "tbs_8001"  
    "idx_test_73" btree (c73), tablespace "tbs_8001"  
    "idx_test_74" btree (c74), tablespace "tbs_8001"  
    "idx_test_75" btree (c75), tablespace "tbs_8001"  
    "idx_test_76" btree (c76), tablespace "tbs_8001"  
    "idx_test_77" btree (c77), tablespace "tbs_8001"  
    "idx_test_78" btree (c78), tablespace "tbs_8001"  
    "idx_test_79" btree (c79), tablespace "tbs_8001"  
    "idx_test_8" btree (c8), tablespace "tbs_8001"  
    "idx_test_80" btree (c80), tablespace "tbs_8001"  
    "idx_test_81" btree (c81), tablespace "tbs_8001"  
    "idx_test_82" btree (c82), tablespace "tbs_8001"  
    "idx_test_83" btree (c83), tablespace "tbs_8001"  
    "idx_test_84" btree (c84), tablespace "tbs_8001"  
    "idx_test_85" btree (c85), tablespace "tbs_8001"  
    "idx_test_86" btree (c86), tablespace "tbs_8001"  
    "idx_test_87" btree (c87), tablespace "tbs_8001"  
    "idx_test_88" btree (c88), tablespace "tbs_8001"  
    "idx_test_89" btree (c89), tablespace "tbs_8001"  
    "idx_test_9" btree (c9), tablespace "tbs_8001"  
    "idx_test_90" btree (c90), tablespace "tbs_8001"  
    "idx_test_91" btree (c91), tablespace "tbs_8001"  
    "idx_test_92" btree (c92), tablespace "tbs_8001"  
    "idx_test_93" btree (c93), tablespace "tbs_8001"  
    "idx_test_94" btree (c94), tablespace "tbs_8001"  
    "idx_test_95" btree (c95), tablespace "tbs_8001"  
    "idx_test_96" btree (c96), tablespace "tbs_8001"  
    "idx_test_97" btree (c97), tablespace "tbs_8001"  
    "idx_test_98" btree (c98), tablespace "tbs_8001"  
    "idx_test_99" btree (c99), tablespace "tbs_8001"  
Options: parallel_workers=64

写入性能如何

当前有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

小结

性能差异：

1、执行计划

2、扫描量

3、运算量（与结果集大小无直接关系，关键看扫描方法和中间计算量）。

写入能力：129个索引，写入9505行/s。瓶颈在IO侧，通过提升IO能力，加分区可以提高。

参考