我的书签
添加书签
移除书签背景
《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、测试表
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) 用户画像圈人场景 - 多个字段任意组合条件筛选与透视》
