pg_hint_plan icon indicating copy to clipboard operation
pg_hint_plan copied to clipboard

parallel hint invalid when SQL has only one table

Open cstarc opened this issue 2 years ago • 0 comments

pg13 pg_hint_plan 13.7

case1 parallel not work when only one table:

chuhx@postgres=# create table test(id int,val int);
CREATE TABLE
chuhx@postgres=# explain select * from test;
                       QUERY PLAN                       
--------------------------------------------------------
 Seq Scan on test  (cost=0.00..32.60 rows=2260 width=8)
(1 row)

chuhx@postgres=# explain select /*+parallel(test 5 hard)*/* from test;
                           QUERY PLAN                           
----------------------------------------------------------------
 Seq Scan on test @"lt#0"  (cost=0.00..32.60 rows=2260 width=8)
(1 row)

case 2 parallel worked :

explain select /*+parallel(test 5 hard)*/* from test,test x;
                                    QUERY PLAN                                     
-----------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..51108.60 rows=5107600 width=16)
   ->  Seq Scan on test x @"lt#0"  (cost=0.00..32.60 rows=2260 width=8)
   ->  Gather  (cost=0.00..0.00 rows=2260 width=8)
         Workers Planned: 5
         ->  Parallel Seq Scan on test @"lt#0"  (cost=0.00..0.00 rows=729 width=8)
(5 rows)

after debug, i find it is because in pg_hint_plan_set_rel_pathlist func ,following code is not executed because of bms_membership(root->all_baserels) == BMS_SINGLETON:

/* Generate gather paths */
if (rel->reloptkind == RELOPT_BASEREL &&
	bms_membership(root->all_baserels) != BMS_SINGLETON)
	generate_gather_paths(root, rel, false);

Generate gather paths is on apply_scanjoin_target_to_paths , here , the guc which is setted by parallel hint had alreadly been resetted,so parallel hint is not used when Generating gather paths.

#1  0x000000000086ce7d in create_gather_path (root=0x1b7bda0, rel=0x1b57ad0, 
    subpath=0x1b66758, target=0x1b668f8, required_outer=0x0, rows=0x0)
    at pathnode.c:1945
#2  0x00000000007fdea0 in generate_gather_paths (root=0x1b7bda0, rel=0x1b57ad0, 
    override_rows=false) at allpaths.c:2711
#3  0x00000000007fe17d in generate_useful_gather_paths (root=0x1b7bda0, 
    rel=0x1b57ad0, override_rows=false) at allpaths.c:2843
#4  0x0000000000843b8e in apply_scanjoin_target_to_paths (root=0x1b7bda0, 
    rel=0x1b57ad0, scanjoin_targets=0x1b669c8, scanjoin_targets_contain_srfs=0x0, 
    scanjoin_target_parallel_safe=true, tlist_same_exprs=true) at planner.c:7676
#5  0x0000000000839611 in grouping_planner (root=0x1b7bda0, 

cstarc avatar Jun 22 '22 08:06 cstarc