Re: Parallelize correlated subqueries that execute within each worker

Tomas Vondra <tomas.vondra@enterprisedb.com>

From: Tomas Vondra <tomas.vondra@enterprisedb.com>

To: vignesh C <vignesh21@gmail.com>, James Coleman <jtc331@gmail.com>

Cc: 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: 2023-01-18T19:09:21Z

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 →

Windows support in pg_import_system_collations
- bf03cfd16217 16.0 cited
Remove pessimistic cost penalization from Incremental Sort
- 4a29eabd1d91 16.0 cited

Attachments

0001-Add-tests-before-change-v6.patch (text/x-patch) patch v6-0001
0002-Parallelize-correlated-subqueries-v6.patch (text/x-patch) patch v6-0002
0003-review-comments-v6.patch (text/x-patch) patch v6-0003
0004-Possible-additional-checks-v6.patch (text/x-patch) patch v6-0004
0005-review-comments-v6.patch (text/x-patch) patch v6-0005

Hi,

This patch hasn't been updated since September, and it got broken by
4a29eabd1d91c5484426bc5836e0a7143b064f5a which the incremental sort
stuff a little bit. But the breakage was rather limited, so I took a
stab at fixing it - attached is the result, hopefully correct.

I also added a couple minor comments about stuff I noticed while
rebasing and skimming the patch, I kept those in separate commits.
There's also a couple pre-existing TODOs.

James, what's your plan with this patch. Do you intend to work on it for
PG16, or are there some issues I missed in the thread?


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.

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company