Re: Allowing parallel-safe initplans

[Frédéric Yhuel]

Le 12/04/2023 à 20:06, Robert Haas a écrit :
There's only one existing test case that visibly changes plan with
these changes.  The new plan is clearly saner-looking than before,
and testing with some data loaded into the table confirms that it
is faster.  I'm not sure if it's worth devising more test cases.
It seems like it would be nice to see one or two additional scenarios
where these changes bring a benefit, with different kinds of plan
shapes.

Hi,

Currently working on illustrating some points in the v17 release notes, 
I'm trying to come up with a sexier scenario than the test case, but it 
seems that with a non-trivial InitPlan (2nd explain below), we still 
have a non-parallel Append node at the top:

SET parallel_setup_cost = 0;
SET parallel_tuple_cost = 0;
SET min_parallel_table_scan_size = 10;

CREATE TABLE foo (a int) PARTITION by LIST(a);

CREATE TABLE foo_0 PARTITION OF foo FOR VALUES IN (0);

CREATE TABLE foo_1 PARTITION OF foo FOR VALUES IN (1);

EXPLAIN (COSTS OFF)
        SELECT * FROM foo WHERE a = (SELECT 2)
        UNION ALL
        SELECT * FROM foo WHERE a = 0;

                     QUERY PLAN
-----------------------------------------------------
 Gather
   Workers Planned: 2
   ->  Parallel Append
         ->  Parallel Append
               InitPlan 1
                 ->  Result
               ->  Parallel Seq Scan on foo_0 foo_1
                     Filter: (a = (InitPlan 1).col1)
               ->  Parallel Seq Scan on foo_1 foo_2
                     Filter: (a = (InitPlan 1).col1)
         ->  Parallel Seq Scan on foo_0 foo_3
               Filter: (a = 0)


EXPLAIN (COSTS OFF)
        SELECT * FROM foo WHERE a = (SELECT max(a) FROM foo)
        UNION ALL
        SELECT * FROM foo WHERE a = 0;

                               QUERY PLAN
------------------------------------------------------------------------
 Append
   ->  Gather
         Workers Planned: 2
         InitPlan 1
           ->  Finalize Aggregate
                 ->  Gather
                       Workers Planned: 2
                       ->  Partial Aggregate
                             ->  Parallel Append
                                   ->  Parallel Seq Scan on foo_0 foo_5
                                   ->  Parallel Seq Scan on foo_1 foo_6
         ->  Parallel Append
               ->  Parallel Seq Scan on foo_0 foo_1
                     Filter: (a = (InitPlan 1).col1)
               ->  Parallel Seq Scan on foo_1 foo_2
                     Filter: (a = (InitPlan 1).col1)
   ->  Gather
         Workers Planned: 1
         ->  Parallel Seq Scan on foo_0 foo_3
               Filter: (a = 0)

Did I miss something?

Best regards,
Frédéric