OSDN > ソフトウェアを探す > PostgreSQL でヒント句を使う > SCM > git > pg_hint_plan > コミット

PostgreSQL でヒント句を使う
Fork

R/O
HTTP
SSH
HTTPS

pg_hint_plan: コミット

firtst release

コミットメタ情報

リビジョン	5da6c043e1dec61215d395f6698e302456688980 (tree)
日時	2017-02-21 09:11:44
作者	Kyotaro Horiguchi <horiguchi.kyotaro@lab....>
コミッター	Kyotaro Horiguchi

ログメッセージ

Fix parallel hint error handling.

Some kind of error of a parallel hint resulted confusing log
output. Addition to that, due to improper assumption of .conf file,
ordinary setup of max_worker_processes caused failure of ut-W. Fixed
them.

変更サマリ

modified: expected/ut-W.out (diff)
modified: pg_hint_plan.c (diff)
modified: sql/ut-W.sql (diff)

差分

--- a/expected/ut-W.out

+++ b/expected/ut-W.out

		@@ -16,11 +16,11 @@ SET parallel_setup_cost to 0;
16	16	SET parallel_tuple_cost to 0;
17	17	SET min_parallel_relation_size to 0;
18	18	SET max_parallel_workers_per_gather to DEFAULT;
19		-/+Parallel(t1 10)/
	19	+/+Parallel(t1 8)/
20	20	EXPLAIN (COSTS false) SELECT * FROM s1.t1;
21	21	LOG: pg_hint_plan:
22	22	used hint:
23		-Parallel(t1 10 soft)
	23	+Parallel(t1 8 soft)
24	24	not used hint:
25	25	duplication hint:
26	26	error hint:

		@@ -32,11 +32,11 @@ error hint:
32	32	-> Parallel Seq Scan on t1
33	33	(3 rows)
34	34
35		-/+Parallel(t1 10 soft)/
	35	+/+Parallel(t1 8 soft)/
36	36	EXPLAIN (COSTS false) SELECT * FROM s1.t1;
37	37	LOG: pg_hint_plan:
38	38	used hint:
39		-Parallel(t1 10 soft)
	39	+Parallel(t1 8 soft)
40	40	not used hint:
41	41	duplication hint:
42	42	error hint:

		@@ -48,11 +48,11 @@ error hint:
48	48	-> Parallel Seq Scan on t1
49	49	(3 rows)
50	50
51		-/+Parallel(t1 10 hard)/
	51	+/+Parallel(t1 8 hard)/
52	52	EXPLAIN (COSTS false) SELECT * FROM s1.t1;
53	53	LOG: pg_hint_plan:
54	54	used hint:
55		-Parallel(t1 10 hard)
	55	+Parallel(t1 8 hard)
56	56	not used hint:
57	57	duplication hint:
58	58	error hint:

		@@ -60,7 +60,7 @@ error hint:
60	60	QUERY PLAN
61	61	-------------------------------
62	62	Gather
63		- Workers Planned: 10
	63	+ Workers Planned: 8
64	64	-> Parallel Seq Scan on t1
65	65	(3 rows)
66	66

		@@ -68,11 +68,11 @@ error hint:
68	68	SET parallel_setup_cost to 0;
69	69	SET parallel_tuple_cost to 0;
70	70	SET min_parallel_relation_size to 0;
71		-/+Parallel(p1 10)/
	71	+/+Parallel(p1 8)/
72	72	EXPLAIN (COSTS false) SELECT * FROM p1;
73	73	LOG: pg_hint_plan:
74	74	used hint:
75		-Parallel(p1 10 soft)
	75	+Parallel(p1 8 soft)
76	76	not used hint:
77	77	duplication hint:
78	78	error hint:

		@@ -96,11 +96,11 @@ error hint:
96	96	SET parallel_setup_cost to DEFAULT;
97	97	SET parallel_tuple_cost to DEFAULT;
98	98	SET min_parallel_relation_size to DEFAULT;
99		-/+Parallel(p1 10 hard)/
	99	+/+Parallel(p1 8 hard)/
100	100	EXPLAIN (COSTS false) SELECT * FROM p1;
101	101	LOG: pg_hint_plan:
102	102	used hint:
103		-Parallel(p1 10 hard)
	103	+Parallel(p1 8 hard)
104	104	not used hint:
105	105	duplication hint:
106	106	error hint:

		@@ -108,7 +108,7 @@ error hint:
108	108	QUERY PLAN
109	109	-------------------------------------------
110	110	Gather
111		- Workers Planned: 10
	111	+ Workers Planned: 8
112	112	-> Append
113	113	-> Parallel Seq Scan on p1
114	114	-> Parallel Seq Scan on p1_c1

		@@ -122,11 +122,11 @@ error hint:
122	122	(12 rows)
123	123
124	124	-- hinting on children makes the whole inheritance parallel
125		-/+Parallel(p1_c1 10 hard)/
	125	+/+Parallel(p1_c1 8 hard)/
126	126	EXPLAIN (COSTS false) SELECT * FROM p1;
127	127	LOG: pg_hint_plan:
128	128	used hint:
129		-Parallel(p1_c1 10 hard)
	129	+Parallel(p1_c1 8 hard)
130	130	not used hint:
131	131	duplication hint:
132	132	error hint:

		@@ -134,7 +134,7 @@ error hint:
134	134	QUERY PLAN
135	135	-------------------------------------------
136	136	Gather
137		- Workers Planned: 10
	137	+ Workers Planned: 8
138	138	-> Append
139	139	-> Parallel Seq Scan on p1
140	140	-> Parallel Seq Scan on p1_c1

		@@ -158,11 +158,11 @@ EXPLAIN (COSTS false) SELECT * FROM p1_c1_c1 join p2_c1_c1 on p1_c1_c1.id = p2_c
158	158	-> Seq Scan on p2_c1_c1
159	159	(5 rows)
160	160
161		-/+Parallel(p1_c1_c1 10 hard)/
	161	+/+Parallel(p1_c1_c1 8 hard)/
162	162	EXPLAIN (COSTS false) SELECT * FROM p1_c1_c1 join p2_c1_c1 on p1_c1_c1.id = p2_c1_c1.id;
163	163	LOG: pg_hint_plan:
164	164	used hint:
165		-Parallel(p1_c1_c1 10 hard)
	165	+Parallel(p1_c1_c1 8 hard)
166	166	not used hint:
167	167	duplication hint:
168	168	error hint:

		@@ -174,18 +174,18 @@ error hint:
174	174	-> Seq Scan on p2_c1_c1
175	175	-> Hash
176	176	-> Gather
177		- Workers Planned: 10
	177	+ Workers Planned: 8
178	178	-> Parallel Seq Scan on p1_c1_c1
179	179	(7 rows)
180	180
181	181	SET parallel_setup_cost to 0;
182	182	SET parallel_tuple_cost to 0;
183	183	SET min_parallel_relation_size to 0;
184		-/+Parallel(p1_c1_c1 10 soft) Parallel(p2_c1_c1 0)/
	184	+/+Parallel(p1_c1_c1 8 soft) Parallel(p2_c1_c1 0)/
185	185	EXPLAIN (COSTS false) SELECT * FROM p1_c1_c1 join p2_c1_c1 on p1_c1_c1.id = p2_c1_c1.id;
186	186	LOG: pg_hint_plan:
187	187	used hint:
188		-Parallel(p1_c1_c1 10 soft)
	188	+Parallel(p1_c1_c1 8 soft)
189	189	Parallel(p2_c1_c1 0 soft)
190	190	not used hint:
191	191	duplication hint:

		@@ -202,11 +202,11 @@ error hint:
202	202	-> Seq Scan on p2_c1_c1
203	203	(7 rows)
204	204
205		-/+Parallel(p1_c1_c1 10 hard) Parallel(p2_c1_c1 0)/
	205	+/+Parallel(p1_c1_c1 8 hard) Parallel(p2_c1_c1 0)/
206	206	EXPLAIN (COSTS false) SELECT * FROM p1_c1_c1 join p2_c1_c1 on p1_c1_c1.id = p2_c1_c1.id;
207	207	LOG: pg_hint_plan:
208	208	used hint:
209		-Parallel(p1_c1_c1 10 hard)
	209	+Parallel(p1_c1_c1 8 hard)
210	210	Parallel(p2_c1_c1 0 soft)
211	211	not used hint:
212	212	duplication hint:

		@@ -215,7 +215,7 @@ error hint:
215	215	QUERY PLAN
216	216	------------------------------------------------
217	217	Gather
218		- Workers Planned: 10
	218	+ Workers Planned: 8
219	219	-> Hash Join
220	220	Hash Cond: (p1_c1_c1.id = p2_c1_c1.id)
221	221	-> Parallel Seq Scan on p1_c1_c1

		@@ -223,12 +223,12 @@ error hint:
223	223	-> Seq Scan on p2_c1_c1
224	224	(7 rows)
225	225
226		-/+Parallel(p1_c1_c1 10 hard) Parallel(p2_c1_c1 10 hard)/
	226	+/+Parallel(p1_c1_c1 8 hard) Parallel(p2_c1_c1 8 hard)/
227	227	EXPLAIN (COSTS false) SELECT * FROM p1_c1_c1 join p2_c1_c1 on p1_c1_c1.id = p2_c1_c1.id;
228	228	LOG: pg_hint_plan:
229	229	used hint:
230		-Parallel(p1_c1_c1 10 hard)
231		-Parallel(p2_c1_c1 10 hard)
	230	+Parallel(p1_c1_c1 8 hard)
	231	+Parallel(p2_c1_c1 8 hard)
232	232	not used hint:
233	233	duplication hint:
234	234	error hint:

		@@ -238,11 +238,11 @@ error hint:
238	238	Hash Join
239	239	Hash Cond: (p1_c1_c1.id = p2_c1_c1.id)
240	240	-> Gather
241		- Workers Planned: 10
	241	+ Workers Planned: 8
242	242	-> Parallel Seq Scan on p1_c1_c1
243	243	-> Hash
244	244	-> Gather
245		- Workers Planned: 10
	245	+ Workers Planned: 8
246	246	-> Parallel Seq Scan on p2_c1_c1
247	247	(9 rows)
248	248

		@@ -250,11 +250,11 @@ error hint:
250	250	SET parallel_setup_cost to 0;
251	251	SET parallel_tuple_cost to 0;
252	252	SET min_parallel_relation_size to 0;
253		-/+Parallel(p1 10)/
	253	+/+Parallel(p1 8)/
254	254	EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
255	255	LOG: pg_hint_plan:
256	256	used hint:
257		-Parallel(p1 10 soft)
	257	+Parallel(p1 8 soft)
258	258	not used hint:
259	259	duplication hint:
260	260	error hint:

		@@ -290,11 +290,11 @@ error hint:
290	290	-> Parallel Seq Scan on p2_c3_c2
291	291	(27 rows)
292	292
293		-/+Parallel(p1 10)Parallel(p2 0)/
	293	+/+Parallel(p1 8)Parallel(p2 0)/
294	294	EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
295	295	LOG: pg_hint_plan:
296	296	used hint:
297		-Parallel(p1 10 soft)
	297	+Parallel(p1 8 soft)
298	298	Parallel(p2 0 soft)
299	299	not used hint:
300	300	duplication hint:

		@@ -332,11 +332,11 @@ error hint:
332	332	SET parallel_setup_cost to DEFAULT;
333	333	SET parallel_tuple_cost to DEFAULT;
334	334	SET min_parallel_relation_size to DEFAULT;
335		-/+Parallel(p2 10 hard)/
	335	+/+Parallel(p2 8 hard)/
336	336	EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
337	337	LOG: pg_hint_plan:
338	338	used hint:
339		-Parallel(p2 10 hard)
	339	+Parallel(p2 8 hard)
340	340	not used hint:
341	341	duplication hint:
342	342	error hint:

		@@ -344,40 +344,40 @@ error hint:
344	344	QUERY PLAN
345	345	-------------------------------------------------------
346	346	Hash Join
347		- Hash Cond: (p1.id = p2.id)
	347	+ Hash Cond: (p2.id = p1.id)
348	348	-> Gather
349		- Workers Planned: 1
	349	+ Workers Planned: 8
350	350	-> Append
351		- -> Parallel Seq Scan on p1
352		- -> Parallel Seq Scan on p1_c1
353		- -> Parallel Seq Scan on p1_c2
354		- -> Parallel Seq Scan on p1_c3
355		- -> Parallel Seq Scan on p1_c4
356		- -> Parallel Seq Scan on p1_c1_c1
357		- -> Parallel Seq Scan on p1_c1_c2
358		- -> Parallel Seq Scan on p1_c3_c1
359		- -> Parallel Seq Scan on p1_c3_c2
	351	+ -> Parallel Seq Scan on p2
	352	+ -> Parallel Seq Scan on p2_c1
	353	+ -> Parallel Seq Scan on p2_c2
	354	+ -> Parallel Seq Scan on p2_c3
	355	+ -> Parallel Seq Scan on p2_c4
	356	+ -> Parallel Seq Scan on p2_c1_c1
	357	+ -> Parallel Seq Scan on p2_c1_c2
	358	+ -> Parallel Seq Scan on p2_c3_c1
	359	+ -> Parallel Seq Scan on p2_c3_c2
360	360	-> Hash
361	361	-> Gather
362		- Workers Planned: 10
	362	+ Workers Planned: 1
363	363	-> Append
364		- -> Parallel Seq Scan on p2
365		- -> Parallel Seq Scan on p2_c1
366		- -> Parallel Seq Scan on p2_c2
367		- -> Parallel Seq Scan on p2_c3
368		- -> Parallel Seq Scan on p2_c4
369		- -> Parallel Seq Scan on p2_c1_c1
370		- -> Parallel Seq Scan on p2_c1_c2
371		- -> Parallel Seq Scan on p2_c3_c1
372		- -> Parallel Seq Scan on p2_c3_c2
	364	+ -> Parallel Seq Scan on p1
	365	+ -> Parallel Seq Scan on p1_c1
	366	+ -> Parallel Seq Scan on p1_c2
	367	+ -> Parallel Seq Scan on p1_c3
	368	+ -> Parallel Seq Scan on p1_c4
	369	+ -> Parallel Seq Scan on p1_c1_c1
	370	+ -> Parallel Seq Scan on p1_c1_c2
	371	+ -> Parallel Seq Scan on p1_c3_c1
	372	+ -> Parallel Seq Scan on p1_c3_c2
373	373	(27 rows)
374	374
375		-/+Parallel(p2 10 hard) Parallel(p1 5 hard) /
	375	+/+Parallel(p2 8 hard) Parallel(p1 5 hard) /
376	376	EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
377	377	LOG: pg_hint_plan:
378	378	used hint:
379	379	Parallel(p1 5 hard)
380		-Parallel(p2 10 hard)
	380	+Parallel(p2 8 hard)
381	381	not used hint:
382	382	duplication hint:
383	383	error hint:

		@@ -385,42 +385,42 @@ error hint:
385	385	QUERY PLAN
386	386	-------------------------------------------------------
387	387	Hash Join
388		- Hash Cond: (p1.id = p2.id)
	388	+ Hash Cond: (p2.id = p1.id)
389	389	-> Gather
390		- Workers Planned: 5
	390	+ Workers Planned: 8
391	391	-> Append
392		- -> Parallel Seq Scan on p1
393		- -> Parallel Seq Scan on p1_c1
394		- -> Parallel Seq Scan on p1_c2
395		- -> Parallel Seq Scan on p1_c3
396		- -> Parallel Seq Scan on p1_c4
397		- -> Parallel Seq Scan on p1_c1_c1
398		- -> Parallel Seq Scan on p1_c1_c2
399		- -> Parallel Seq Scan on p1_c3_c1
400		- -> Parallel Seq Scan on p1_c3_c2
	392	+ -> Parallel Seq Scan on p2
	393	+ -> Parallel Seq Scan on p2_c1
	394	+ -> Parallel Seq Scan on p2_c2
	395	+ -> Parallel Seq Scan on p2_c3
	396	+ -> Parallel Seq Scan on p2_c4
	397	+ -> Parallel Seq Scan on p2_c1_c1
	398	+ -> Parallel Seq Scan on p2_c1_c2
	399	+ -> Parallel Seq Scan on p2_c3_c1
	400	+ -> Parallel Seq Scan on p2_c3_c2
401	401	-> Hash
402	402	-> Gather
403		- Workers Planned: 10
	403	+ Workers Planned: 5
404	404	-> Append
405		- -> Parallel Seq Scan on p2
406		- -> Parallel Seq Scan on p2_c1
407		- -> Parallel Seq Scan on p2_c2
408		- -> Parallel Seq Scan on p2_c3
409		- -> Parallel Seq Scan on p2_c4
410		- -> Parallel Seq Scan on p2_c1_c1
411		- -> Parallel Seq Scan on p2_c1_c2
412		- -> Parallel Seq Scan on p2_c3_c1
413		- -> Parallel Seq Scan on p2_c3_c2
	405	+ -> Parallel Seq Scan on p1
	406	+ -> Parallel Seq Scan on p1_c1
	407	+ -> Parallel Seq Scan on p1_c2
	408	+ -> Parallel Seq Scan on p1_c3
	409	+ -> Parallel Seq Scan on p1_c4
	410	+ -> Parallel Seq Scan on p1_c1_c1
	411	+ -> Parallel Seq Scan on p1_c1_c2
	412	+ -> Parallel Seq Scan on p1_c3_c1
	413	+ -> Parallel Seq Scan on p1_c3_c2
414	414	(27 rows)
415	415
416	416	-- Mixture with a scan hint
417	417	-- p1 can be parallel
418		-/+Parallel(p1 10 hard) IndexScan(p2) /
	418	+/+Parallel(p1 8 hard) IndexScan(p2) /
419	419	EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
420	420	LOG: pg_hint_plan:
421	421	used hint:
422	422	IndexScan(p2)
423		-Parallel(p1 10 hard)
	423	+Parallel(p1 8 hard)
424	424	not used hint:
425	425	duplication hint:
426	426	error hint:

		@@ -430,7 +430,7 @@ error hint:
430	430	Hash Join
431	431	Hash Cond: (p1.id = p2.id)
432	432	-> Gather
433		- Workers Planned: 10
	433	+ Workers Planned: 8
434	434	-> Append
435	435	-> Parallel Seq Scan on p1
436	436	-> Parallel Seq Scan on p1_c1

		@@ -457,12 +457,12 @@ error hint:
457	457	(27 rows)
458	458
459	459	-- seqscan doesn't harm parallelism
460		-/+Parallel(p1 10 hard) SeqScan(p1) /
	460	+/+Parallel(p1 8 hard) SeqScan(p1) /
461	461	EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
462	462	LOG: pg_hint_plan:
463	463	used hint:
464	464	SeqScan(p1)
465		-Parallel(p1 10 hard)
	465	+Parallel(p1 8 hard)
466	466	not used hint:
467	467	duplication hint:
468	468	error hint:

		@@ -472,7 +472,7 @@ error hint:
472	472	Hash Join
473	473	Hash Cond: (p1.id = p2.id)
474	474	-> Gather
475		- Workers Planned: 10
	475	+ Workers Planned: 8
476	476	-> Append
477	477	-> Parallel Seq Scan on p1
478	478	-> Parallel Seq Scan on p1_c1

		@@ -499,12 +499,12 @@ error hint:
499	499	(27 rows)
500	500
501	501	-- parallel overrides index scan
502		-/+Parallel(p1 10 hard) IndexScan(p1) /
	502	+/+Parallel(p1 8 hard) IndexScan(p1) /
503	503	EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
504	504	LOG: pg_hint_plan:
505	505	used hint:
506	506	IndexScan(p1)
507		-Parallel(p1 10 hard)
	507	+Parallel(p1 8 hard)
508	508	not used hint:
509	509	duplication hint:
510	510	error hint:

		@@ -514,7 +514,7 @@ error hint:
514	514	Hash Join
515	515	Hash Cond: (p1.id = p2.id)
516	516	-> Gather
517		- Workers Planned: 10
	517	+ Workers Planned: 8
518	518	-> Append
519	519	-> Parallel Seq Scan on p1
520	520	-> Parallel Seq Scan on p1_c1

		@@ -607,11 +607,11 @@ SET parallel_setup_cost to 0;
607	607	SET parallel_tuple_cost to 0;
608	608	SET min_parallel_relation_size to 0;
609	609	SET max_parallel_workers_per_gather to 0;
610		-/+Parallel(p1 10) /
	610	+/+Parallel(p1 8) /
611	611	EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2;
612	612	LOG: pg_hint_plan:
613	613	used hint:
614		-Parallel(p1 10 soft)
	614	+Parallel(p1 8 soft)
615	615	not used hint:
616	616	duplication hint:
617	617	error hint:

		@@ -680,7 +680,7 @@ error hint:
680	680	SET parallel_setup_cost to DEFAULT;
681	681	SET parallel_tuple_cost to DEFAULT;
682	682	SET min_parallel_relation_size to DEFAULT;
683		-SET max_parallel_workers_per_gather to 10;
	683	+SET max_parallel_workers_per_gather to 8;
684	684	/+Parallel(p1 5 hard)Parallel(p2 6 hard) /
685	685	EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2;
686	686	LOG: pg_hint_plan:

		@@ -721,11 +721,11 @@ SET parallel_setup_cost to 0;
721	721	SET parallel_tuple_cost to 0;
722	722	SET min_parallel_relation_size to 0;
723	723	SET max_parallel_workers_per_gather to 3;
724		-/+Parallel(p1 10 hard) /
	724	+/+Parallel(p1 8 hard) /
725	725	EXPLAIN (COSTS false) SELECT * FROM p1 join t1 on p1.id = t1.id;
726	726	LOG: pg_hint_plan:
727	727	used hint:
728		-Parallel(p1 10 hard)
	728	+Parallel(p1 8 hard)
729	729	not used hint:
730	730	duplication hint:
731	731	error hint:

		@@ -739,7 +739,7 @@ error hint:
739	739	-> Parallel Seq Scan on t1
740	740	-> Hash
741	741	-> Gather
742		- Workers Planned: 10
	742	+ Workers Planned: 8
743	743	-> Append
744	744	-> Parallel Seq Scan on p1
745	745	-> Parallel Seq Scan on p1_c1

		@@ -799,14 +799,14 @@ error hint:
799	799
800	800	-- Errors
801	801	/*+Parallel(p1 100x hard)Parallel(p1 -1000 hard)Parallel(p1 1000000 hard)
802		- Parallel(p1 10 hoge)Parallel(p1)Parallel(p1 100 soft x)*/
	802	+ Parallel(p1 8 hoge)Parallel(p1)Parallel(p1 100 soft x)*/
803	803	EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2;
804	804	INFO: pg_hint_plan: hint syntax error at or near "100x"
805	805	DETAIL: number of workers must be a number: Parallel
806	806	INFO: pg_hint_plan: hint syntax error at or near "-1000"
807	807	DETAIL: number of workers must be positive: Parallel
808	808	INFO: pg_hint_plan: hint syntax error at or near "1000000"
809		-DETAIL: number of workers = 1000000 is larger than max_worker_processes(100): Parallel
	809	+DETAIL: number of workers = 1000000 is larger than max_worker_processes(8): Parallel
810	810	INFO: pg_hint_plan: hint syntax error at or near "hoge"
811	811	DETAIL: enforcement must be soft or hard: Parallel
812	812	INFO: pg_hint_plan: hint syntax error at or near ")"

		@@ -821,7 +821,7 @@ error hint:
821	821	Parallel(p1 100x hard)
822	822	Parallel(p1 -1000 hard)
823	823	Parallel(p1 1000000 hard)
824		-Parallel(p1 10 hard)
	824	+Parallel(p1 8 soft)
825	825	Parallel()
826	826	Parallel()
827	827

--- a/pg_hint_plan.c

+++ b/pg_hint_plan.c

		@@ -2367,20 +2367,16 @@ ParallelHintParse(ParallelHint hint, HintState hstate, Query *parse,
2367	2367	hint_ereport(hint->nworkers_str,
2368	2368	("number of workers must be positive: %s",
2369	2369	hint->base.keyword));
2370		- if ( nworkers > max_worker_processes)
	2370	+ else if (nworkers > max_worker_processes)
2371	2371	hint_ereport(hint->nworkers_str,
2372	2372	("number of workers = %d is larger than max_worker_processes(%d): %s",
2373	2373	nworkers, max_worker_processes, hint->base.keyword));
2374	2374
2375	2375	hint->base.state = HINT_STATE_ERROR;
2376		- return str;
2377	2376	}
2378	2377
2379	2378	hint->nworkers = nworkers;
2380	2379
2381		- if (nworkers > max_hint_nworkers)
2382		- max_hint_nworkers = nworkers;
2383		-
2384	2380	/* optional third parameter is specified */
2385	2381	if (length == 3)
2386	2382	{

		@@ -2393,12 +2389,15 @@ ParallelHintParse(ParallelHint hint, HintState hstate, Query *parse,
2393	2389	("enforcement must be soft or hard: %s",
2394	2390	hint->base.keyword));
2395	2391	hint->base.state = HINT_STATE_ERROR;
2396		- return str;
2397	2392	}
2398	2393	}
2399		-
	2394	+
2400	2395	hint->force_parallel = force_parallel;
2401	2396
	2397	+ if (hint->base.state != HINT_STATE_ERROR &&
	2398	+ nworkers > max_hint_nworkers)
	2399	+ max_hint_nworkers = nworkers;
	2400	+
2402	2401	return str;
2403	2402	}
2404	2403

--- a/sql/ut-W.sql

+++ b/sql/ut-W.sql

		@@ -16,50 +16,50 @@ SET parallel_tuple_cost to 0;
16	16	SET min_parallel_relation_size to 0;
17	17	SET max_parallel_workers_per_gather to DEFAULT;
18	18
19		-/+Parallel(t1 10)/
	19	+/+Parallel(t1 8)/
20	20	EXPLAIN (COSTS false) SELECT * FROM s1.t1;
21	21
22		-/+Parallel(t1 10 soft)/
	22	+/+Parallel(t1 8 soft)/
23	23	EXPLAIN (COSTS false) SELECT * FROM s1.t1;
24	24
25		-/+Parallel(t1 10 hard)/
	25	+/+Parallel(t1 8 hard)/
26	26	EXPLAIN (COSTS false) SELECT * FROM s1.t1;
27	27
28	28	-- Queries on inheritance tables
29	29	SET parallel_setup_cost to 0;
30	30	SET parallel_tuple_cost to 0;
31	31	SET min_parallel_relation_size to 0;
32		-/+Parallel(p1 10)/
	32	+/+Parallel(p1 8)/
33	33	EXPLAIN (COSTS false) SELECT * FROM p1;
34	34	SET parallel_setup_cost to DEFAULT;
35	35	SET parallel_tuple_cost to DEFAULT;
36	36	SET min_parallel_relation_size to DEFAULT;
37	37
38		-/+Parallel(p1 10 hard)/
	38	+/+Parallel(p1 8 hard)/
39	39	EXPLAIN (COSTS false) SELECT * FROM p1;
40	40
41	41	-- hinting on children makes the whole inheritance parallel
42		-/+Parallel(p1_c1 10 hard)/
	42	+/+Parallel(p1_c1 8 hard)/
43	43	EXPLAIN (COSTS false) SELECT * FROM p1;
44	44
45	45
46	46	-- Joins
47	47	EXPLAIN (COSTS false) SELECT * FROM p1_c1_c1 join p2_c1_c1 on p1_c1_c1.id = p2_c1_c1.id;
48	48
49		-/+Parallel(p1_c1_c1 10 hard)/
	49	+/+Parallel(p1_c1_c1 8 hard)/
50	50	EXPLAIN (COSTS false) SELECT * FROM p1_c1_c1 join p2_c1_c1 on p1_c1_c1.id = p2_c1_c1.id;
51	51
52	52	SET parallel_setup_cost to 0;
53	53	SET parallel_tuple_cost to 0;
54	54	SET min_parallel_relation_size to 0;
55	55
56		-/+Parallel(p1_c1_c1 10 soft) Parallel(p2_c1_c1 0)/
	56	+/+Parallel(p1_c1_c1 8 soft) Parallel(p2_c1_c1 0)/
57	57	EXPLAIN (COSTS false) SELECT * FROM p1_c1_c1 join p2_c1_c1 on p1_c1_c1.id = p2_c1_c1.id;
58	58
59		-/+Parallel(p1_c1_c1 10 hard) Parallel(p2_c1_c1 0)/
	59	+/+Parallel(p1_c1_c1 8 hard) Parallel(p2_c1_c1 0)/
60	60	EXPLAIN (COSTS false) SELECT * FROM p1_c1_c1 join p2_c1_c1 on p1_c1_c1.id = p2_c1_c1.id;
61	61
62		-/+Parallel(p1_c1_c1 10 hard) Parallel(p2_c1_c1 10 hard)/
	62	+/+Parallel(p1_c1_c1 8 hard) Parallel(p2_c1_c1 8 hard)/
63	63	EXPLAIN (COSTS false) SELECT * FROM p1_c1_c1 join p2_c1_c1 on p1_c1_c1.id = p2_c1_c1.id;
64	64
65	65

		@@ -67,34 +67,34 @@ EXPLAIN (COSTS false) SELECT * FROM p1_c1_c1 join p2_c1_c1 on p1_c1_c1.id = p2_c
67	67	SET parallel_setup_cost to 0;
68	68	SET parallel_tuple_cost to 0;
69	69	SET min_parallel_relation_size to 0;
70		-/+Parallel(p1 10)/
	70	+/+Parallel(p1 8)/
71	71	EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
72	72
73		-/+Parallel(p1 10)Parallel(p2 0)/
	73	+/+Parallel(p1 8)Parallel(p2 0)/
74	74	EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
75	75
76	76	SET parallel_setup_cost to DEFAULT;
77	77	SET parallel_tuple_cost to DEFAULT;
78	78	SET min_parallel_relation_size to DEFAULT;
79	79
80		-/+Parallel(p2 10 hard)/
	80	+/+Parallel(p2 8 hard)/
81	81	EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
82	82
83		-/+Parallel(p2 10 hard) Parallel(p1 5 hard) /
	83	+/+Parallel(p2 8 hard) Parallel(p1 5 hard) /
84	84	EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
85	85
86	86
87	87	-- Mixture with a scan hint
88	88	-- p1 can be parallel
89		-/+Parallel(p1 10 hard) IndexScan(p2) /
	89	+/+Parallel(p1 8 hard) IndexScan(p2) /
90	90	EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
91	91
92	92	-- seqscan doesn't harm parallelism
93		-/+Parallel(p1 10 hard) SeqScan(p1) /
	93	+/+Parallel(p1 8 hard) SeqScan(p1) /
94	94	EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
95	95
96	96	-- parallel overrides index scan
97		-/+Parallel(p1 10 hard) IndexScan(p1) /
	97	+/+Parallel(p1 8 hard) IndexScan(p1) /
98	98	EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
99	99	/+Parallel(p1 0 hard) IndexScan(p1) /
100	100	EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;

		@@ -109,7 +109,7 @@ SET parallel_tuple_cost to 0;
109	109	SET min_parallel_relation_size to 0;
110	110	SET max_parallel_workers_per_gather to 0;
111	111
112		-/+Parallel(p1 10) /
	112	+/+Parallel(p1 8) /
113	113	EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2;
114	114
115	115	-- set hint also does

		@@ -120,7 +120,7 @@ EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2;
120	120	SET parallel_setup_cost to DEFAULT;
121	121	SET parallel_tuple_cost to DEFAULT;
122	122	SET min_parallel_relation_size to DEFAULT;
123		-SET max_parallel_workers_per_gather to 10;
	123	+SET max_parallel_workers_per_gather to 8;
124	124	/+Parallel(p1 5 hard)Parallel(p2 6 hard) /
125	125	EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2;
126	126

		@@ -131,7 +131,7 @@ SET parallel_tuple_cost to 0;
131	131	SET min_parallel_relation_size to 0;
132	132	SET max_parallel_workers_per_gather to 3;
133	133
134		-/+Parallel(p1 10 hard) /
	134	+/+Parallel(p1 8 hard) /
135	135	EXPLAIN (COSTS false) SELECT * FROM p1 join t1 on p1.id = t1.id;
136	136
137	137	-- Negative hint

		@@ -146,7 +146,7 @@ EXPLAIN (COSTS false) SELECT * FROM p1;
146	146
147	147	-- Errors
148	148	/*+Parallel(p1 100x hard)Parallel(p1 -1000 hard)Parallel(p1 1000000 hard)
149		- Parallel(p1 10 hoge)Parallel(p1)Parallel(p1 100 soft x)*/
	149	+ Parallel(p1 8 hoge)Parallel(p1)Parallel(p1 100 soft x)*/
150	150	EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2;
151	151
152	152	ALTER SYSTEM SET session_preload_libraries TO DEFAULT;

旧リポジトリブラウザで表示

PostgreSQL で ヒント句を使う Fork

pg_hint_plan: コミット

コミットメタ情報

ログメッセージ

変更サマリ

差分

PostgreSQL でヒント句を使う
Fork