Re: Allowing parallel-safe initplans

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