Re: PG 10 release notes

On Tue, Apr 25, 2017 at 7:19 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> On Tue, Apr 25, 2017 at 09:00:45AM +0530, Amit Kapila wrote:
>> On Tue, Apr 25, 2017 at 8:35 AM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>> > On Tue, Apr 25, 2017 at 08:30:50AM +0530, Amit Kapila wrote:
>> >> On Tue, Apr 25, 2017 at 7:01 AM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>> >> > I have committed the first draft of the Postgres 10 release notes. They
>> >> > are current as of two days ago, and I will keep them current. Please
>> >> > give me any feedback you have.
>> >> >
>> >>
>> >> Some of the items which I feel could be added:
>> >>
>> >> 5e6d8d2bbbcace304450b309e79366c0da4063e4
>> >> Allow parallel workers to execute subplans.
>> >
>> > Uh, can you show me the commit on that and give some text ideas?
>> >
>>
>> I have already mentioned the commit id (5e6d8d2b). Text can be "Allow
>> queries containing subplans to execute in parallel". We should also
>> mention in some way that this applies only when the query contains
>> uncorrelated subplan.
>
> Sorry but I don't know what that means, and if I don't know, others
> might not either.
>

Let me try to explain by example:

Without this feature, the queries that refer subplans will be executed
serially like below:

regression=# explain (costs off) select count(*) from tenk1 where
(two, four) not in (select hundred, thousand from tenk2 where thousand
> 100);
QUERY PLAN
------------------------------------------
Aggregate
-> Seq Scan on tenk1
Filter: (NOT (hashed SubPlan 1))
SubPlan 1
-> Seq Scan on tenk2
Filter: (thousand > 100)
(6 rows)

After this feature, the queries that refer subplans can use parallel
plans like below:

regression=# explain (costs off) select count(*) from tenk1 where
(two, four) not in (select hundred, thousand from tenk2 where
thousand > 100);
QUERY PLAN
------------------------------------------------------
Finalize Aggregate
-> Gather
Workers Planned: 2
-> Partial Aggregate
-> Parallel Seq Scan on tenk1
Filter: (NOT (hashed SubPlan 1))
SubPlan 1
-> Seq Scan on tenk2
Filter: (thousand > 100)
(9 rows)

Now, it won't use parallelism if there is correlated subplan like below:

Seq Scan on t1
Filter: (SubPlan 1)
SubPlan 1
-> Result
One-Time Filter: (t1.k = 0)
-> Parallel Seq Scan on t2

In this plan difference is that SubPlan refers to outer relation t1.

Do the above examples helps in understanding the feature?

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com