Horton

Re: Parallelize correlated subqueries that execute within each worker

Andy Fan <zhihuifan1213@163.com>

View thread
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>
Date: 2025-07-03T14:04:22Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Windows support in pg_import_system_collations

  2. Remove pessimistic cost penalization from Incremental Sort

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