From: Andy Fan <zhihuifan1213@163.com>
To: James Coleman <jtc331@gmail.com>
Cc: Tomas Vondra <tomas.vondra@enterprisedb.com>,  vignesh C
 <vignesh21@gmail.com>,  Andres Freund <andres@anarazel.de>,  Robert Haas
 <robertmhaas@gmail.com>,  pgsql-hackers <pgsql-hackers@postgresql.org>,
  Amit Kapila <amit.kapila16@gmail.com>,  Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: Parallelize correlated subqueries that execute within each worker
In-Reply-To: 
 <CAAaqYe8=wzXDB5fpO8geK+DiTdz9HaDBRy-2JOiAPC-8vcnXDA@mail.gmail.com>
	(James Coleman's message of "Wed, 18 Jan 2023 21:34:21 -0500")
References: 
 <CAAaqYe_x1u3V4uPiv=dJ=k2EJ7txhdq6yexJDkYZ1x1pu0QwcQ@mail.gmail.com>
	<CA+TgmobtK2OnhNaKbq8Q+k217mCUdpLFeMXTjxr6QZMG3KE5Gw@mail.gmail.com>
	<CAAaqYe-FYVq=CSZu8VtKi0fx8Zdi6fzyzEP2B9iK2cUgY8xGjA@mail.gmail.com>
	<CA+TgmoaAPfPcQx0uYLusU4A+Vm5Cr2F8irxo=xf8BQ-97YyZ7A@mail.gmail.com>
	<CAAaqYe8HLwTGJC2jPZ8noiv2SRSSvS3zSjQQMmeUqNiY4Uw_dw@mail.gmail.com>
	<CA+TgmoYXm2NCLt1nikWfYj1_r3=fsoNCHCtDVdN7X1uX_xuXgw@mail.gmail.com>
	<CAAaqYe8m0DHUWk7gLKb_C4abTD4nMkU26ErE=ahow4zNMZbzPQ@mail.gmail.com>
	<20220322004805.vwmgjp62tq3mj2oq@alap3.anarazel.de>
	<CAAaqYe9hJ=GzcaVSH82e8o6edr3bkYkauhk_yLiGVbF=8xss1w@mail.gmail.com>
	<CALDaNm0vp-xLtVEwFHBjTP38ufNN1e-b6J17nDsUhNNiqZ8iHw@mail.gmail.com>
	<1818a285-59b9-e028-c5a8-539f715b9371@enterprisedb.com>
	<CAAaqYe8=wzXDB5fpO8geK+DiTdz9HaDBRy-2JOiAPC-8vcnXDA@mail.gmail.com>
Date: Thu, 03 Jul 2025 14:04:22 +0000
Message-ID: <87frfd9xqx.fsf@163.com>
MIME-Version: 1.0
Content-Type: text/plain
Archived-At: <https://www.postgresql.org/message-id/87frfd9xqx.fsf%40163.com>
Precedence: bulk


Hi James:

Very nice to find this topic, I am recently working on this topic [1] as 
well without finding this topic has been discussed before. I just go
through this thread and find it goes with a different direction with mine.
would you mind to check my soluation to see is there any case I can't
cover? I suggested this because my soluation should be much easier than
yours. But I'm not suprised to know I'm miss some obvious keypoint. 

>
>> One of the queries in in incremental_sort changed plans a little bit:
>>
>> explain (costs off) select distinct
>>   unique1,
>>   (select t.unique1 from tenk1 where tenk1.unique1 = t.unique1)
>> from tenk1 t, generate_series(1, 1000);
>>
>> switched from
>>
>>  Unique  (cost=18582710.41..18747375.21 rows=10000 width=8)
>>    ->  Gather Merge  (cost=18582710.41..18697375.21 rows=10000000 ...)
>>          Workers Planned: 2
>>          ->  Sort  (cost=18582710.39..18593127.06 rows=4166667 ...)
>>                Sort Key: t.unique1, ((SubPlan 1))
>>              ...
>>
>> to
>>
>>  Unique  (cost=18582710.41..18614268.91 rows=10000 ...)
>>    ->  Gather Merge  (cost=18582710.41..18614168.91 rows=20000 ...)
>>          Workers Planned: 2
>>          ->  Unique  (cost=18582710.39..18613960.39 rows=10000 ...)
>>                ->  Sort  (cost=18582710.39..18593127.06 ...)
>>                      Sort Key: t.unique1, ((SubPlan 1))
>>                    ...
>>
>> which probably makes sense, as the cost estimate decreases a bit.
>
> Off the cuff that seems fine. I'll read it over again when I send the
> updated series.

I had a detailed explaination for this plan change in [1] and I think
this could be amazing gain no matter which way we go finally. 

[1] https://www.postgresql.org/message-id/871pqzm5wj.fsf@163.com 

-- 
Best Regards
Andy Fan