postgres.git / summary / log / commit / refs
commit 3ab3f33281f62bdcef1dd987a89fcfe7f082b6d8
Author: Fujii Masao <fujii@postgresql.org>
Date: Sat Aug 15 09:12:14 2026 +0000
Rename EXISTS-to-ANY converted subplan to exists_to_any
Simple EXISTS subplans can be converted to hashed ANY subplans as
an alternative implementation. Previously, both the original EXISTS
subplan and the converted ANY subplan used names with the
exists_ prefix, making EXPLAIN output harder to read and
pg_plan_advice targets less clear.
Use the exists_to_any_ prefix for converted ANY subplans so that
their names distinguish them from the original EXISTS alternative.
This only changes the names shown by EXPLAIN and used by plan advice;
it does not affect planner behavior.
Author: Yugo Nagata <nagata@sraoss.co.jp>
Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>
Reviewed-by: solai v <solai.cdac@gmail.com>
Reviewed-by: Fujii Masao <masao.fujii@gmail.com>
Discussion: https://postgr.es/m/20260605165641.3950f99ace0aad8f807abe96@sraoss.co.jp
Backpatch-through: 19
contrib/pg_plan_advice/expected/alternatives.out | 30 ++++++++++++------------
contrib/pg_plan_advice/sql/alternatives.sql | 2 +-
src/backend/optimizer/plan/subselect.c | 8 +++++--
src/test/regress/expected/partition_prune.out | 10 ++++----
src/test/regress/expected/subselect.out | 16 ++++++-------
5 files changed, 35 insertions(+), 31 deletions(-)
diff --git a/contrib/pg_plan_advice/expected/alternatives.out b/contrib/pg_plan_advice/expected/alternatives.out
index a6fb296d4b4..94bfcf3d0a2 100644
--- a/contrib/pg_plan_advice/expected/alternatives.out
+++ b/contrib/pg_plan_advice/expected/alternatives.out
@@ -12,15 +12,15 @@ VACUUM ANALYZE alt_t2;
EXPLAIN (COSTS OFF, PLAN_ADVICE)
SELECT * FROM alt_t1
WHERE EXISTS (SELECT 1 FROM alt_t2 WHERE alt_t2.a = alt_t1.a) OR alt_t1.a < 0;
- QUERY PLAN
--------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------
Seq Scan on alt_t1
- Filter: ((ANY (a = (hashed SubPlan exists_2).col1)) OR (a < 0))
- SubPlan exists_2
+ Filter: ((ANY (a = (hashed SubPlan exists_to_any_1).col1)) OR (a < 0))
+ SubPlan exists_to_any_1
-> Seq Scan on alt_t2
Generated Plan Advice:
- SEQ_SCAN(alt_t1 alt_t2@exists_2)
- NO_GATHER(alt_t1 alt_t2@exists_2)
+ SEQ_SCAN(alt_t1 alt_t2@exists_to_any_1)
+ NO_GATHER(alt_t1 alt_t2@exists_to_any_1)
DO_NOT_SCAN(alt_t2@exists_1)
(8 rows)
@@ -31,21 +31,21 @@ SET LOCAL pg_plan_advice.advice = 'DO_NOT_SCAN(alt_t2@exists_1)';
EXPLAIN (COSTS OFF, PLAN_ADVICE)
SELECT * FROM alt_t1
WHERE EXISTS (SELECT 1 FROM alt_t2 WHERE alt_t2.a = alt_t1.a) OR alt_t1.a < 0;
- QUERY PLAN
--------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------
Seq Scan on alt_t1
- Filter: ((ANY (a = (hashed SubPlan exists_2).col1)) OR (a < 0))
- SubPlan exists_2
+ Filter: ((ANY (a = (hashed SubPlan exists_to_any_1).col1)) OR (a < 0))
+ SubPlan exists_to_any_1
-> Seq Scan on alt_t2
Supplied Plan Advice:
DO_NOT_SCAN(alt_t2@exists_1) /* matched */
Generated Plan Advice:
- SEQ_SCAN(alt_t1 alt_t2@exists_2)
- NO_GATHER(alt_t1 alt_t2@exists_2)
+ SEQ_SCAN(alt_t1 alt_t2@exists_to_any_1)
+ NO_GATHER(alt_t1 alt_t2@exists_to_any_1)
DO_NOT_SCAN(alt_t2@exists_1)
(10 rows)
-SET LOCAL pg_plan_advice.advice = 'DO_NOT_SCAN(alt_t2@exists_2)';
+SET LOCAL pg_plan_advice.advice = 'DO_NOT_SCAN(alt_t2@exists_to_any_1)';
EXPLAIN (COSTS OFF, PLAN_ADVICE)
SELECT * FROM alt_t1
WHERE EXISTS (SELECT 1 FROM alt_t2 WHERE alt_t2.a = alt_t1.a) OR alt_t1.a < 0;
@@ -57,12 +57,12 @@ WHERE EXISTS (SELECT 1 FROM alt_t2 WHERE alt_t2.a = alt_t1.a) OR alt_t1.a < 0;
-> Index Only Scan using alt_t2_a_idx on alt_t2
Index Cond: (a = alt_t1.a)
Supplied Plan Advice:
- DO_NOT_SCAN(alt_t2@exists_2) /* matched */
+ DO_NOT_SCAN(alt_t2@exists_to_any_1) /* matched */
Generated Plan Advice:
SEQ_SCAN(alt_t1)
INDEX_ONLY_SCAN(alt_t2@exists_1 public.alt_t2_a_idx)
NO_GATHER(alt_t1 alt_t2@exists_1)
- DO_NOT_SCAN(alt_t2@exists_2)
+ DO_NOT_SCAN(alt_t2@exists_to_any_1)
(12 rows)
COMMIT;
diff --git a/contrib/pg_plan_advice/sql/alternatives.sql b/contrib/pg_plan_advice/sql/alternatives.sql
index 16299edd196..f45f7083882 100644
--- a/contrib/pg_plan_advice/sql/alternatives.sql
+++ b/contrib/pg_plan_advice/sql/alternatives.sql
@@ -22,7 +22,7 @@ SET LOCAL pg_plan_advice.advice = 'DO_NOT_SCAN(alt_t2@exists_1)';
EXPLAIN (COSTS OFF, PLAN_ADVICE)
SELECT * FROM alt_t1
WHERE EXISTS (SELECT 1 FROM alt_t2 WHERE alt_t2.a = alt_t1.a) OR alt_t1.a < 0;
-SET LOCAL pg_plan_advice.advice = 'DO_NOT_SCAN(alt_t2@exists_2)';
+SET LOCAL pg_plan_advice.advice = 'DO_NOT_SCAN(alt_t2@exists_to_any_1)';
EXPLAIN (COSTS OFF, PLAN_ADVICE)
SELECT * FROM alt_t1
WHERE EXISTS (SELECT 1 FROM alt_t2 WHERE alt_t2.a = alt_t1.a) OR alt_t1.a < 0;
diff --git a/src/backend/optimizer/plan/subselect.c b/src/backend/optimizer/plan/subselect.c
index 6aa8971c95d..26384fd5fdb 100644
--- a/src/backend/optimizer/plan/subselect.c
+++ b/src/backend/optimizer/plan/subselect.c
@@ -271,8 +271,12 @@ make_subplan(PlannerInfo *root, Query *orig_subquery,
{
char *plan_name;
- /* Generate Paths for the ANY subquery; we'll need all rows */
- plan_name = choose_plan_name(root->glob, sublinkstr, true);
+ /*
+ * Generate Paths for the ANY subquery; we'll need all rows.
+ * Use a distinct prefix for this user-visible name, since this is
+ * an ANY implementation of the original EXISTS subplan.
+ */
+ plan_name = choose_plan_name(root->glob, "exists_to_any", true);
subroot = subquery_planner(root->glob, subquery, plan_name,
root, subroot, false, 0.0, NULL);
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index 0d21a2d027c..aa821646011 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -1967,15 +1967,15 @@ where asptab.id > ss.b::int;
-> Seq Scan on int4_tbl touter
-> Append
-> Index Only Scan using asptab0_pkey on asptab0 asptab_1
- Index Cond: (id > (EXISTS(SubPlan exists_3))::integer)
- SubPlan exists_4
+ Index Cond: (id > (EXISTS(SubPlan exists_2))::integer)
+ SubPlan exists_to_any_2
-> Seq Scan on int4_tbl tinner_2
-> Index Only Scan using asptab1_pkey on asptab1 asptab_2
- Index Cond: (id > (EXISTS(SubPlan exists_3))::integer)
- SubPlan exists_3
+ Index Cond: (id > (EXISTS(SubPlan exists_2))::integer)
+ SubPlan exists_2
-> Seq Scan on int4_tbl tinner_1
Filter: (f1 = touter.f1)
- SubPlan exists_2
+ SubPlan exists_to_any_1
-> Seq Scan on int4_tbl tinner
(14 rows)
diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out
index 427b3765ae3..6a54f2bbb88 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -1253,12 +1253,12 @@ rollback; -- to get rid of the bogus operator
explain (costs off)
select count(*) from tenk1 t
where (exists(select 1 from tenk1 k where k.unique1 = t.unique2) or ten < 0);
- QUERY PLAN
----------------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------------
Aggregate
-> Seq Scan on tenk1 t
- Filter: ((ANY (unique2 = (hashed SubPlan exists_2).col1)) OR (ten < 0))
- SubPlan exists_2
+ Filter: ((ANY (unique2 = (hashed SubPlan exists_to_any_1).col1)) OR (ten < 0))
+ SubPlan exists_to_any_1
-> Index Only Scan using tenk1_unique1 on tenk1 k
(5 rows)
@@ -1303,8 +1303,8 @@ analyze exists_tbl;
explain (costs off)
select * from exists_tbl t1
where (exists(select 1 from exists_tbl t2 where t1.c1 = t2.c2) or c3 < 0);
- QUERY PLAN
----------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------
Append
-> Seq Scan on exists_tbl_null t1_1
Filter: (EXISTS(SubPlan exists_1) OR (c3 < 0))
@@ -1315,8 +1315,8 @@ select * from exists_tbl t1
-> Seq Scan on exists_tbl_def t2_2
Filter: (t1_1.c1 = c2)
-> Seq Scan on exists_tbl_def t1_2
- Filter: ((ANY (c1 = (hashed SubPlan exists_2).col1)) OR (c3 < 0))
- SubPlan exists_2
+ Filter: ((ANY (c1 = (hashed SubPlan exists_to_any_1).col1)) OR (c3 < 0))
+ SubPlan exists_to_any_1
-> Append
-> Seq Scan on exists_tbl_null t2_4
-> Seq Scan on exists_tbl_def t2_5
[parent: 16742849a3de]