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