背景

《PostgreSQL 设计优化case - 大宽表任意字段组合查询索引如何选择(btree, gin, rum) - (含单个索引列数超过32列的方法)》

《PostgreSQL 任意字段数组合 AND\OR 条件，指定返回结果条数，构造测试数据算法举例》

《PostgreSQL ADHoc(任意字段组合)查询(rums索引加速) - 非字典化，普通、数组等组合字段生成新数组》

《PostgreSQL 实践 - 实时广告位推荐 2 (任意字段组合、任意维度组合搜索、输出TOP-K)》

《PostgreSQL 实践 - 实时广告位推荐 1 (任意字段组合、任意维度组合搜索、输出TOP-K)》

《PostgreSQL ADHoc(任意字段组合)查询 与 字典化 (rum索引加速) - 实践与方案1》

《PostgreSQL 如何高效解决 按任意字段分词检索的问题 - case 1》

《HTAP数据库 PostgreSQL 场景与性能测试之 20 - (OLAP) 用户画像圈人场景 - 多个字段任意组合条件筛选与透视》

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

1亿记录，128个字段，任意字段组合查询。性能如何？

PG凭什么可以搞定大数据量的任意字段组合实时搜索？

《PostgreSQL 并行计算解说 汇总》

《PostgreSQL 9种索引的原理和应用场景》

例子

1、测试表

  1. do language plpgsql $$  
  2. declare  
  3.   sql text;  
  4. begin  
  5.   sql := 'create unlogged table test(id serial primary key,';  
  6.   for i in 1..64 loop  
  7.     sql := sql||' c'||i||' int default random()*100,';  
  8.   end loop;  
  9.   for i in 65..128 loop  
  10.     sql := sql||' c'||i||' int default random()*1000000,';  
  11.   end loop;  
  12.   sql := rtrim(sql,',');  
  13.   sql := sql||')';  
  14.   execute sql;  
  15. end;  
  16. $$;

2、写入1亿数据

  1. vi test.sql  
  2. insert into test (c1) select random()*100 from generate_series(1,100);  
  5. nohup pgbench -M prepared -n -r -P 1 -f ./test.sql -c 50 -j 50 -t 20000 >/dev/null 2>&1 &

3、写完后的大小

  1. postgres=# \dt+ test  
  2.                    List of relations  
  3.  Schema | Name | Type  |  Owner   | Size  | Description   
  4. --------+------+-------+----------+-------+-------------  
  5.  public | test | table | postgres | 55 GB |   
  6. (1 row)  
  9. postgres=# select count(*) from test;  
  10.    count     
  11. -----------  
  12.  100000000  
  13. (1 row)

4、高效率创建索引

  1. vi idx.sql  
  3. vacuum (analyze,verbose) test;  
  4. set maintenance_work_mem='8GB';  
  5. set max_parallel_workers=128;  
  6. set max_parallel_workers_per_gather=32;  
  7. set min_parallel_index_scan_size=0;  
  8. set min_parallel_table_scan_size=0;  
  9. set parallel_setup_cost=0;  
  10. set parallel_tuple_cost=0;  
  11. set max_parallel_maintenance_workers=16;  
  12. alter table test set (parallel_workers=64);  
  14. do language plpgsql $$  
  15. declare  
  16.   sql text;  
  17. begin  
  18.   for i in 1..128 loop  
  19.     execute format('create index idx_test_%s on test (c%s) %s', i, i, 'tablespace tbs_8001');  
  20.   end loop;  
  21. end;  
  22. $$;  
  24. vacuum (analyze,verbose) test;  
  29. nohup psql -f ./idx.sql >/dev/null 2>&1 &

5、建完索引后

  1. postgres=# \d+ test  
  2.                                                Unlogged table "public.test"  
  3.  Column |  Type   | Collation | Nullable |                 Default                  | Storage | Stats target | Description   
  4. --------+---------+-----------+----------+------------------------------------------+---------+--------------+-------------  
  5.  id     | integer |           | not null | nextval('test_id_seq'::regclass)         | plain   |              |   
  6.  c1     | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  7.  c2     | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  8.  c3     | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  9.  c4     | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  10.  c5     | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  11.  c6     | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  12.  c7     | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  13.  c8     | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  14.  c9     | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  15.  c10    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  16.  c11    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  17.  c12    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  18.  c13    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  19.  c14    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  20.  c15    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  21.  c16    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  22.  c17    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  23.  c18    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  24.  c19    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  25.  c20    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  26.  c21    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  27.  c22    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  28.  c23    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  29.  c24    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  30.  c25    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  31.  c26    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  32.  c27    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  33.  c28    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  34.  c29    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  35.  c30    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  36.  c31    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  37.  c32    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  38.  c33    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  39.  c34    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  40.  c35    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  41.  c36    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  42.  c37    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  43.  c38    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  44.  c39    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  45.  c40    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  46.  c41    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  47.  c42    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  48.  c43    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  49.  c44    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  50.  c45    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  51.  c46    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  52.  c47    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  53.  c48    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  54.  c49    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  55.  c50    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  56.  c51    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  57.  c52    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  58.  c53    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  59.  c54    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  60.  c55    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  61.  c56    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  62.  c57    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  63.  c58    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  64.  c59    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  65.  c60    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  66.  c61    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  67.  c62    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  68.  c63    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  69.  c64    | integer |           |          | (random() * (100)::double precision)     | plain   |              |   
  70.  c65    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  71.  c66    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  72.  c67    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  73.  c68    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  74.  c69    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  75.  c70    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  76.  c71    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  77.  c72    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  78.  c73    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  79.  c74    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  80.  c75    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  81.  c76    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  82.  c77    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  83.  c78    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  84.  c79    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  85.  c80    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  86.  c81    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  87.  c82    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  88.  c83    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  89.  c84    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  90.  c85    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  91.  c86    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  92.  c87    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  93.  c88    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  94.  c89    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  95.  c90    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  96.  c91    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  97.  c92    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  98.  c93    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  99.  c94    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  100.  c95    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  101.  c96    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  102.  c97    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  103.  c98    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  104.  c99    | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  105.  c100   | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  106.  c101   | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  107.  c102   | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  108.  c103   | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  109.  c104   | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  110.  c105   | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  111.  c106   | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  112.  c107   | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  113.  c108   | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  114.  c109   | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  115.  c110   | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  116.  c111   | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  117.  c112   | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  118.  c113   | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  119.  c114   | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  120.  c115   | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  121.  c116   | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  122.  c117   | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  123.  c118   | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  124.  c119   | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  125.  c120   | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  126.  c121   | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  127.  c122   | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  128.  c123   | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  129.  c124   | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  130.  c125   | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  131.  c126   | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  132.  c127   | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  133.  c128   | integer |           |          | (random() * (1000000)::double precision) | plain   |              |   
  134. Indexes:  
  135.     "test_pkey" PRIMARY KEY, btree (id)  
  136.     "idx_test_1" btree (c1), tablespace "tbs_8001"  
  137.     "idx_test_10" btree (c10), tablespace "tbs_8001"  
  138.     "idx_test_100" btree (c100), tablespace "tbs_8001"  
  139.     "idx_test_101" btree (c101), tablespace "tbs_8001"  
  140.     "idx_test_102" btree (c102), tablespace "tbs_8001"  
  141.     "idx_test_103" btree (c103), tablespace "tbs_8001"  
  142.     "idx_test_104" btree (c104), tablespace "tbs_8001"  
  143.     "idx_test_105" btree (c105), tablespace "tbs_8001"  
  144.     "idx_test_106" btree (c106), tablespace "tbs_8001"  
  145.     "idx_test_107" btree (c107), tablespace "tbs_8001"  
  146.     "idx_test_108" btree (c108), tablespace "tbs_8001"  
  147.     "idx_test_109" btree (c109), tablespace "tbs_8001"  
  148.     "idx_test_11" btree (c11), tablespace "tbs_8001"  
  149.     "idx_test_110" btree (c110), tablespace "tbs_8001"  
  150.     "idx_test_111" btree (c111), tablespace "tbs_8001"  
  151.     "idx_test_112" btree (c112), tablespace "tbs_8001"  
  152.     "idx_test_113" btree (c113), tablespace "tbs_8001"  
  153.     "idx_test_114" btree (c114), tablespace "tbs_8001"  
  154.     "idx_test_115" btree (c115), tablespace "tbs_8001"  
  155.     "idx_test_116" btree (c116), tablespace "tbs_8001"  
  156.     "idx_test_117" btree (c117), tablespace "tbs_8001"  
  157.     "idx_test_118" btree (c118), tablespace "tbs_8001"  
  158.     "idx_test_119" btree (c119), tablespace "tbs_8001"  
  159.     "idx_test_12" btree (c12), tablespace "tbs_8001"  
  160.     "idx_test_120" btree (c120), tablespace "tbs_8001"  
  161.     "idx_test_121" btree (c121), tablespace "tbs_8001"  
  162.     "idx_test_122" btree (c122), tablespace "tbs_8001"  
  163.     "idx_test_123" btree (c123), tablespace "tbs_8001"  
  164.     "idx_test_124" btree (c124), tablespace "tbs_8001"  
  165.     "idx_test_125" btree (c125), tablespace "tbs_8001"  
  166.     "idx_test_126" btree (c126), tablespace "tbs_8001"  
  167.     "idx_test_127" btree (c127), tablespace "tbs_8001"  
  168.     "idx_test_128" btree (c128), tablespace "tbs_8001"  
  169.     "idx_test_13" btree (c13), tablespace "tbs_8001"  
  170.     "idx_test_14" btree (c14), tablespace "tbs_8001"  
  171.     "idx_test_15" btree (c15), tablespace "tbs_8001"  
  172.     "idx_test_16" btree (c16), tablespace "tbs_8001"  
  173.     "idx_test_17" btree (c17), tablespace "tbs_8001"  
  174.     "idx_test_18" btree (c18), tablespace "tbs_8001"  
  175.     "idx_test_19" btree (c19), tablespace "tbs_8001"  
  176.     "idx_test_2" btree (c2), tablespace "tbs_8001"  
  177.     "idx_test_20" btree (c20), tablespace "tbs_8001"  
  178.     "idx_test_21" btree (c21), tablespace "tbs_8001"  
  179.     "idx_test_22" btree (c22), tablespace "tbs_8001"  
  180.     "idx_test_23" btree (c23), tablespace "tbs_8001"  
  181.     "idx_test_24" btree (c24), tablespace "tbs_8001"  
  182.     "idx_test_25" btree (c25), tablespace "tbs_8001"  
  183.     "idx_test_26" btree (c26), tablespace "tbs_8001"  
  184.     "idx_test_27" btree (c27), tablespace "tbs_8001"  
  185.     "idx_test_28" btree (c28), tablespace "tbs_8001"  
  186.     "idx_test_29" btree (c29), tablespace "tbs_8001"  
  187.     "idx_test_3" btree (c3), tablespace "tbs_8001"  
  188.     "idx_test_30" btree (c30), tablespace "tbs_8001"  
  189.     "idx_test_31" btree (c31), tablespace "tbs_8001"  
  190.     "idx_test_32" btree (c32), tablespace "tbs_8001"  
  191.     "idx_test_33" btree (c33), tablespace "tbs_8001"  
  192.     "idx_test_34" btree (c34), tablespace "tbs_8001"  
  193.     "idx_test_35" btree (c35), tablespace "tbs_8001"  
  194.     "idx_test_36" btree (c36), tablespace "tbs_8001"  
  195.     "idx_test_37" btree (c37), tablespace "tbs_8001"  
  196.     "idx_test_38" btree (c38), tablespace "tbs_8001"  
  197.     "idx_test_39" btree (c39), tablespace "tbs_8001"  
  198.     "idx_test_4" btree (c4), tablespace "tbs_8001"  
  199.     "idx_test_40" btree (c40), tablespace "tbs_8001"  
  200.     "idx_test_41" btree (c41), tablespace "tbs_8001"  
  201.     "idx_test_42" btree (c42), tablespace "tbs_8001"  
  202.     "idx_test_43" btree (c43), tablespace "tbs_8001"  
  203.     "idx_test_44" btree (c44), tablespace "tbs_8001"  
  204.     "idx_test_45" btree (c45), tablespace "tbs_8001"  
  205.     "idx_test_46" btree (c46), tablespace "tbs_8001"  
  206.     "idx_test_47" btree (c47), tablespace "tbs_8001"  
  207.     "idx_test_48" btree (c48), tablespace "tbs_8001"  
  208.     "idx_test_49" btree (c49), tablespace "tbs_8001"  
  209.     "idx_test_5" btree (c5), tablespace "tbs_8001"  
  210.     "idx_test_50" btree (c50), tablespace "tbs_8001"  
  211.     "idx_test_51" btree (c51), tablespace "tbs_8001"  
  212.     "idx_test_52" btree (c52), tablespace "tbs_8001"  
  213.     "idx_test_53" btree (c53), tablespace "tbs_8001"  
  214.     "idx_test_54" btree (c54), tablespace "tbs_8001"  
  215.     "idx_test_55" btree (c55), tablespace "tbs_8001"  
  216.     "idx_test_56" btree (c56), tablespace "tbs_8001"  
  217.     "idx_test_57" btree (c57), tablespace "tbs_8001"  
  218.     "idx_test_58" btree (c58), tablespace "tbs_8001"  
  219.     "idx_test_59" btree (c59), tablespace "tbs_8001"  
  220.     "idx_test_6" btree (c6), tablespace "tbs_8001"  
  221.     "idx_test_60" btree (c60), tablespace "tbs_8001"  
  222.     "idx_test_61" btree (c61), tablespace "tbs_8001"  
  223.     "idx_test_62" btree (c62), tablespace "tbs_8001"  
  224.     "idx_test_63" btree (c63), tablespace "tbs_8001"  
  225.     "idx_test_64" btree (c64), tablespace "tbs_8001"  
  226.     "idx_test_65" btree (c65), tablespace "tbs_8001"  
  227.     "idx_test_66" btree (c66), tablespace "tbs_8001"  
  228.     "idx_test_67" btree (c67), tablespace "tbs_8001"  
  229.     "idx_test_68" btree (c68), tablespace "tbs_8001"  
  230.     "idx_test_69" btree (c69), tablespace "tbs_8001"  
  231.     "idx_test_7" btree (c7), tablespace "tbs_8001"  
  232.     "idx_test_70" btree (c70), tablespace "tbs_8001"  
  233.     "idx_test_71" btree (c71), tablespace "tbs_8001"  
  234.     "idx_test_72" btree (c72), tablespace "tbs_8001"  
  235.     "idx_test_73" btree (c73), tablespace "tbs_8001"  
  236.     "idx_test_74" btree (c74), tablespace "tbs_8001"  
  237.     "idx_test_75" btree (c75), tablespace "tbs_8001"  
  238.     "idx_test_76" btree (c76), tablespace "tbs_8001"  
  239.     "idx_test_77" btree (c77), tablespace "tbs_8001"  
  240.     "idx_test_78" btree (c78), tablespace "tbs_8001"  
  241.     "idx_test_79" btree (c79), tablespace "tbs_8001"  
  242.     "idx_test_8" btree (c8), tablespace "tbs_8001"  
  243.     "idx_test_80" btree (c80), tablespace "tbs_8001"  
  244.     "idx_test_81" btree (c81), tablespace "tbs_8001"  
  245.     "idx_test_82" btree (c82), tablespace "tbs_8001"  
  246.     "idx_test_83" btree (c83), tablespace "tbs_8001"  
  247.     "idx_test_84" btree (c84), tablespace "tbs_8001"  
  248.     "idx_test_85" btree (c85), tablespace "tbs_8001"  
  249.     "idx_test_86" btree (c86), tablespace "tbs_8001"  
  250.     "idx_test_87" btree (c87), tablespace "tbs_8001"  
  251.     "idx_test_88" btree (c88), tablespace "tbs_8001"  
  252.     "idx_test_89" btree (c89), tablespace "tbs_8001"  
  253.     "idx_test_9" btree (c9), tablespace "tbs_8001"  
  254.     "idx_test_90" btree (c90), tablespace "tbs_8001"  
  255.     "idx_test_91" btree (c91), tablespace "tbs_8001"  
  256.     "idx_test_92" btree (c92), tablespace "tbs_8001"  
  257.     "idx_test_93" btree (c93), tablespace "tbs_8001"  
  258.     "idx_test_94" btree (c94), tablespace "tbs_8001"  
  259.     "idx_test_95" btree (c95), tablespace "tbs_8001"  
  260.     "idx_test_96" btree (c96), tablespace "tbs_8001"  
  261.     "idx_test_97" btree (c97), tablespace "tbs_8001"  
  262.     "idx_test_98" btree (c98), tablespace "tbs_8001"  
  263.     "idx_test_99" btree (c99), tablespace "tbs_8001"  
  264. Options: parallel_workers=64

写入性能如何

当前有129个索引，写入性能如何？

9505行/s。

  1. transaction type: ./test.sql
  2. scaling factor: 1
  3. query mode: prepared
  4. number of clients: 24
  5. number of threads: 24
  6. duration: 120 s
  7. number of transactions actually processed: 11433
  8. latency average = 252.195 ms
  9. latency stddev = 70.089 ms
  10. tps = 95.054689 (including connections establishing)
  11. tps = 95.058210 (excluding connections establishing)
  12. statement latencies in milliseconds:
  13.        252.179  insert into test (c1) select random()*100 from generate_series(1,100);

瓶颈，磁盘读写5.5GB/s。

  1. Total DISK READ :     207.91 K/s | Total DISK WRITE :       3.54 G/s  
  2. Actual DISK READ:     207.91 K/s | Actual DISK WRITE:    2015.64 M/s  
  3.   TID  PRIO  USER     DISK READ  DISK WRITE  SWAPIN     IO>    COMMAND   
  4. 55887 be/4 digoal     15.40 K/s  158.54 M/s  0.00 %  1.05 % postgres: postgres postgres [local] INSERT  
  5. 55872 be/4 digoal      7.70 K/s  157.62 M/s  0.00 %  0.84 % postgres: postgres postgres [local] INSERT  
  6. 55886 be/4 digoal     23.10 K/s  158.78 M/s  0.00 %  0.78 % postgres: postgres postgres [local] INSERT  
  7. 55897 be/4 digoal      7.70 K/s  158.79 M/s  0.00 %  0.75 % postgres: postgres postgres [local] INSERT  
  8. 55889 be/4 digoal      0.00 B/s  158.72 M/s  0.00 %  0.69 % postgres: postgres postgres [local] INSERT  
  9. 55894 be/4 digoal      0.00 B/s  157.25 M/s  0.00 %  0.69 % postgres: postgres postgres [local] INSERT  
  10. 55888 be/4 digoal      7.70 K/s  136.26 M/s  0.00 %  0.68 % postgres: postgres postgres [local] INSERT  
  11. 55885 be/4 digoal      7.70 K/s  143.24 M/s  0.00 %  0.67 % postgres: postgres postgres [local] INSERT  
  12. 55890 be/4 digoal      0.00 B/s  159.07 M/s  0.00 %  0.67 % postgres: postgres postgres [local] INSERT  
  13. 55865 be/4 digoal     15.40 K/s  158.27 M/s  0.00 %  0.65 % postgres: postgres postgres [local] INSERT  
  14. 55900 be/4 digoal      7.70 K/s  151.00 M/s  0.00 %  0.64 % postgres: postgres postgres [local] INSERT  
  15. 55891 be/4 digoal      0.00 B/s  160.40 M/s  0.00 %  0.63 % postgres: postgres postgres [local] INSERT  
  16. 55896 be/4 digoal      0.00 B/s  158.79 M/s  0.00 %  0.62 % postgres: postgres postgres [local] INSERT  
  17. 55902 be/4 digoal     15.40 K/s  157.65 M/s  0.00 %  0.62 % postgres: postgres postgres [local] INSERT  
  18. 55875 be/4 digoal      0.00 B/s  158.52 M/s  0.00 %  0.58 % postgres: postgres postgres [local] INSERT  
  19. 55892 be/4 digoal      7.70 K/s  136.20 M/s  0.00 %  0.58 % postgres: postgres postgres [local] INSERT  
  20. 55868 be/4 digoal      0.00 B/s  139.10 M/s  0.00 %  0.58 % postgres: postgres postgres [local] INSERT  
  21. 55895 be/4 digoal      0.00 B/s  159.75 M/s  0.00 %  0.57 % postgres: postgres postgres [local] INSERT  
  22. 55898 be/4 digoal      0.00 B/s  113.43 M/s  0.00 %  0.55 % postgres: postgres postgres [local] INSERT  
  23. 55880 be/4 digoal     46.20 K/s  121.68 M/s  0.00 %  0.50 % postgres: postgres postgres [local] INSERT  
  24. 55884 be/4 digoal     23.10 K/s  126.35 M/s  0.00 %  0.47 % postgres: postgres postgres [local] INSERT  
  25. 55901 be/4 digoal     15.40 K/s  117.46 M/s  0.00 %  0.46 % postgres: postgres postgres [local] INSERT  
  26. 55899 be/4 digoal      7.70 K/s  115.13 M/s  0.00 %  0.46 % postgres: postgres postgres [local] INSERT

瓶颈在读写数据文件

  1. 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;  
  2.  wait_event_type |     wait_event      | count   
  3. -----------------+---------------------+-------  
  4.  IO              | DataFileWrite       |    15  
  5.  IO              | DataFileRead        |     5  
  6.  Activity        | WalWriterMain       |     1  
  7.  Activity        | LogicalLauncherMain |     1  
  8.  Activity        | CheckpointerMain    |     1  
  9.  Activity        | AutoVacuumMain      |     1  
  10. (6 rows)

任意字段组合查询性能如何

1、

  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;  
  2.                                             QUERY PLAN                                               
  3. ---------------------------------------------------------------------------------------------------  
  4.  Aggregate  (cost=1201.23..1201.24 rows=1 width=8)  
  5.    ->  Result  (cost=1192.25..1201.22 rows=1 width=0)  
  6.          One-Time Filter: false  
  7.          ->  Bitmap Heap Scan on test  (cost=1192.25..1201.22 rows=1 width=0)  
  8.                Recheck Cond: ((c98 >= 100) AND (c98 <= 200) AND (c99 >= 100) AND (c99 <= 1000))  
  9.                Filter: (c1 = 2)  
  10.                ->  BitmapAnd  (cost=1192.25..1192.25 rows=8 width=0)  
  11.                      ->  Bitmap Index Scan on idx_test_98  (cost=0.00..125.98 rows=9571 width=0)  
  12.                            Index Cond: ((c98 >= 100) AND (c98 <= 200))  
  13.                      ->  Bitmap Index Scan on idx_test_99  (cost=0.00..1066.02 rows=81795 width=0)  
  14.                            Index Cond: ((c99 >= 100) AND (c99 <= 1000))  
  15. (11 rows)  
  18. postgres=# select count(*) from test where c1=2 and c99 between 100 and 1000 and c98 between 100 and 200 and c2=1;  
  19.  count   
  20. -------  
  21.      0  
  22. (1 row)  
  24. Time: 1.087 ms

2、

  1. set min_parallel_index_scan_size=0;  
  2. set min_parallel_table_scan_size=0;  
  3. set parallel_setup_cost=0;  
  4. set parallel_tuple_cost=0;  
  8. set work_mem='1GB';  
  9. set max_parallel_workers=128;  
  10. set max_parallel_workers_per_gather=24;  
  11. set random_page_cost =1.1;  
  12. set effective_cache_size ='400GB';  
  13. alter table test set (parallel_workers=64);  
  14. set enable_bitmapscan=off;
  1. postgres=# select count(*) from test where c1=2 and c99 between 100 and 10000;  
  2.  count   
  3. -------  
  4.   9764  
  5. (1 row)  
  7. Time: 50.160 ms  
  10. postgres=# select count(*) from test where c1=2 and c99 between 100 and 1000 and c98 between 100 and 200 and c2=1;  
  11.  count   
  12. -------  
  13.      0  
  14. (1 row)  
  16. Time: 20.969 ms  
  18. postgres=# select count(*) from test where c1=2 and c99 between 100 and 10000 and c108 between 100 and 10000;  
  19.  count   
  20. -------  
  21.    102  
  22. (1 row)  
  24. Time: 72.359 ms  
  26. postgres=# select count(*) from test where c1=2 and c99=1;  
  27.  count   
  28. -------  
  29.      2  
  30. (1 row)  
  32. Time: 1.118 ms

3、OR

  1. set enable_bitmapscan=on;  
  3. postgres=# explain select count(*) from test where c1=2 and c99=1 or c100 between 10 and 100;  
  4.                                          QUERY PLAN                                           
  5. --------------------------------------------------------------------------------------------  
  6.  Aggregate  (cost=10000010781.91..10000010781.92 rows=1 width=8)  
  7.    ->  Bitmap Heap Scan on test  (cost=10000000130.57..10000010758.33 rows=9430 width=0)  
  8.          Recheck Cond: ((c99 = 1) OR ((c100 >= 10) AND (c100 <= 100)))  
  9.          Filter: (((c1 = 2) AND (c99 = 1)) OR ((c100 >= 10) AND (c100 <= 100)))  
  10.          ->  BitmapOr  (cost=130.57..130.57 rows=9526 width=0)  
  11.                ->  Bitmap Index Scan on idx_test_99  (cost=0.00..2.39 rows=96 width=0)  
  12.                      Index Cond: (c99 = 1)  
  13.                ->  Bitmap Index Scan on idx_test_100  (cost=0.00..123.47 rows=9430 width=0)  
  14.                      Index Cond: ((c100 >= 10) AND (c100 <= 100))  
  15. (9 rows)  
  17. Time: 1.281 ms  
  18. postgres=#  select count(*) from test where c1=2 and c99=1 or c100 between 10 and 100;  
  19.  count   
  20. -------  
  21.   9174  
  22. (1 row)  
  24. Time: 18.785 ms

小结

任意维度查询case耗时
c1=2 and c99 between 100 and 10000;50 毫秒
c1=2 and c99 between 100 and 1000 and c98 between 100 and 200 and c2=1;21 毫秒
c1=2 and c99 between 100 and 10000 and c108 between 100 and 10000;72 毫秒
c1=2 and c99=1;1 毫秒
c1=2 and c99=1 or c100 between 10 and 100;19 毫秒

性能差异：

1、执行计划

2、扫描量

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

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

参考

《PostgreSQL 设计优化case - 大宽表任意字段组合查询索引如何选择(btree, gin, rum) - (含单个索引列数超过32列的方法)》

《PostgreSQL 任意字段数组合 AND\OR 条件，指定返回结果条数，构造测试数据算法举例》

《PostgreSQL ADHoc(任意字段组合)查询(rums索引加速) - 非字典化，普通、数组等组合字段生成新数组》

《PostgreSQL 实践 - 实时广告位推荐 2 (任意字段组合、任意维度组合搜索、输出TOP-K)》

《PostgreSQL 实践 - 实时广告位推荐 1 (任意字段组合、任意维度组合搜索、输出TOP-K)》

《PostgreSQL ADHoc(任意字段组合)查询 与 字典化 (rum索引加速) - 实践与方案1》

《PostgreSQL 如何高效解决 按任意字段分词检索的问题 - case 1》

《HTAP数据库 PostgreSQL 场景与性能测试之 20 - (OLAP) 用户画像圈人场景 - 多个字段任意组合条件筛选与透视》

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

《PostgreSQL 并行计算解说 汇总》

《PostgreSQL 9种索引的原理和应用场景》

原文：http://mysql.taobao.org/monthly/2019/04/09/