Correlated IN/Any Subquery Transformation
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t42094psql -h localhost -U postgresBuilt from patchset v1 (message #1), July 28, 2026 at 02:55 AM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t42094_1 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t42094_1 && git checkout t42094_1Patchset v1 (message #1) is on t42094_1
Hi PGHackers:
Currently, correlated IN/Any subquery always gets planned as a SubPlan which leads to poor performance:
postgres=# explain (costs off) select count(*) from s where s.n in (select l.n from l where l.u != s.u);
QUERY PLAN
------------------------------------
Aggregate
-> Seq Scan on s
Filter: (SubPlan 1)
SubPlan 1
-> Seq Scan on l
Filter: (u <> s.u)
postgres=# select count() from s where s.n in (select l.n from l where l.u != s.u);
Time: 3419.466 ms (00:03.419)
However, you can rewrite the query using exists which will be executed using join. In this example the join plan is more than 3 orders of magnitudes faster than the SubPlan:
postgres=# explain (costs off) select count(*) from s where exists (select 1 from l where l.n = s.n and l.u != s.u);
QUERY PLAN
---------------------------------------
Aggregate
-> Merge Semi Join
Merge Cond: (s.n = l.n)
Join Filter: (l.u <> s.u)
-> Index Scan using s_n on s
-> Index Scan using l_n on l
postgres=# select count() from s where exists (select 1 from l where l.n = s.n and l.u != s.u);
Time: 1.188 ms
Table s has 10 rows, table l has 1, 000, 000 rows.
This patch enables correlated IN/Any subquery to be transformed to join, the transformation is allowed only when the correlated Var is in the where clause of the subquery. It covers the most common correlated cases and follows the same criteria that is followed by the correlated Exists transformation code.
Here is the new query plan for the same correlated IN query:
postgres=# explain (costs off) select count(*) from s where s.n in (select l.n from l where l.u != s.u);
QUERY PLAN
Aggregate
-> Merge Semi Join
Merge Cond: (s.n = l.n)
Join Filter: (l.u <> s.u)
-> Index Scan using s_n on s
-> Index Scan using l_n on l
postgres=# select count(*) from s where s.n in (select l.n from l where l.u != s.u);
Time: 1.693 ms
________________________________
Also the patch introduces a new GUC enable_correlated_any_transform (on by default) to guard the optimization. Test cases are included in the patch. Comments are welcome!
-----------
Zheng Li
AWS, Amazon Aurora PostgreSQL
"Li, Zheng" <zhelli@amazon.com> writes:
This patch enables correlated IN/Any subquery to be transformed to join, the transformation is allowed only when the correlated Var is in the where clause of the subquery. It covers the most common correlated cases and follows the same criteria that is followed by the correlated Exists transformation code.
It's too late to include this in v13, but please add the patch to the
next commitfest so that we remember to consider it for v14.
https://commitfest.postgresql.org
regards, tom lane