• R/O
  • HTTP
  • SSH
  • HTTPS

pg_hint_plan: Commit

firtst release


Commit MetaInfo

Revision3b588bf3256c4edec94f94f28a42cdc6e36aec39 (tree)
Time2019-02-26 18:27:46
AuthorKyotaro Horiguchi <horiguchi.kyotaro@lab....>
CommiterKyotaro Horiguchi

Log Message

Follow the behavior change of PostgreSQL

Some behavioral changes of PostgreSQL breaks regression test. Follow
the changes.

Change Summary

Incremental Difference

--- a/expected/init.out
+++ b/expected/init.out
@@ -159,8 +159,9 @@ SELECT name, setting, category
159159 OR name = 'client_min_messages'
160160 ORDER BY category, name;
161161 SELECT * FROM settings;
162- name | setting | category
163---------------------------------+-----------+---------------------------------------------
162+ name | setting | category
163+--------------------------------+-----------+-------------------------------------------------
164+ client_min_messages | notice | Client Connection Defaults / Statement Behavior
164165 geqo | on | Query Tuning / Genetic Query Optimizer
165166 geqo_effort | 5 | Query Tuning / Genetic Query Optimizer
166167 geqo_generations | 0 | Query Tuning / Genetic Query Optimizer
@@ -205,7 +206,6 @@ SELECT * FROM settings;
205206 enable_seqscan | on | Query Tuning / Planner Method Configuration
206207 enable_sort | on | Query Tuning / Planner Method Configuration
207208 enable_tidscan | on | Query Tuning / Planner Method Configuration
208- client_min_messages | notice | Reporting and Logging / When to Log
209209 (45 rows)
210210
211211 ANALYZE;
--- a/expected/pg_hint_plan.out
+++ b/expected/pg_hint_plan.out
@@ -11,13 +11,13 @@ EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
1111 (4 rows)
1212
1313 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.val = t2.val;
14- QUERY PLAN
15--------------------------------------------
16- Merge Join
17- Merge Cond: (t2.val = t1.val)
18- -> Index Scan using t2_val on t2
19- -> Materialize
20- -> Index Scan using t1_val on t1
14+ QUERY PLAN
15+--------------------------------
16+ Hash Join
17+ Hash Cond: (t2.val = t1.val)
18+ -> Seq Scan on t2
19+ -> Hash
20+ -> Seq Scan on t1
2121 (5 rows)
2222
2323 LOAD 'pg_hint_plan';
@@ -32,13 +32,13 @@ EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
3232 (4 rows)
3333
3434 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.val = t2.val;
35- QUERY PLAN
36--------------------------------------------
37- Merge Join
38- Merge Cond: (t2.val = t1.val)
39- -> Index Scan using t2_val on t2
40- -> Materialize
41- -> Index Scan using t1_val on t1
35+ QUERY PLAN
36+--------------------------------
37+ Hash Join
38+ Hash Cond: (t2.val = t1.val)
39+ -> Seq Scan on t2
40+ -> Hash
41+ -> Seq Scan on t1
4242 (5 rows)
4343
4444 /*+ Test (t1 t2) */
--- a/expected/ut-A.out
+++ b/expected/ut-A.out
@@ -2016,6 +2016,8 @@ ERROR: pg_hint_plan: hint syntax error at or near ""
20162016 DETAIL: Opening parenthesis is necessary.
20172017 SET client_min_messages TO fatal;
20182018 /*+Set*/SELECT 1;
2019+ERROR: pg_hint_plan: hint syntax error at or near ""
2020+DETAIL: Opening parenthesis is necessary.
20192021 -- No. A-8-4-11
20202022 RESET client_min_messages;
20212023 SET pg_hint_plan.parse_messages TO DEFAULT;
@@ -3226,6 +3228,7 @@ NestLoop(t1 t1)
32263228 SELECT name, setting FROM settings;
32273229 name | setting
32283230 --------------------------------+-----------
3231+ client_min_messages | log
32293232 geqo | on
32303233 geqo_effort | 5
32313234 geqo_generations | 0
@@ -3270,7 +3273,6 @@ SELECT name, setting FROM settings;
32703273 enable_seqscan | on
32713274 enable_sort | on
32723275 enable_tidscan | on
3273- client_min_messages | log
32743276 (45 rows)
32753277
32763278 SET pg_hint_plan.parse_messages TO error;
@@ -3281,6 +3283,7 @@ DETAIL: Relation name "t1" is duplicated.
32813283 SELECT name, setting FROM settings;
32823284 name | setting
32833285 --------------------------------+-----------
3286+ client_min_messages | log
32843287 geqo | on
32853288 geqo_effort | 5
32863289 geqo_generations | 0
@@ -3325,7 +3328,6 @@ SELECT name, setting FROM settings;
33253328 enable_seqscan | on
33263329 enable_sort | on
33273330 enable_tidscan | on
3328- client_min_messages | log
33293331 (45 rows)
33303332
33313333 /*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)*/
@@ -3355,6 +3357,7 @@ error hint:
33553357 SELECT name, setting FROM settings;
33563358 name | setting
33573359 --------------------------------+-----------
3360+ client_min_messages | log
33583361 geqo | on
33593362 geqo_effort | 5
33603363 geqo_generations | 0
@@ -3399,7 +3402,6 @@ SELECT name, setting FROM settings;
33993402 enable_seqscan | on
34003403 enable_sort | on
34013404 enable_tidscan | on
3402- client_min_messages | log
34033405 (45 rows)
34043406
34053407 SET pg_hint_plan.parse_messages TO error;
@@ -3410,6 +3412,7 @@ DETAIL: Relation name "t1" is duplicated.
34103412 SELECT name, setting FROM settings;
34113413 name | setting
34123414 --------------------------------+-----------
3415+ client_min_messages | log
34133416 geqo | on
34143417 geqo_effort | 5
34153418 geqo_generations | 0
@@ -3454,7 +3457,6 @@ SELECT name, setting FROM settings;
34543457 enable_seqscan | on
34553458 enable_sort | on
34563459 enable_tidscan | on
3457- client_min_messages | log
34583460 (45 rows)
34593461
34603462 EXPLAIN (COSTS false) EXECUTE p1;
@@ -3473,6 +3475,7 @@ EXPLAIN (COSTS false) EXECUTE p1;
34733475 SELECT name, setting FROM settings;
34743476 name | setting
34753477 --------------------------------+-----------
3478+ client_min_messages | log
34763479 geqo | on
34773480 geqo_effort | 5
34783481 geqo_generations | 0
@@ -3517,7 +3520,6 @@ SELECT name, setting FROM settings;
35173520 enable_seqscan | on
35183521 enable_sort | on
35193522 enable_tidscan | on
3520- client_min_messages | log
35213523 (45 rows)
35223524
35233525 SET pg_hint_plan.parse_messages TO error;
@@ -3559,6 +3561,7 @@ EXPLAIN (COSTS false) EXECUTE p1;
35593561 SELECT name, setting FROM settings;
35603562 name | setting
35613563 --------------------------------+-----------
3564+ client_min_messages | log
35623565 geqo | on
35633566 geqo_effort | 5
35643567 geqo_generations | 0
@@ -3603,7 +3606,6 @@ SELECT name, setting FROM settings;
36033606 enable_seqscan | on
36043607 enable_sort | on
36053608 enable_tidscan | on
3606- client_min_messages | log
36073609 (45 rows)
36083610
36093611 -- No. A-12-1-4
@@ -3611,6 +3613,7 @@ SELECT name, setting FROM settings;
36113613 SELECT name, setting FROM settings;
36123614 name | setting
36133615 --------------------------------+-----------
3616+ client_min_messages | log
36143617 geqo | on
36153618 geqo_effort | 5
36163619 geqo_generations | 0
@@ -3655,7 +3658,6 @@ SELECT name, setting FROM settings;
36553658 enable_seqscan | on
36563659 enable_sort | on
36573660 enable_tidscan | on
3658- client_min_messages | log
36593661 (45 rows)
36603662
36613663 SET pg_hint_plan.parse_messages TO error;
@@ -3675,6 +3677,7 @@ EXPLAIN (COSTS false) EXECUTE p1;
36753677 SELECT name, setting FROM settings;
36763678 name | setting
36773679 --------------------------------+-----------
3680+ client_min_messages | log
36783681 geqo | on
36793682 geqo_effort | 5
36803683 geqo_generations | 0
@@ -3719,7 +3722,6 @@ SELECT name, setting FROM settings;
37193722 enable_seqscan | on
37203723 enable_sort | on
37213724 enable_tidscan | on
3722- client_min_messages | log
37233725 (45 rows)
37243726
37253727 DEALLOCATE p1;
@@ -3754,6 +3756,7 @@ EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
37543756 SELECT name, setting FROM settings;
37553757 name | setting
37563758 --------------------------------+-----------
3759+ client_min_messages | log
37573760 geqo | on
37583761 geqo_effort | 5
37593762 geqo_generations | 0
@@ -3798,7 +3801,6 @@ SELECT name, setting FROM settings;
37983801 enable_seqscan | on
37993802 enable_sort | on
38003803 enable_tidscan | on
3801- client_min_messages | log
38023804 (45 rows)
38033805
38043806 /*+Set(enable_indexscan on)Set(geqo_threshold 100)IndexScan(t2)MergeJoin(t1 t2)Leading(t2 t1)*/
@@ -3825,6 +3827,7 @@ error hint:
38253827 SELECT name, setting FROM settings;
38263828 name | setting
38273829 --------------------------------+-----------
3830+ client_min_messages | log
38283831 geqo | on
38293832 geqo_effort | 5
38303833 geqo_generations | 0
@@ -3869,7 +3872,6 @@ SELECT name, setting FROM settings;
38693872 enable_seqscan | on
38703873 enable_sort | on
38713874 enable_tidscan | on
3872- client_min_messages | log
38733875 (45 rows)
38743876
38753877 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
@@ -3898,6 +3900,7 @@ EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
38983900 SELECT name, setting FROM settings;
38993901 name | setting
39003902 --------------------------------+-----------
3903+ client_min_messages | log
39013904 geqo | on
39023905 geqo_effort | 5
39033906 geqo_generations | 0
@@ -3942,7 +3945,6 @@ SELECT name, setting FROM settings;
39423945 enable_seqscan | on
39433946 enable_sort | on
39443947 enable_tidscan | on
3945- client_min_messages | log
39463948 (45 rows)
39473949
39483950 BEGIN;
@@ -3972,6 +3974,7 @@ BEGIN;
39723974 SELECT name, setting FROM settings;
39733975 name | setting
39743976 --------------------------------+-----------
3977+ client_min_messages | log
39753978 geqo | on
39763979 geqo_effort | 5
39773980 geqo_generations | 0
@@ -4016,7 +4019,6 @@ SELECT name, setting FROM settings;
40164019 enable_seqscan | on
40174020 enable_sort | on
40184021 enable_tidscan | on
4019- client_min_messages | log
40204022 (45 rows)
40214023
40224024 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
@@ -4046,6 +4048,7 @@ EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
40464048 SELECT name, setting FROM settings;
40474049 name | setting
40484050 --------------------------------+-----------
4051+ client_min_messages | log
40494052 geqo | on
40504053 geqo_effort | 5
40514054 geqo_generations | 0
@@ -4090,7 +4093,6 @@ SELECT name, setting FROM settings;
40904093 enable_seqscan | on
40914094 enable_sort | on
40924095 enable_tidscan | on
4093- client_min_messages | log
40944096 (45 rows)
40954097
40964098 /*+Set(enable_indexscan on)Set(geqo_threshold 100)IndexScan(t2)MergeJoin(t1 t2)Leading(t2 t1)*/
@@ -4121,6 +4123,7 @@ LOAD 'pg_hint_plan';
41214123 SELECT name, setting FROM settings;
41224124 name | setting
41234125 --------------------------------+-----------
4126+ client_min_messages | notice
41244127 geqo | on
41254128 geqo_effort | 5
41264129 geqo_generations | 0
@@ -4165,7 +4168,6 @@ SELECT name, setting FROM settings;
41654168 enable_seqscan | on
41664169 enable_sort | on
41674170 enable_tidscan | on
4168- client_min_messages | notice
41694171 (45 rows)
41704172
41714173 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
--- a/expected/ut-S.out
+++ b/expected/ut-S.out
@@ -1888,7 +1888,7 @@ error hint:
18881888 Filter: (ctid = '(1,1)'::tid)
18891889 -> Bitmap Index Scan on t4_pkey
18901890 Index Cond: (c1 = b1t2.c1)
1891- InitPlan 2 (returns $4)
1891+ InitPlan 2 (returns $5)
18921892 -> Aggregate
18931893 -> Nested Loop
18941894 Join Filter: (b2t1.c1 = b2t4.c1)
@@ -1908,7 +1908,7 @@ error hint:
19081908 -> Index Scan using t4_pkey on t4 b2t4
19091909 Index Cond: (c1 = b2t2.c1)
19101910 Filter: (ctid = '(1,1)'::tid)
1911- InitPlan 3 (returns $7)
1911+ InitPlan 3 (returns $8)
19121912 -> Aggregate
19131913 -> Nested Loop
19141914 Join Filter: (b3t1.c1 = b3t2.c1)
@@ -1937,7 +1937,7 @@ error hint:
19371937 -> Nested Loop
19381938 Join Filter: (bmt1.c1 = bmt4.c1)
19391939 -> Seq Scan on t1 bmt1
1940- Filter: ((c1 <> $7) AND (ctid = '(1,1)'::tid))
1940+ Filter: ((c1 <> $8) AND (ctid = '(1,1)'::tid))
19411941 -> Tid Scan on t4 bmt4
19421942 TID Cond: (ctid = '(1,1)'::tid)
19431943 -> Index Scan using t2_pkey on t2 bmt2
@@ -2078,18 +2078,18 @@ error hint:
20782078 Filter: (ctid = '(1,1)'::tid)
20792079 -> Bitmap Index Scan on t4_pkey
20802080 Index Cond: (c1 = b1t2.c1)
2081- InitPlan 3 (returns $3)
2081+ InitPlan 3 (returns $4)
20822082 -> Result
2083- InitPlan 2 (returns $2)
2083+ InitPlan 2 (returns $3)
20842084 -> Limit
20852085 -> Bitmap Heap Scan on t1 b2t1
20862086 Recheck Cond: ((c1 IS NOT NULL) AND (c1 = 1))
20872087 Filter: (ctid = '(1,1)'::tid)
20882088 -> Bitmap Index Scan on t1_pkey
20892089 Index Cond: ((c1 IS NOT NULL) AND (c1 = 1))
2090- InitPlan 5 (returns $5)
2090+ InitPlan 5 (returns $6)
20912091 -> Result
2092- InitPlan 4 (returns $4)
2092+ InitPlan 4 (returns $5)
20932093 -> Limit
20942094 -> Index Scan Backward using t1_pkey on t1 b3t1
20952095 Index Cond: (c1 IS NOT NULL)
@@ -2103,7 +2103,7 @@ error hint:
21032103 -> Nested Loop
21042104 Join Filter: (bmt1.c1 = bmt4.c1)
21052105 -> Seq Scan on t1 bmt1
2106- Filter: ((c1 <> $5) AND (ctid = '(1,1)'::tid))
2106+ Filter: ((c1 <> $6) AND (ctid = '(1,1)'::tid))
21072107 -> Tid Scan on t4 bmt4
21082108 TID Cond: (ctid = '(1,1)'::tid)
21092109 -> Index Scan using t2_pkey on t2 bmt2
--- a/output/ut-W.source
+++ b/output/ut-W.source
@@ -347,6 +347,7 @@ SET parallel_setup_cost to 0;
347347 SET parallel_tuple_cost to 0;
348348 SET min_parallel_table_scan_size to 0;
349349 SET min_parallel_index_scan_size to 0;
350+SET max_parallel_workers_per_gather to 0;
350351 SET enable_parallel_append to false;
351352 /*+Parallel(p1 8)*/
352353 EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
@@ -399,14 +400,14 @@ error hint:
399400 QUERY PLAN
400401 -------------------------------------------------------
401402 Gather
402- Workers Planned: 2
403+ Workers Planned: 1
403404 -> Parallel Hash Join
404- Hash Cond: (p1.id = p2.id)
405+ Hash Cond: (p1.id = p2_c2.id)
405406 -> Parallel Append
406- -> Seq Scan on p1
407- -> Seq Scan on p1_c1
408- -> Seq Scan on p1_c3
407+ -> Parallel Seq Scan on p1
408+ -> Parallel Seq Scan on p1_c1
409409 -> Parallel Seq Scan on p1_c2
410+ -> Parallel Seq Scan on p1_c3
410411 -> Parallel Seq Scan on p1_c4
411412 -> Parallel Seq Scan on p1_c1_c1
412413 -> Parallel Seq Scan on p1_c1_c2
@@ -414,15 +415,15 @@ error hint:
414415 -> Parallel Seq Scan on p1_c3_c2
415416 -> Parallel Hash
416417 -> Parallel Append
417- -> Seq Scan on p2
418- -> Seq Scan on p2_c1
419- -> Seq Scan on p2_c3
420418 -> Parallel Seq Scan on p2_c2
421419 -> Parallel Seq Scan on p2_c4
422420 -> Parallel Seq Scan on p2_c1_c1
423421 -> Parallel Seq Scan on p2_c1_c2
424422 -> Parallel Seq Scan on p2_c3_c1
425423 -> Parallel Seq Scan on p2_c3_c2
424+ -> Parallel Seq Scan on p2
425+ -> Parallel Seq Scan on p2_c1
426+ -> Parallel Seq Scan on p2_c3
426427 (25 rows)
427428
428429 SET enable_parallel_append to false;
@@ -479,14 +480,14 @@ error hint:
479480 QUERY PLAN
480481 -------------------------------------------------
481482 Gather
482- Workers Planned: 2
483+ Workers Planned: 1
483484 -> Parallel Hash Join
484485 Hash Cond: (p1.id = p2_c2.id)
485486 -> Parallel Append
486- -> Seq Scan on p1
487- -> Seq Scan on p1_c1
488- -> Seq Scan on p1_c3
487+ -> Parallel Seq Scan on p1
488+ -> Parallel Seq Scan on p1_c1
489489 -> Parallel Seq Scan on p1_c2
490+ -> Parallel Seq Scan on p1_c3
490491 -> Parallel Seq Scan on p1_c4
491492 -> Parallel Seq Scan on p1_c1_c1
492493 -> Parallel Seq Scan on p1_c1_c2
@@ -556,31 +557,31 @@ error hint:
556557
557558 QUERY PLAN
558559 -------------------------------------------------------
559- Gather
560- Workers Planned: 8
561- -> Parallel Hash Join
562- Hash Cond: (p2.id = p1.id)
563- -> Parallel Append
564- -> Seq Scan on p2
565- -> Seq Scan on p2_c1
566- -> Seq Scan on p2_c3
567- -> Parallel Seq Scan on p2_c2
568- -> Parallel Seq Scan on p2_c4
569- -> Parallel Seq Scan on p2_c1_c1
570- -> Parallel Seq Scan on p2_c1_c2
571- -> Parallel Seq Scan on p2_c3_c1
572- -> Parallel Seq Scan on p2_c3_c2
573- -> Parallel Hash
560+ Hash Join
561+ Hash Cond: (p1.id = p2.id)
562+ -> Append
563+ -> Seq Scan on p1
564+ -> Seq Scan on p1_c1
565+ -> Seq Scan on p1_c2
566+ -> Seq Scan on p1_c3
567+ -> Seq Scan on p1_c4
568+ -> Seq Scan on p1_c1_c1
569+ -> Seq Scan on p1_c1_c2
570+ -> Seq Scan on p1_c3_c1
571+ -> Seq Scan on p1_c3_c2
572+ -> Hash
573+ -> Gather
574+ Workers Planned: 8
574575 -> Parallel Append
575- -> Seq Scan on p1
576- -> Seq Scan on p1_c1
577- -> Seq Scan on p1_c3
578- -> Parallel Seq Scan on p1_c2
579- -> Parallel Seq Scan on p1_c4
580- -> Parallel Seq Scan on p1_c1_c1
581- -> Parallel Seq Scan on p1_c1_c2
582- -> Parallel Seq Scan on p1_c3_c1
583- -> Parallel Seq Scan on p1_c3_c2
576+ -> Seq Scan on p2
577+ -> Seq Scan on p2_c1
578+ -> Seq Scan on p2_c3
579+ -> Parallel Seq Scan on p2_c2
580+ -> Parallel Seq Scan on p2_c4
581+ -> Parallel Seq Scan on p2_c1_c1
582+ -> Parallel Seq Scan on p2_c1_c2
583+ -> Parallel Seq Scan on p2_c3_c1
584+ -> Parallel Seq Scan on p2_c3_c2
584585 (25 rows)
585586
586587 -- Number of workers results to the largest number
@@ -677,33 +678,33 @@ not used hint:
677678 duplication hint:
678679 error hint:
679680
680- QUERY PLAN
681------------------------------------------------------------------------------------
682- Gather
683- Workers Planned: 8
684- -> Parallel Hash Join
685- Hash Cond: (p1.id = p2.id)
686- -> Append
687- -> Parallel Seq Scan on p1
688- -> Parallel Seq Scan on p1_c1
689- -> Parallel Seq Scan on p1_c2
690- -> Parallel Seq Scan on p1_c3
691- -> Parallel Seq Scan on p1_c4
692- -> Parallel Seq Scan on p1_c1_c1
693- -> Parallel Seq Scan on p1_c1_c2
694- -> Parallel Seq Scan on p1_c3_c1
695- -> Parallel Seq Scan on p1_c3_c2
696- -> Parallel Hash
681+ QUERY PLAN
682+--------------------------------------------------------------
683+ Hash Join
684+ Hash Cond: (p2.id = p1.id)
685+ -> Append
686+ -> Index Scan using p2_id2_val on p2
687+ -> Index Scan using p2_c1_id2_val on p2_c1
688+ -> Index Scan using p2_c2_id2_val on p2_c2
689+ -> Index Scan using p2_c3_id_val_idx on p2_c3
690+ -> Index Scan using p2_c4_id_val_idx on p2_c4
691+ -> Index Scan using p2_c1_c1_id_val_idx on p2_c1_c1
692+ -> Index Scan using p2_c1_c2_id_val_idx on p2_c1_c2
693+ -> Index Scan using p2_c3_c1_id_val_idx on p2_c3_c1
694+ -> Index Scan using p2_c3_c2_id_val_idx on p2_c3_c2
695+ -> Hash
696+ -> Gather
697+ Workers Planned: 8
697698 -> Append
698- -> Parallel Index Scan using p2_id2_val on p2
699- -> Parallel Index Scan using p2_c1_id2_val on p2_c1
700- -> Parallel Index Scan using p2_c2_id2_val on p2_c2
701- -> Parallel Index Scan using p2_c3_id_val_idx on p2_c3
702- -> Parallel Index Scan using p2_c4_id_val_idx on p2_c4
703- -> Parallel Index Scan using p2_c1_c1_id_val_idx on p2_c1_c1
704- -> Parallel Index Scan using p2_c1_c2_id_val_idx on p2_c1_c2
705- -> Parallel Index Scan using p2_c3_c1_id_val_idx on p2_c3_c1
706- -> Parallel Index Scan using p2_c3_c2_id_val_idx on p2_c3_c2
699+ -> Parallel Seq Scan on p1
700+ -> Parallel Seq Scan on p1_c1
701+ -> Parallel Seq Scan on p1_c2
702+ -> Parallel Seq Scan on p1_c3
703+ -> Parallel Seq Scan on p1_c4
704+ -> Parallel Seq Scan on p1_c1_c1
705+ -> Parallel Seq Scan on p1_c1_c2
706+ -> Parallel Seq Scan on p1_c3_c1
707+ -> Parallel Seq Scan on p1_c3_c2
707708 (25 rows)
708709
709710 SET enable_parallel_append to true;
@@ -717,33 +718,33 @@ not used hint:
717718 duplication hint:
718719 error hint:
719720
720- QUERY PLAN
721------------------------------------------------------------------------------------
722- Gather
723- Workers Planned: 8
724- -> Parallel Hash Join
725- Hash Cond: (p1.id = p2_c2.id)
726- -> Parallel Append
727- -> Seq Scan on p1
728- -> Seq Scan on p1_c1
729- -> Seq Scan on p1_c3
730- -> Parallel Seq Scan on p1_c2
731- -> Parallel Seq Scan on p1_c4
732- -> Parallel Seq Scan on p1_c1_c1
733- -> Parallel Seq Scan on p1_c1_c2
734- -> Parallel Seq Scan on p1_c3_c1
735- -> Parallel Seq Scan on p1_c3_c2
736- -> Parallel Hash
721+ QUERY PLAN
722+--------------------------------------------------------------
723+ Hash Join
724+ Hash Cond: (p2.id = p1.id)
725+ -> Append
726+ -> Index Scan using p2_id2_val on p2
727+ -> Index Scan using p2_c1_id2_val on p2_c1
728+ -> Index Scan using p2_c2_id2_val on p2_c2
729+ -> Index Scan using p2_c3_id_val_idx on p2_c3
730+ -> Index Scan using p2_c4_id_val_idx on p2_c4
731+ -> Index Scan using p2_c1_c1_id_val_idx on p2_c1_c1
732+ -> Index Scan using p2_c1_c2_id_val_idx on p2_c1_c2
733+ -> Index Scan using p2_c3_c1_id_val_idx on p2_c3_c1
734+ -> Index Scan using p2_c3_c2_id_val_idx on p2_c3_c2
735+ -> Hash
736+ -> Gather
737+ Workers Planned: 8
737738 -> Parallel Append
738- -> Parallel Index Scan using p2_c2_id2_val on p2_c2
739- -> Parallel Index Scan using p2_c4_id_val_idx on p2_c4
740- -> Parallel Index Scan using p2_c1_c1_id_val_idx on p2_c1_c1
741- -> Parallel Index Scan using p2_c1_c2_id_val_idx on p2_c1_c2
742- -> Parallel Index Scan using p2_c3_c1_id_val_idx on p2_c3_c1
743- -> Parallel Index Scan using p2_c3_c2_id_val_idx on p2_c3_c2
744- -> Parallel Index Scan using p2_id2_val on p2
745- -> Parallel Index Scan using p2_c1_id2_val on p2_c1
746- -> Parallel Index Scan using p2_c3_id_val_idx on p2_c3
739+ -> Seq Scan on p1
740+ -> Seq Scan on p1_c1
741+ -> Seq Scan on p1_c3
742+ -> Parallel Seq Scan on p1_c2
743+ -> Parallel Seq Scan on p1_c4
744+ -> Parallel Seq Scan on p1_c1_c1
745+ -> Parallel Seq Scan on p1_c1_c2
746+ -> Parallel Seq Scan on p1_c3_c1
747+ -> Parallel Seq Scan on p1_c3_c2
747748 (25 rows)
748749
749750 -- Parallel sequential scan
@@ -800,31 +801,31 @@ error hint:
800801
801802 QUERY PLAN
802803 -------------------------------------------------------
803- Gather
804- Workers Planned: 8
805- -> Parallel Hash Join
806- Hash Cond: (p1.id = p2.id)
807- -> Parallel Append
808- -> Seq Scan on p1
809- -> Seq Scan on p1_c1
810- -> Seq Scan on p1_c3
811- -> Parallel Seq Scan on p1_c2
812- -> Parallel Seq Scan on p1_c4
813- -> Parallel Seq Scan on p1_c1_c1
814- -> Parallel Seq Scan on p1_c1_c2
815- -> Parallel Seq Scan on p1_c3_c1
816- -> Parallel Seq Scan on p1_c3_c2
817- -> Parallel Hash
804+ Hash Join
805+ Hash Cond: (p2.id = p1.id)
806+ -> Append
807+ -> Seq Scan on p2
808+ -> Seq Scan on p2_c1
809+ -> Seq Scan on p2_c2
810+ -> Seq Scan on p2_c3
811+ -> Seq Scan on p2_c4
812+ -> Seq Scan on p2_c1_c1
813+ -> Seq Scan on p2_c1_c2
814+ -> Seq Scan on p2_c3_c1
815+ -> Seq Scan on p2_c3_c2
816+ -> Hash
817+ -> Gather
818+ Workers Planned: 8
818819 -> Parallel Append
819- -> Seq Scan on p2
820- -> Seq Scan on p2_c1
821- -> Seq Scan on p2_c3
822- -> Parallel Seq Scan on p2_c2
823- -> Parallel Seq Scan on p2_c4
824- -> Parallel Seq Scan on p2_c1_c1
825- -> Parallel Seq Scan on p2_c1_c2
826- -> Parallel Seq Scan on p2_c3_c1
827- -> Parallel Seq Scan on p2_c3_c2
820+ -> Seq Scan on p1
821+ -> Seq Scan on p1_c1
822+ -> Seq Scan on p1_c3
823+ -> Parallel Seq Scan on p1_c2
824+ -> Parallel Seq Scan on p1_c4
825+ -> Parallel Seq Scan on p1_c1_c1
826+ -> Parallel Seq Scan on p1_c1_c2
827+ -> Parallel Seq Scan on p1_c3_c1
828+ -> Parallel Seq Scan on p1_c3_c2
828829 (25 rows)
829830
830831 -- Parallel index scan
@@ -879,33 +880,33 @@ not used hint:
879880 duplication hint:
880881 error hint:
881882
882- QUERY PLAN
883------------------------------------------------------------------------
884- Gather
885- Workers Planned: 8
886- -> Parallel Hash Join
887- Hash Cond: (p1.id = p2.id)
888- -> Parallel Append
889- -> Parallel Index Scan using p1_pkey on p1
890- -> Parallel Index Scan using p1_c1_pkey on p1_c1
891- -> Parallel Index Scan using p1_c2_pkey on p1_c2
892- -> Parallel Index Scan using p1_c3_pkey on p1_c3
893- -> Parallel Index Scan using p1_c4_pkey on p1_c4
894- -> Parallel Index Scan using p1_c1_c1_pkey on p1_c1_c1
895- -> Parallel Index Scan using p1_c1_c2_pkey on p1_c1_c2
896- -> Parallel Index Scan using p1_c3_c1_pkey on p1_c3_c1
897- -> Parallel Index Scan using p1_c3_c2_pkey on p1_c3_c2
898- -> Parallel Hash
883+ QUERY PLAN
884+-----------------------------------------------------------------------------
885+ Hash Join
886+ Hash Cond: (p2.id = p1.id)
887+ -> Append
888+ -> Seq Scan on p2
889+ -> Seq Scan on p2_c1
890+ -> Seq Scan on p2_c2
891+ -> Seq Scan on p2_c3
892+ -> Seq Scan on p2_c4
893+ -> Seq Scan on p2_c1_c1
894+ -> Seq Scan on p2_c1_c2
895+ -> Seq Scan on p2_c3_c1
896+ -> Seq Scan on p2_c3_c2
897+ -> Hash
898+ -> Gather
899+ Workers Planned: 8
899900 -> Parallel Append
900- -> Seq Scan on p2
901- -> Seq Scan on p2_c1
902- -> Seq Scan on p2_c3
903- -> Parallel Seq Scan on p2_c2
904- -> Parallel Seq Scan on p2_c4
905- -> Parallel Seq Scan on p2_c1_c1
906- -> Parallel Seq Scan on p2_c1_c2
907- -> Parallel Seq Scan on p2_c3_c1
908- -> Parallel Seq Scan on p2_c3_c2
901+ -> Parallel Index Scan using p1_pkey on p1
902+ -> Parallel Index Scan using p1_c1_pkey on p1_c1
903+ -> Parallel Index Scan using p1_c2_pkey on p1_c2
904+ -> Parallel Index Scan using p1_c3_pkey on p1_c3
905+ -> Parallel Index Scan using p1_c4_pkey on p1_c4
906+ -> Parallel Index Scan using p1_c1_c1_pkey on p1_c1_c1
907+ -> Parallel Index Scan using p1_c1_c2_pkey on p1_c1_c2
908+ -> Parallel Index Scan using p1_c3_c1_pkey on p1_c3_c1
909+ -> Parallel Index Scan using p1_c3_c2_pkey on p1_c3_c2
909910 (25 rows)
910911
911912 -- This hint doesn't turn on parallel, so the Parallel hint is ignored
--- a/sql/ut-W.sql
+++ b/sql/ut-W.sql
@@ -89,6 +89,7 @@ SET parallel_setup_cost to 0;
8989 SET parallel_tuple_cost to 0;
9090 SET min_parallel_table_scan_size to 0;
9191 SET min_parallel_index_scan_size to 0;
92+SET max_parallel_workers_per_gather to 0;
9293 SET enable_parallel_append to false;
9394 /*+Parallel(p1 8)*/
9495 EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
Show on old repository browser