Author: digoal
《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) 用户画像圈人场景 - 多个字段任意组合条件筛选与透视》
1亿记录，128个字段，任意字段组合查询。性能如何？
PG凭什么可以搞定大数据量的任意字段组合实时搜索？
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
| 任意维度查询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) 用户画像圈人场景 - 多个字段任意组合条件筛选与透视》