Subject: Re: Improving EXPLAIN's display of SubPlan nodes

From: Tom Lane <[email-masked]>
Date: Fri, 16 Feb 2024 14:38:50 -0500

I wrote:
> So now I'm thinking that we do have enough detail in the present
> proposal, and we just need to think about whether there's some
> nicer way to present it than the particular spelling I used here.

Here's a rebase over 9f1337639 --- no code changes, but this affects
some of the new or changed expected outputs from that commit.

			regards, tom lane

From f84343864e74501df627f986e326f766072398cd Mon Sep 17 00:00:00 2001
From: Tom Lane <[email-masked]>
Date: Fri, 16 Feb 2024 14:32:23 -0500
Subject: [PATCH v2] Improve EXPLAIN's display of SubPlan nodes.

Represent the SubLinkType as best we can, and show the testexpr
where relevant.  To aid in interpreting testexprs, add the output
parameter IDs to the subplan name for subplans as well as initplans.

Discussion: https://postgr.es/m/[email-masked]
---
 .../postgres_fdw/expected/postgres_fdw.out    |  16 +-
 src/backend/optimizer/plan/subselect.c        |  30 ++--
 src/backend/utils/adt/ruleutils.c             |  45 ++++-
 src/test/regress/expected/aggregates.out      |   2 +-
 src/test/regress/expected/insert_conflict.out |   2 +-
 src/test/regress/expected/join.out            |  24 +--
 src/test/regress/expected/memoize.out         |   2 +-
 src/test/regress/expected/rowsecurity.out     |  56 +++---
 src/test/regress/expected/select_parallel.out |  22 +--
 src/test/regress/expected/subselect.out       | 159 ++++++++++++------
 src/test/regress/expected/updatable_views.out |  24 +--
 src/test/regress/sql/subselect.sql            |  14 ++
 12 files changed, 255 insertions(+), 141 deletions(-)

diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index c355e8f3f7..bf067738e3 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -3264,14 +3264,14 @@ select sum(c1) filter (where (c1 / c1) * random() <= 1) from ft1 group by c2 ord
 
 explain (verbose, costs off)
 select sum(c2) filter (where c2 in (select c2 from ft1 where c2 < 5)) from ft1;
-                            QUERY PLAN                             
--------------------------------------------------------------------
+                                         QUERY PLAN                                          
+---------------------------------------------------------------------------------------------
  Aggregate
-   Output: sum(ft1.c2) FILTER (WHERE (hashed SubPlan 1))
+   Output: sum(ft1.c2) FILTER (WHERE (ANY (ft1.c2 = $0) FROM HASHED SubPlan 1 (returns $0)))
    ->  Foreign Scan on public.ft1
          Output: ft1.c2
          Remote SQL: SELECT c2 FROM "S 1"."T 1"
-   SubPlan 1
+   SubPlan 1 (returns $0)
      ->  Foreign Scan on public.ft1 ft1_1
            Output: ft1_1.c2
            Remote SQL: SELECT c2 FROM "S 1"."T 1" WHERE ((c2 < 5))
@@ -11895,12 +11895,12 @@ CREATE FOREIGN TABLE foreign_tbl2 () INHERITS (foreign_tbl)
   SERVER loopback OPTIONS (table_name 'base_tbl');
 EXPLAIN (VERBOSE, COSTS OFF)
 SELECT a FROM base_tbl WHERE (a, random() > 0) IN (SELECT a, random() > 0 FROM foreign_tbl);
-                                 QUERY PLAN                                  
------------------------------------------------------------------------------
+                                                    QUERY PLAN                                                    
+------------------------------------------------------------------------------------------------------------------
  Seq Scan on public.base_tbl
    Output: base_tbl.a
-   Filter: (SubPlan 1)
-   SubPlan 1
+   Filter: (ANY ((base_tbl.a = $1) AND ((random() > '0'::double precision) = $2)) FROM SubPlan 1 (returns $1,$2))
+   SubPlan 1 (returns $1,$2)
      ->  Result
            Output: base_tbl.a, (random() > '0'::double precision)
            ->  Append
diff --git a/src/backend/optimizer/plan/subselect.c b/src/backend/optimizer/plan/subselect.c
index 47e14723d2..d5919a9164 100644
--- a/src/backend/optimizer/plan/subselect.c
+++ b/src/backend/optimizer/plan/subselect.c
@@ -326,6 +326,7 @@ build_subplan(PlannerInfo *root, Plan *plan, PlannerInfo *subroot,
 	Node	   *result;
 	SubPlan    *splan;
 	bool		isInitPlan;
+	StringInfoData splanname;
 	ListCell   *lc;
 
 	/*
@@ -560,22 +561,31 @@ build_subplan(PlannerInfo *root, Plan *plan, PlannerInfo *subroot,
 												   splan->plan_id);
 
 	/* Label the subplan for EXPLAIN purposes */
-	splan->plan_name = palloc(32 + 12 * list_length(splan->setParam));
-	sprintf(splan->plan_name, "%s %d",
-			isInitPlan ? "InitPlan" : "SubPlan",
-			splan->plan_id);
+	initStringInfo(&splanname);
+	appendStringInfo(&splanname, "%s %d",
+					 isInitPlan ? "InitPlan" : "SubPlan",
+					 splan->plan_id);
 	if (splan->setParam)
 	{
-		char	   *ptr = splan->plan_name + strlen(splan->plan_name);
-
-		ptr += sprintf(ptr, " (returns ");
+		appendStringInfoString(&splanname, " (returns ");
 		foreach(lc, splan->setParam)
 		{
-			ptr += sprintf(ptr, "$%d%s",
-						   lfirst_int(lc),
-						   lnext(splan->setParam, lc) ? "," : ")");
+			appendStringInfo(&splanname, "$%d%s",
+							 lfirst_int(lc),
+							 lnext(splan->setParam, lc) ? "," : ")");
+		}
+	}
+	else if (splan->paramIds)
+	{
+		appendStringInfoString(&splanname, " (returns ");
+		foreach(lc, splan->paramIds)
+		{
+			appendStringInfo(&splanname, "$%d%s",
+							 lfirst_int(lc),
+							 lnext(splan->paramIds, lc) ? "," : ")");
 		}
 	}
+	splan->plan_name = splanname.data;
 
 	/* Lastly, fill in the cost estimates for use later */
 	cost_subplan(root, splan, plan);
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index a928a8c55d..255498a58d 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -8869,12 +8869,51 @@ get_rule_expr(Node *node, deparse_context *context,
 				 * We cannot see an already-planned subplan in rule deparsing,
 				 * only while EXPLAINing a query plan.  We don't try to
 				 * reconstruct the original SQL, just reference the subplan
-				 * that appears elsewhere in EXPLAIN's result.
+				 * that appears elsewhere in EXPLAIN's result.  It does seem
+				 * useful to show the subLinkType and testexpr, however, and
+				 * we also note whether the subplan will be hashed.
 				 */
+				switch (subplan->subLinkType)
+				{
+					case EXISTS_SUBLINK:
+						appendStringInfoString(buf, "EXISTS(");
+						Assert(subplan->testexpr == NULL);
+						break;
+					case ALL_SUBLINK:
+						appendStringInfoString(buf, "(ALL ");
+						get_rule_expr(subplan->testexpr, context, showimplicit);
+						appendStringInfoString(buf, " FROM ");
+						break;
+					case ANY_SUBLINK:
+						appendStringInfoString(buf, "(ANY ");
+						get_rule_expr(subplan->testexpr, context, showimplicit);
+						appendStringInfoString(buf, " FROM ");
+						break;
+					case ROWCOMPARE_SUBLINK:
+						appendStringInfoString(buf, "(ROWCOMPARE ");
+						get_rule_expr(subplan->testexpr, context, showimplicit);
+						appendStringInfoString(buf, " FROM ");
+						break;
+					case EXPR_SUBLINK:
+					case MULTIEXPR_SUBLINK:
+						/* No need to decorate these subplan references */
+						appendStringInfoString(buf, "(");
+						Assert(subplan->testexpr == NULL);
+						break;
+					case ARRAY_SUBLINK:
+						appendStringInfoString(buf, "ARRAY(");
+						Assert(subplan->testexpr == NULL);
+						break;
+					case CTE_SUBLINK:
+						/* This case is unreachable within expressions */
+						appendStringInfoString(buf, "CTE(");
+						Assert(subplan->testexpr == NULL);
+						break;
+				}
 				if (subplan->useHashTable)
-					appendStringInfo(buf, "(hashed %s)", subplan->plan_name);
+					appendStringInfo(buf, "HASHED %s)", subplan->plan_name);
 				else
-					appendStringInfo(buf, "(%s)", subplan->plan_name);
+					appendStringInfo(buf, "%s)", subplan->plan_name);
 			}
 			break;
 
diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out
index f736bab67e..56f6154ffc 100644
--- a/src/test/regress/expected/aggregates.out
+++ b/src/test/regress/expected/aggregates.out
@@ -738,7 +738,7 @@ select array(select sum(x+y) s
                             QUERY PLAN                             
 -------------------------------------------------------------------
  Function Scan on pg_catalog.generate_series x
-   Output: (SubPlan 1)
+   Output: ARRAY(SubPlan 1)
    Function Call: generate_series(1, 3)
    SubPlan 1
      ->  Sort
diff --git a/src/test/regress/expected/insert_conflict.out b/src/test/regress/expected/insert_conflict.out
index 563c5eb52a..701217ddbc 100644
--- a/src/test/regress/expected/insert_conflict.out
+++ b/src/test/regress/expected/insert_conflict.out
@@ -50,7 +50,7 @@ explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on con
  Insert on insertconflicttest
    Conflict Resolution: UPDATE
    Conflict Arbiter Indexes: op_index_key, collation_index_key, both_index_key
-   Conflict Filter: (SubPlan 1)
+   Conflict Filter: EXISTS(SubPlan 1)
    ->  Result
    SubPlan 1
      ->  Index Only Scan using both_index_expr_key on insertconflicttest ii
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 0c2cba8921..6a7a6aa839 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -3028,11 +3028,11 @@ where unique1 in (select unique2 from tenk1 b);
 explain (costs off)
 select a.* from tenk1 a
 where unique1 not in (select unique2 from tenk1 b);
-                       QUERY PLAN                       
---------------------------------------------------------
+                               QUERY PLAN                                
+-------------------------------------------------------------------------
  Seq Scan on tenk1 a
-   Filter: (NOT (hashed SubPlan 1))
-   SubPlan 1
+   Filter: (NOT (ANY (unique1 = $0) FROM HASHED SubPlan 1 (returns $0)))
+   SubPlan 1 (returns $0)
      ->  Index Only Scan using tenk1_unique2 on tenk1 b
 (4 rows)
 
@@ -5278,13 +5278,13 @@ explain (costs off)
 select a.unique1, b.unique2
   from onek a left join onek b on a.unique1 = b.unique2
   where (b.unique2, random() > 0) = any (select q1, random() > 0 from int8_tbl c where c.q1 < b.unique1);
-                        QUERY PLAN                        
-----------------------------------------------------------
+                                                     QUERY PLAN                                                      
+---------------------------------------------------------------------------------------------------------------------
  Hash Join
    Hash Cond: (b.unique2 = a.unique1)
    ->  Seq Scan on onek b
-         Filter: (SubPlan 1)
-         SubPlan 1
+         Filter: (ANY ((unique2 = $1) AND ((random() > '0'::double precision) = $2)) FROM SubPlan 1 (returns $1,$2))
+         SubPlan 1 (returns $1,$2)
            ->  Seq Scan on int8_tbl c
                  Filter: (q1 < b.unique1)
    ->  Hash
@@ -8214,8 +8214,8 @@ lateral (select * from int8_tbl t1,
                                      where q2 = (select greatest(t1.q1,t2.q2))
                                        and (select v.id=0)) offset 0) ss2) ss
          where t1.q1 = ss.q2) ss0;
-                                  QUERY PLAN                                   
--------------------------------------------------------------------------------
+                                                          QUERY PLAN                                                           
+-------------------------------------------------------------------------------------------------------------------------------
  Nested Loop
    Output: "*VALUES*".column1, t1.q1, t1.q2, ss2.q1, ss2.q2
    ->  Seq Scan on public.int8_tbl t1
@@ -8229,8 +8229,8 @@ lateral (select * from int8_tbl t1,
                Filter: (t1.q1 = ss2.q2)
                ->  Seq Scan on public.int8_tbl t2
                      Output: t2.q1, t2.q2
-                     Filter: (SubPlan 3)
-                     SubPlan 3
+                     Filter: (ANY ((t2.q1 = $5) AND ((random() > '0'::double precision) = $6)) FROM SubPlan 3 (returns $5,$6))
+                     SubPlan 3 (returns $5,$6)
                        ->  Result
                              Output: t3.q2, (random() > '0'::double precision)
                              One-Time Filter: $4
diff --git a/src/test/regress/expected/memoize.out b/src/test/regress/expected/memoize.out
index cf6886a288..8fb24063b6 100644
--- a/src/test/regress/expected/memoize.out
+++ b/src/test/regress/expected/memoize.out
@@ -313,7 +313,7 @@ WHERE unique1 < 3
 ----------------------------------------------------------------
  Index Scan using tenk1_unique1 on tenk1 t0
    Index Cond: (unique1 < 3)
-   Filter: (SubPlan 1)
+   Filter: EXISTS(SubPlan 1)
    SubPlan 1
      ->  Nested Loop
            ->  Index Scan using tenk1_hundred on tenk1 t2
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index 6988128aa4..3f3815a9cf 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -1437,11 +1437,11 @@ NOTICE:  f_leak => 03b26944890929ff751653acb2f2af79
 (1 row)
 
 EXPLAIN (COSTS OFF) SELECT * FROM only s1 WHERE f_leak(b);
-                        QUERY PLAN                         
------------------------------------------------------------
+                                 QUERY PLAN                                  
+-----------------------------------------------------------------------------
  Seq Scan on s1
-   Filter: ((hashed SubPlan 1) AND f_leak(b))
-   SubPlan 1
+   Filter: ((ANY (a = $0) FROM HASHED SubPlan 1 (returns $0)) AND f_leak(b))
+   SubPlan 1 (returns $0)
      ->  Seq Scan on s2
            Filter: (((x % 2) = 0) AND (y ~~ '%2f%'::text))
 (5 rows)
@@ -1457,11 +1457,11 @@ NOTICE:  f_leak => 03b26944890929ff751653acb2f2af79
 (1 row)
 
 EXPLAIN (COSTS OFF) SELECT * FROM s1 WHERE f_leak(b);
-                        QUERY PLAN                         
------------------------------------------------------------
+                                 QUERY PLAN                                  
+-----------------------------------------------------------------------------
  Seq Scan on s1
-   Filter: ((hashed SubPlan 1) AND f_leak(b))
-   SubPlan 1
+   Filter: ((ANY (a = $0) FROM HASHED SubPlan 1 (returns $0)) AND f_leak(b))
+   SubPlan 1 (returns $0)
      ->  Seq Scan on s2
            Filter: (((x % 2) = 0) AND (y ~~ '%af%'::text))
 (5 rows)
@@ -1473,15 +1473,15 @@ SELECT (SELECT x FROM s1 LIMIT 1) xx, * FROM s2 WHERE y like '%28%';
 (1 row)
 
 EXPLAIN (COSTS OFF) SELECT (SELECT x FROM s1 LIMIT 1) xx, * FROM s2 WHERE y like '%28%';
-                               QUERY PLAN                                
--------------------------------------------------------------------------
+                                QUERY PLAN                                 
+---------------------------------------------------------------------------
  Seq Scan on s2
    Filter: (((x % 2) = 0) AND (y ~~ '%28%'::text))
    SubPlan 2
      ->  Limit
            ->  Seq Scan on s1
-                 Filter: (hashed SubPlan 1)
-                 SubPlan 1
+                 Filter: (ANY (a = $1) FROM HASHED SubPlan 1 (returns $1))
+                 SubPlan 1 (returns $1)
                    ->  Seq Scan on s2 s2_1
                          Filter: (((x % 2) = 0) AND (y ~~ '%af%'::text))
 (9 rows)
@@ -2687,11 +2687,11 @@ NOTICE:  f_leak => bbb
 (1 row)
 
 EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
-                              QUERY PLAN                              
-----------------------------------------------------------------------
+                                             QUERY PLAN                                              
+-----------------------------------------------------------------------------------------------------
  Seq Scan on z1
-   Filter: ((NOT (hashed SubPlan 1)) AND ((a % 2) = 0) AND f_leak(b))
-   SubPlan 1
+   Filter: ((NOT (ANY (a = $0) FROM HASHED SubPlan 1 (returns $0))) AND ((a % 2) = 0) AND f_leak(b))
+   SubPlan 1 (returns $0)
      ->  Seq Scan on z1_blacklist
 (4 rows)
 
@@ -2705,11 +2705,11 @@ NOTICE:  f_leak => bbb
 (1 row)
 
 EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
-                              QUERY PLAN                              
-----------------------------------------------------------------------
+                                             QUERY PLAN                                              
+-----------------------------------------------------------------------------------------------------
  Seq Scan on z1
-   Filter: ((NOT (hashed SubPlan 1)) AND ((a % 2) = 0) AND f_leak(b))
-   SubPlan 1
+   Filter: ((NOT (ANY (a = $0) FROM HASHED SubPlan 1 (returns $0))) AND ((a % 2) = 0) AND f_leak(b))
+   SubPlan 1 (returns $0)
      ->  Seq Scan on z1_blacklist
 (4 rows)
 
@@ -2877,11 +2877,11 @@ NOTICE:  f_leak => bbb
 (1 row)
 
 EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
-                              QUERY PLAN                              
-----------------------------------------------------------------------
+                                             QUERY PLAN                                              
+-----------------------------------------------------------------------------------------------------
  Seq Scan on z1
-   Filter: ((NOT (hashed SubPlan 1)) AND ((a % 2) = 0) AND f_leak(b))
-   SubPlan 1
+   Filter: ((NOT (ANY (a = $0) FROM HASHED SubPlan 1 (returns $0))) AND ((a % 2) = 0) AND f_leak(b))
+   SubPlan 1 (returns $0)
      ->  Seq Scan on z1_blacklist
 (4 rows)
 
@@ -2903,11 +2903,11 @@ NOTICE:  f_leak => aba
 (1 row)
 
 EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
-                              QUERY PLAN                              
-----------------------------------------------------------------------
+                                             QUERY PLAN                                              
+-----------------------------------------------------------------------------------------------------
  Seq Scan on z1
-   Filter: ((NOT (hashed SubPlan 1)) AND ((a % 2) = 1) AND f_leak(b))
-   SubPlan 1
+   Filter: ((NOT (ANY (a = $0) FROM HASHED SubPlan 1 (returns $0))) AND ((a % 2) = 1) AND f_leak(b))
+   SubPlan 1 (returns $0)
      ->  Seq Scan on z1_blacklist
 (4 rows)
 
diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out
index 7a0d78dfe3..f8dd144c7e 100644
--- a/src/test/regress/expected/select_parallel.out
+++ b/src/test/regress/expected/select_parallel.out
@@ -291,15 +291,15 @@ alter table tenk2 set (parallel_workers = 0);
 explain (costs off)
 	select count(*) from tenk1 where (two, four) not in
 	(select hundred, thousand from tenk2 where thousand > 100);
-                      QUERY PLAN                      
-------------------------------------------------------
+                                                 QUERY PLAN                                                 
+------------------------------------------------------------------------------------------------------------
  Finalize Aggregate
    ->  Gather
          Workers Planned: 4
          ->  Partial Aggregate
                ->  Parallel Seq Scan on tenk1
-                     Filter: (NOT (hashed SubPlan 1))
-                     SubPlan 1
+                     Filter: (NOT (ANY ((two = $0) AND (four = $1)) FROM HASHED SubPlan 1 (returns $0,$1)))
+                     SubPlan 1 (returns $0,$1)
                        ->  Seq Scan on tenk2
                              Filter: (thousand > 100)
 (9 rows)
@@ -315,11 +315,11 @@ select count(*) from tenk1 where (two, four) not in
 explain (costs off)
 	select * from tenk1 where (unique1 + random())::integer not in
 	(select ten from tenk2);
-             QUERY PLAN             
-------------------------------------
+                                                     QUERY PLAN                                                      
+---------------------------------------------------------------------------------------------------------------------
  Seq Scan on tenk1
-   Filter: (NOT (hashed SubPlan 1))
-   SubPlan 1
+   Filter: (NOT (ANY ((((unique1)::double precision + random()))::integer = $0) FROM HASHED SubPlan 1 (returns $0)))
+   SubPlan 1 (returns $0)
      ->  Seq Scan on tenk2
 (4 rows)
 
@@ -1182,10 +1182,10 @@ ORDER BY 1, 2, 3;
 EXPLAIN (VERBOSE, COSTS OFF)
 SELECT generate_series(1, two), array(select generate_series(1, two))
   FROM tenk1 ORDER BY tenthous;
-                              QUERY PLAN                              
-----------------------------------------------------------------------
+                                QUERY PLAN                                 
+---------------------------------------------------------------------------
  ProjectSet
-   Output: generate_series(1, tenk1.two), (SubPlan 1), tenk1.tenthous
+   Output: generate_series(1, tenk1.two), ARRAY(SubPlan 1), tenk1.tenthous
    ->  Gather Merge
          Output: tenk1.two, tenk1.tenthous
          Workers Planned: 4
diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out
index e41b728df8..fb4e2a4d53 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -202,6 +202,57 @@ SELECT f1 AS "Correlated Field"
                 3
 (5 rows)
 
+-- Check ROWCOMPARE cases, both correlated and not
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT ROW(1, 2) = (SELECT f1, f2) AS eq FROM SUBSELECT_TBL;
+                                  QUERY PLAN                                   
+-------------------------------------------------------------------------------
+ Seq Scan on public.subselect_tbl
+   Output: (ROWCOMPARE ((1 = $2) AND (2 = $3)) FROM SubPlan 1 (returns $2,$3))
+   SubPlan 1 (returns $2,$3)
+     ->  Result
+           Output: subselect_tbl.f1, subselect_tbl.f2
+(5 rows)
+
+SELECT ROW(1, 2) = (SELECT f1, f2) AS eq FROM SUBSELECT_TBL;
+ eq 
+----
+ t
+ f
+ f
+ f
+ f
+ f
+ f
+ f
+(8 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT ROW(1, 2) = (SELECT 3, 4) AS eq FROM SUBSELECT_TBL;
+            QUERY PLAN             
+-----------------------------------
+ Seq Scan on public.subselect_tbl
+   Output: ((1 = $0) AND (2 = $1))
+   InitPlan 1 (returns $0,$1)
+     ->  Result
+           Output: 3, 4
+(5 rows)
+
+SELECT ROW(1, 2) = (SELECT 3, 4) AS eq FROM SUBSELECT_TBL;
+ eq 
+----
+ f
+ f
+ f
+ f
+ f
+ f
+ f
+ f
+(8 rows)
+
+SELECT ROW(1, 2) = (SELECT f1, f2 FROM SUBSELECT_TBL);  -- error
+ERROR:  more than one row returned by a subquery used as an expression
 -- Subselects without aliases
 SELECT count FROM (SELECT COUNT(DISTINCT name) FROM road);
  count 
@@ -324,11 +375,11 @@ explain (verbose, costs off) select '42' union all select 43;
 -- check materialization of an initplan reference (bug #14524)
 explain (verbose, costs off)
 select 1 = all (select (select 1));
-            QUERY PLAN             
------------------------------------
+                      QUERY PLAN                      
+------------------------------------------------------
  Result
-   Output: (SubPlan 2)
-   SubPlan 2
+   Output: (ALL (1 = $1) FROM SubPlan 2 (returns $1))
+   SubPlan 2 (returns $1)
      ->  Materialize
            Output: ($0)
            InitPlan 1 (returns $0)
@@ -377,7 +428,7 @@ select * from int4_tbl o where exists
               QUERY PLAN              
 --------------------------------------
  Seq Scan on int4_tbl o
-   Filter: (SubPlan 1)
+   Filter: EXISTS(SubPlan 1)
    SubPlan 1
      ->  Limit
            ->  Seq Scan on int4_tbl i
@@ -840,11 +891,11 @@ select * from outer_text where (f1, f2) not in (select * from inner_text);
 --
 explain (verbose, costs off)
 select 'foo'::text in (select 'bar'::name union all select 'bar'::name);
-             QUERY PLAN              
--------------------------------------
+                              QUERY PLAN                               
+-----------------------------------------------------------------------
  Result
-   Output: (hashed SubPlan 1)
-   SubPlan 1
+   Output: (ANY ('foo'::text = $0) FROM HASHED SubPlan 1 (returns $0))
+   SubPlan 1 (returns $0)
      ->  Append
            ->  Result
                  Output: 'bar'::name
@@ -864,11 +915,11 @@ select 'foo'::text in (select 'bar'::name union all select 'bar'::name);
 --
 explain (verbose, costs off)
 select row(row(row(1))) = any (select row(row(1)));
-                QUERY PLAN                 
--------------------------------------------
+                              QUERY PLAN                              
+----------------------------------------------------------------------
  Result
-   Output: (SubPlan 1)
-   SubPlan 1
+   Output: (ANY ('("(1)")'::record = $0) FROM SubPlan 1 (returns $0))
+   SubPlan 1 (returns $0)
      ->  Materialize
            Output: '("(1)")'::record
            ->  Result
@@ -907,11 +958,11 @@ language sql as 'select $1::text = $2';
 create operator = (procedure=bogus_int8_text_eq, leftarg=int8, rightarg=text);
 explain (costs off)
 select * from int8_tbl where q1 in (select c1 from inner_text);
-           QUERY PLAN           
---------------------------------
+                              QUERY PLAN                              
+----------------------------------------------------------------------
  Seq Scan on int8_tbl
-   Filter: (hashed SubPlan 1)
-   SubPlan 1
+   Filter: (ANY ((q1)::text = $0) FROM HASHED SubPlan 1 (returns $0))
+   SubPlan 1 (returns $0)
      ->  Seq Scan on inner_text
 (4 rows)
 
@@ -928,11 +979,11 @@ create or replace function bogus_int8_text_eq(int8, text) returns boolean
 language sql as 'select $1::text = $2 and $1::text = $2';
 explain (costs off)
 select * from int8_tbl where q1 in (select c1 from inner_text);
-           QUERY PLAN           
---------------------------------
+                                          QUERY PLAN                                          
+----------------------------------------------------------------------------------------------
  Seq Scan on int8_tbl
-   Filter: (hashed SubPlan 1)
-   SubPlan 1
+   Filter: (ANY (((q1)::text = $0) AND ((q1)::text = $0)) FROM HASHED SubPlan 1 (returns $0))
+   SubPlan 1 (returns $0)
      ->  Seq Scan on inner_text
 (4 rows)
 
@@ -949,11 +1000,11 @@ create or replace function bogus_int8_text_eq(int8, text) returns boolean
 language sql as 'select $2 = $1::text';
 explain (costs off)
 select * from int8_tbl where q1 in (select c1 from inner_text);
-              QUERY PLAN              
---------------------------------------
+                          QUERY PLAN                           
+---------------------------------------------------------------
  Seq Scan on int8_tbl
-   Filter: (SubPlan 1)
-   SubPlan 1
+   Filter: (ANY ($0 = (q1)::text) FROM SubPlan 1 (returns $0))
+   SubPlan 1 (returns $0)
      ->  Materialize
            ->  Seq Scan on inner_text
 (5 rows)
@@ -972,12 +1023,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: ((hashed SubPlan 2) OR (ten < 0))
-         SubPlan 2
+         Filter: ((ANY (unique2 = $1) FROM HASHED SubPlan 2 (returns $1)) OR (ten < 0))
+         SubPlan 2 (returns $1)
            ->  Index Only Scan using tenk1_unique1 on tenk1 k
 (5 rows)
 
@@ -997,7 +1048,7 @@ where (exists(select 1 from tenk1 k where k.unique1 = t.unique2) or ten < 0)
  Aggregate
    ->  Bitmap Heap Scan on tenk1 t
          Recheck Cond: (thousand = 1)
-         Filter: ((SubPlan 1) OR (ten < 0))
+         Filter: (EXISTS(SubPlan 1) OR (ten < 0))
          ->  Bitmap Index Scan on tenk1_thous_tenthous
                Index Cond: (thousand = 1)
          SubPlan 1
@@ -1022,11 +1073,11 @@ 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: ((SubPlan 1) OR (c3 < 0))
+         Filter: (EXISTS(SubPlan 1) OR (c3 < 0))
          SubPlan 1
            ->  Append
                  ->  Seq Scan on exists_tbl_null t2_1
@@ -1034,8 +1085,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: ((hashed SubPlan 2) OR (c3 < 0))
-         SubPlan 2
+         Filter: ((ANY (c1 = $1) FROM HASHED SubPlan 2 (returns $1)) OR (c3 < 0))
+         SubPlan 2 (returns $1)
            ->  Append
                  ->  Seq Scan on exists_tbl_null t2_4
                  ->  Seq Scan on exists_tbl_def t2_5
@@ -1143,16 +1194,16 @@ where o.ten = 0;
                                                                                          QUERY PLAN                                                                                          
 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Aggregate
-   Output: sum((((hashed SubPlan 1)))::integer)
+   Output: sum((((ANY (i.ten = $1) FROM HASHED SubPlan 1 (returns $1))))::integer)
    ->  Nested Loop
-         Output: ((hashed SubPlan 1))
+         Output: ((ANY (i.ten = $1) FROM HASHED SubPlan 1 (returns $1)))
          ->  Seq Scan on public.onek o
                Output: o.unique1, o.unique2, o.two, o.four, o.ten, o.twenty, o.hundred, o.thousand, o.twothousand, o.fivethous, o.tenthous, o.odd, o.even, o.stringu1, o.stringu2, o.string4
                Filter: (o.ten = 0)
          ->  Index Scan using onek_unique1 on public.onek i
-               Output: (hashed SubPlan 1), random()
+               Output: (ANY (i.ten = $1) FROM HASHED SubPlan 1 (returns $1)), random()
                Index Cond: (i.unique1 = o.unique1)
-               SubPlan 1
+               SubPlan 1 (returns $1)
                  ->  Seq Scan on public.int4_tbl
                        Output: int4_tbl.f1
                        Filter: (int4_tbl.f1 <= o.hundred)
@@ -1319,7 +1370,7 @@ select * from
 ----------------------------------------
  Values Scan on "*VALUES*"
    Output: "*VALUES*".column1
-   SubPlan 1
+   SubPlan 1 (returns $0)
      ->  Values Scan on "*VALUES*_1"
            Output: "*VALUES*_1".column1
 (5 rows)
@@ -1346,12 +1397,12 @@ select * from int4_tbl where
 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Nested Loop Semi Join
    Output: int4_tbl.f1
-   Join Filter: (CASE WHEN (hashed SubPlan 1) THEN int4_tbl.f1 ELSE NULL::integer END = b.ten)
+   Join Filter: (CASE WHEN (ANY (int4_tbl.f1 = $0) FROM HASHED SubPlan 1 (returns $0)) THEN int4_tbl.f1 ELSE NULL::integer END = b.ten)
    ->  Seq Scan on public.int4_tbl
          Output: int4_tbl.f1
    ->  Seq Scan on public.tenk1 b
          Output: b.unique1, b.unique2, b.two, b.four, b.ten, b.twenty, b.hundred, b.thousand, b.twothousand, b.fivethous, b.tenthous, b.odd, b.even, b.stringu1, b.stringu2, b.string4
-   SubPlan 1
+   SubPlan 1 (returns $0)
      ->  Index Only Scan using tenk1_unique1 on public.tenk1 a
            Output: a.unique1
 (10 rows)
@@ -1945,14 +1996,14 @@ select * from tenk1 A where exists
 (select 1 from tenk2 B
 where A.hundred in (select C.hundred FROM tenk2 C
 WHERE c.odd = b.odd));
-           QUERY PLAN            
----------------------------------
+                            QUERY PLAN                             
+-------------------------------------------------------------------
  Nested Loop Semi Join
-   Join Filter: (SubPlan 1)
+   Join Filter: (ANY (a.hundred = $1) FROM SubPlan 1 (returns $1))
    ->  Seq Scan on tenk1 a
    ->  Materialize
          ->  Seq Scan on tenk2 b
-   SubPlan 1
+   SubPlan 1 (returns $1)
      ->  Seq Scan on tenk2 c
            Filter: (odd = b.odd)
 (8 rows)
@@ -1962,14 +2013,14 @@ WHERE c.odd = b.odd));
 explain (costs off)
 SELECT * FROM tenk1 A LEFT JOIN tenk2 B
 ON A.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = b.odd);
-           QUERY PLAN            
----------------------------------
+                            QUERY PLAN                             
+-------------------------------------------------------------------
  Nested Loop Left Join
-   Join Filter: (SubPlan 1)
+   Join Filter: (ANY (a.hundred = $1) FROM SubPlan 1 (returns $1))
    ->  Seq Scan on tenk1 a
    ->  Materialize
          ->  Seq Scan on tenk2 b
-   SubPlan 1
+   SubPlan 1 (returns $1)
      ->  Seq Scan on tenk2 c
            Filter: (odd = b.odd)
 (8 rows)
@@ -1979,14 +2030,14 @@ ON A.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = b.odd);
 explain (costs off)
 SELECT * FROM tenk1 A LEFT JOIN tenk2 B
 ON B.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = a.odd);
-           QUERY PLAN            
----------------------------------
+                            QUERY PLAN                             
+-------------------------------------------------------------------
  Nested Loop Left Join
-   Join Filter: (SubPlan 1)
+   Join Filter: (ANY (b.hundred = $1) FROM SubPlan 1 (returns $1))
    ->  Seq Scan on tenk1 a
    ->  Materialize
          ->  Seq Scan on tenk2 b
-   SubPlan 1
+   SubPlan 1 (returns $1)
      ->  Seq Scan on tenk2 c
            Filter: (odd = a.odd)
 (8 rows)
diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out
index 1950e6f281..86f09b6284 100644
--- a/src/test/regress/expected/updatable_views.out
+++ b/src/test/regress/expected/updatable_views.out
@@ -2586,8 +2586,8 @@ SELECT * FROM v1 WHERE a=8;
 
 EXPLAIN (VERBOSE, COSTS OFF)
 UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a < 7 AND a != 6;
-                                             QUERY PLAN                                              
------------------------------------------------------------------------------------------------------
+                                                QUERY PLAN                                                 
+-----------------------------------------------------------------------------------------------------------
  Update on public.t1
    Update on public.t1 t1_1
    Update on public.t11 t1_2
@@ -2599,7 +2599,7 @@ UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a < 7 AND a != 6;
                ->  Index Scan using t1_a_idx on public.t1 t1_1
                      Output: t1_1.tableoid, t1_1.ctid
                      Index Cond: ((t1_1.a > 5) AND (t1_1.a < 7))
-                     Filter: ((t1_1.a <> 6) AND (SubPlan 1) AND snoop(t1_1.a) AND leakproof(t1_1.a))
+                     Filter: ((t1_1.a <> 6) AND EXISTS(SubPlan 1) AND snoop(t1_1.a) AND leakproof(t1_1.a))
                      SubPlan 1
                        ->  Append
                              ->  Seq Scan on public.t12 t12_1
@@ -2609,15 +2609,15 @@ UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a < 7 AND a != 6;
                ->  Index Scan using t11_a_idx on public.t11 t1_2
                      Output: t1_2.tableoid, t1_2.ctid
                      Index Cond: ((t1_2.a > 5) AND (t1_2.a < 7))
-                     Filter: ((t1_2.a <> 6) AND (SubPlan 1) AND snoop(t1_2.a) AND leakproof(t1_2.a))
+                     Filter: ((t1_2.a <> 6) AND EXISTS(SubPlan 1) AND snoop(t1_2.a) AND leakproof(t1_2.a))
                ->  Index Scan using t12_a_idx on public.t12 t1_3
                      Output: t1_3.tableoid, t1_3.ctid
                      Index Cond: ((t1_3.a > 5) AND (t1_3.a < 7))
-                     Filter: ((t1_3.a <> 6) AND (SubPlan 1) AND snoop(t1_3.a) AND leakproof(t1_3.a))
+                     Filter: ((t1_3.a <> 6) AND EXISTS(SubPlan 1) AND snoop(t1_3.a) AND leakproof(t1_3.a))
                ->  Index Scan using t111_a_idx on public.t111 t1_4
                      Output: t1_4.tableoid, t1_4.ctid
                      Index Cond: ((t1_4.a > 5) AND (t1_4.a < 7))
-                     Filter: ((t1_4.a <> 6) AND (SubPlan 1) AND snoop(t1_4.a) AND leakproof(t1_4.a))
+                     Filter: ((t1_4.a <> 6) AND EXISTS(SubPlan 1) AND snoop(t1_4.a) AND leakproof(t1_4.a))
 (30 rows)
 
 UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a < 7 AND a != 6;
@@ -2633,8 +2633,8 @@ SELECT * FROM t1 WHERE a=100; -- Nothing should have been changed to 100
 
 EXPLAIN (VERBOSE, COSTS OFF)
 UPDATE v1 SET a=a+1 WHERE snoop(a) AND leakproof(a) AND a = 8;
-                                    QUERY PLAN                                     
------------------------------------------------------------------------------------
+                                       QUERY PLAN                                        
+-----------------------------------------------------------------------------------------
  Update on public.t1
    Update on public.t1 t1_1
    Update on public.t11 t1_2
@@ -2646,7 +2646,7 @@ UPDATE v1 SET a=a+1 WHERE snoop(a) AND leakproof(a) AND a = 8;
                ->  Index Scan using t1_a_idx on public.t1 t1_1
                      Output: t1_1.a, t1_1.tableoid, t1_1.ctid
                      Index Cond: ((t1_1.a > 5) AND (t1_1.a = 8))
-                     Filter: ((SubPlan 1) AND snoop(t1_1.a) AND leakproof(t1_1.a))
+                     Filter: (EXISTS(SubPlan 1) AND snoop(t1_1.a) AND leakproof(t1_1.a))
                      SubPlan 1
                        ->  Append
                              ->  Seq Scan on public.t12 t12_1
@@ -2656,15 +2656,15 @@ UPDATE v1 SET a=a+1 WHERE snoop(a) AND leakproof(a) AND a = 8;
                ->  Index Scan using t11_a_idx on public.t11 t1_2
                      Output: t1_2.a, t1_2.tableoid, t1_2.ctid
                      Index Cond: ((t1_2.a > 5) AND (t1_2.a = 8))
-                     Filter: ((SubPlan 1) AND snoop(t1_2.a) AND leakproof(t1_2.a))
+                     Filter: (EXISTS(SubPlan 1) AND snoop(t1_2.a) AND leakproof(t1_2.a))
                ->  Index Scan using t12_a_idx on public.t12 t1_3
                      Output: t1_3.a, t1_3.tableoid, t1_3.ctid
                      Index Cond: ((t1_3.a > 5) AND (t1_3.a = 8))
-                     Filter: ((SubPlan 1) AND snoop(t1_3.a) AND leakproof(t1_3.a))
+                     Filter: (EXISTS(SubPlan 1) AND snoop(t1_3.a) AND leakproof(t1_3.a))
                ->  Index Scan using t111_a_idx on public.t111 t1_4
                      Output: t1_4.a, t1_4.tableoid, t1_4.ctid
                      Index Cond: ((t1_4.a > 5) AND (t1_4.a = 8))
-                     Filter: ((SubPlan 1) AND snoop(t1_4.a) AND leakproof(t1_4.a))
+                     Filter: (EXISTS(SubPlan 1) AND snoop(t1_4.a) AND leakproof(t1_4.a))
 (30 rows)
 
 UPDATE v1 SET a=a+1 WHERE snoop(a) AND leakproof(a) AND a = 8;
diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sql
index 2f3601a058..7c42ebc36f 100644
--- a/src/test/regress/sql/subselect.sql
+++ b/src/test/regress/sql/subselect.sql
@@ -82,6 +82,20 @@ SELECT f1 AS "Correlated Field"
   WHERE (f1, f2) IN (SELECT f2, CAST(f3 AS int4) FROM SUBSELECT_TBL
                      WHERE f3 IS NOT NULL);
 
+-- Check ROWCOMPARE cases, both correlated and not
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT ROW(1, 2) = (SELECT f1, f2) AS eq FROM SUBSELECT_TBL;
+
+SELECT ROW(1, 2) = (SELECT f1, f2) AS eq FROM SUBSELECT_TBL;
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT ROW(1, 2) = (SELECT 3, 4) AS eq FROM SUBSELECT_TBL;
+
+SELECT ROW(1, 2) = (SELECT 3, 4) AS eq FROM SUBSELECT_TBL;
+
+SELECT ROW(1, 2) = (SELECT f1, f2 FROM SUBSELECT_TBL);  -- error
+
 -- Subselects without aliases
 
 SELECT count FROM (SELECT COUNT(DISTINCT name) FROM road);
-- 
2.39.3

---

From: "Andrey M. Borodin" <[email-masked]>
Date: Mon, 4 Mar 2024 11:47:21 +0500

> On 17 Feb 2024, at 00:38, Tom Lane <[email-masked]> wrote:
> 

Aleksander, as long as your was reviewing this previously, I’ve added you to reviewers of this CF entry [0]. Please, ping me or remove yourself, it it’s actually not a case.

BTW, as long as there’s a new version and some input from Tom, would you be willing to post fleshier review?

Thanks for working on this!

Best regards, Andrey Borodin.

[0] https://commitfest.postgresql.org/47/4782/

---

From: Dean Rasheed <[email-masked]>
Date: Sat, 9 Mar 2024 13:07:40 +0000

> > nicer way to present it than the particular spelling I used here.

One thing that concerns me about making even greater use of "$n" is
the potential for confusion with generic plan parameters. Maybe it's
always possible to work out which is which from context, but still it
looks messy:

drop table if exists foo;
create table foo(id int, x int, y int);

explain (verbose, costs off, generic_plan)
select row($3,$4) = (select x,y from foo where id=y) and
       row($1,$2) = (select min(x+y),max(x+y) from generate_series(1,3) x)
from generate_series(1,3) y;

                                                  QUERY PLAN
---------------------------------------------------------------------------------------------------------------
 Function Scan on pg_catalog.generate_series y
   Output: (($3 = $0) AND ($4 = $1) AND (ROWCOMPARE (($1 = $3) AND ($2
= $4)) FROM SubPlan 2 (returns $3,$4)))
   Function Call: generate_series(1, 3)
   InitPlan 1 (returns $0,$1)
     ->  Seq Scan on public.foo
           Output: foo.x, foo.y
           Filter: (foo.id = foo.y)
   SubPlan 2 (returns $3,$4)
     ->  Aggregate
           Output: min((x.x + y.y)), max((x.x + y.y))
           ->  Function Scan on pg_catalog.generate_series x
                 Output: x.x
                 Function Call: generate_series(1, 3)

Another odd thing about that is the inconsistency between how the
SubPlan and InitPlan expressions are displayed. I think "ROWCOMPARE"
is really just an internal detail that could be omitted without losing
anything. But the "FROM SubPlan ..." is useful to work out where it's
coming from. Should it also output "FROM InitPlan ..."? I think that
would risk making it harder to read.

Another possibility is to put the SubPlan and InitPlan names inline,
rather than outputting "FROM SubPlan ...". I had a go at hacking that
up and this was the result:

                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 Function Scan on pg_catalog.generate_series y
   Output: (($3 = (InitPlan 1).$0) AND ($4 = (InitPlan 1).$1) AND
((($1 = (SubPlan 2).$3) AND ($2 = (SubPlan 2).$4))))
   Function Call: generate_series(1, 3)
   InitPlan 1 (returns $0,$1)
     ->  Seq Scan on public.foo
           Output: foo.x, foo.y
           Filter: (foo.id = foo.y)
   SubPlan 2 (returns $3,$4)
     ->  Aggregate
           Output: min((x.x + y.y)), max((x.x + y.y))
           ->  Function Scan on pg_catalog.generate_series x
                 Output: x.x
                 Function Call: generate_series(1, 3)

It's a little more verbose in this case, but in a lot of other cases
it ended up being more compact.

The code is a bit messy, but I think the regression test output
(attached) is clearer and easier to interpret. SubPlans and InitPlans
are displayed consistently, and it's easier to distinguish
SubPlan/InitPlan outputs from external parameters.

There are a few more regression test changes, corresponding to cases
where InitPlans are referenced, such as:

  Seq Scan on document
-   Filter: ((dlevel <= $0) AND f_leak(dtitle))
+   Filter: ((dlevel <= (InitPlan 1).$0) AND f_leak(dtitle))
    InitPlan 1 (returns $0)
      ->  Index Scan using uaccount_pkey on uaccount
            Index Cond: (pguser = CURRENT_USER)

but I think that's useful extra clarification.

Regards,
Dean
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
new file mode 100644
index c355e8f..f0ff936
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -3021,7 +3021,7 @@ select exists(select 1 from pg_enum), su
                     QUERY PLAN                    
 --------------------------------------------------
  Foreign Scan
-   Output: $0, (sum(ft1.c1))
+   Output: (InitPlan 1).$0, (sum(ft1.c1))
    Relations: Aggregate on (public.ft1)
    Remote SQL: SELECT sum("C 1") FROM "S 1"."T 1"
    InitPlan 1 (returns $0)
@@ -3039,7 +3039,7 @@ select exists(select 1 from pg_enum), su
                     QUERY PLAN                     
 ---------------------------------------------------
  GroupAggregate
-   Output: $0, sum(ft1.c1)
+   Output: (InitPlan 1).$0, sum(ft1.c1)
    InitPlan 1 (returns $0)
      ->  Seq Scan on pg_catalog.pg_enum
    ->  Foreign Scan on public.ft1
@@ -3264,14 +3264,14 @@ select sum(c1) filter (where (c1 / c1) *
 
+   Output: sum(ft1.c2) FILTER (WHERE (ANY (ft1.c2 = (hashed SubPlan 1).$0)))
@@ -11895,12 +11895,12 @@ CREATE FOREIGN TABLE foreign_tbl2 () INH
+   Filter: (ANY ((base_tbl.a = (SubPlan 1).$1) AND ((random() > '0'::double precision) = (SubPlan 1).$2)))
new file mode 100644
index 47e1472..d5919a9
@@ -326,6 +326,7 @@ build_subplan(PlannerInfo *root, Plan *p
 
 	/*
@@ -560,22 +561,31 @@ build_subplan(PlannerInfo *root, Plan *p
 												   splan->plan_id);

 	/* Lastly, fill in the cost estimates for use later */
 	cost_subplan(root, splan, plan);
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
new file mode 100644
index 2a1ee69..44de557
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -171,6 +171,7 @@ typedef struct
 	List	   *using_names;	/* List of assigned names for USING columns */
 	/* Remaining fields are used only when deparsing a Plan tree: */
 	Plan	   *plan;			/* immediate parent of current expression */
+	SubPlan    *in_subplan;		/* subplan containing current expression */
 	List	   *ancestors;		/* ancestors of plan */
 	Plan	   *outer_plan;		/* outer subnode, or NULL if none */
 	Plan	   *inner_plan;		/* inner subnode, or NULL if none */
@@ -8184,6 +8185,80 @@ get_parameter(Param *param, deparse_cont
 		return;
 	}
 
+	/* Is it a subplan output? */
+	if (param->paramkind == PARAM_EXEC)
+	{
+		SubPlan    *subplan = NULL;
+
+		dpns = (deparse_namespace *) linitial(context->namespaces);
+
+		/* subplan containing this expression? */
+		if (dpns->in_subplan)
+		{
+			if (dpns->in_subplan->setParam)
+			{
+				foreach_int(paramid, dpns->in_subplan->setParam)
+				{
+					if (paramid == param->paramid)
+					{
+						subplan = dpns->in_subplan;
+						break;
+					}
+				}
+			}
+			else if (dpns->in_subplan->paramIds)
+			{
+				foreach_int(paramid, dpns->in_subplan->paramIds)
+				{
+					if (paramid == param->paramid)
+					{
+						subplan = dpns->in_subplan;
+						break;
+					}
+				}
+			}
+		}
+
+		/* else initplan output? */
+		if (subplan == NULL)
+		{
+			foreach_node(SubPlan, initplan, dpns->plan->initPlan)
+			{
+				if (initplan->setParam)
+				{
+					foreach_int(paramid, initplan->setParam)
+					{
+						if (paramid == param->paramid)
+						{
+							subplan = initplan;
+							break;
+						}
+					}
+				}
+				else if (initplan->paramIds)
+				{
+					foreach_int(paramid, initplan->paramIds)
+					{
+						if (paramid == param->paramid)
+						{
+							subplan = initplan;
+							break;
+						}
+					}
+				}
+			}
+		}
+
+		if (subplan)
+		{
+			appendStringInfo(context->buf, "(%s%s %d).$%d",
+							 subplan->useHashTable ? "hashed " : "",
+							 subplan == dpns->in_subplan ? "SubPlan" : "InitPlan",
+							 subplan->plan_id, param->paramid);
+			return;
+		}
+	}
+
 	/*
 	 * If it's an external parameter, see if the outermost namespace provides
 	 * function argument names.
@@ -8863,17 +8938,68 @@ get_rule_expr(Node *node, deparse_contex
 		case T_SubPlan:
 			{
 				SubPlan    *subplan = (SubPlan *) node;
+				bool		show_subplan_name = true;
+				deparse_namespace *dpns;
 
 				/*
 				 * We cannot see an already-planned subplan in rule deparsing,
 				 * only while EXPLAINing a query plan.  We don't try to
 				 * reconstruct the original SQL, just reference the subplan
-				 * that appears elsewhere in EXPLAIN's result.
+				 * that appears elsewhere in EXPLAIN's result.  It does seem
+				 * useful to show the subLinkType and testexpr, however, and
+				 * we also note whether the subplan will be hashed.
 				 */
-				if (subplan->useHashTable)
-					appendStringInfo(buf, "(hashed %s)", subplan->plan_name);
-				else
-					appendStringInfo(buf, "(%s)", subplan->plan_name);
+				dpns = linitial(context->namespaces);
+				dpns->in_subplan = subplan;
+
+				switch (subplan->subLinkType)
+				{
+					case EXISTS_SUBLINK:
+						appendStringInfoString(buf, "EXISTS(");
+						Assert(subplan->testexpr == NULL);
+						break;
+					case ALL_SUBLINK:
+						appendStringInfoString(buf, "(ALL ");
+						get_rule_expr(subplan->testexpr, context, showimplicit);
+						appendStringInfoString(buf, ")");
+						show_subplan_name = false;
+						break;
+					case ANY_SUBLINK:
+						appendStringInfoString(buf, "(ANY ");
+						get_rule_expr(subplan->testexpr, context, showimplicit);
+						appendStringInfoString(buf, ")");
+						show_subplan_name = false;
+						break;
+					case ROWCOMPARE_SUBLINK:
+						appendStringInfoString(buf, "(");
+						get_rule_expr(subplan->testexpr, context, showimplicit);
+						appendStringInfoString(buf, ")");
+						show_subplan_name = false;
+						break;
+					case EXPR_SUBLINK:
+					case MULTIEXPR_SUBLINK:
+						/* No need to decorate these subplan references */
+						appendStringInfoString(buf, "(");
+						Assert(subplan->testexpr == NULL);
+						break;
+					case ARRAY_SUBLINK:
+						appendStringInfoString(buf, "ARRAY(");
+						Assert(subplan->testexpr == NULL);
+						break;
+					case CTE_SUBLINK:
+						/* This case is unreachable within expressions */
+						appendStringInfoString(buf, "CTE(");
+						Assert(subplan->testexpr == NULL);
+						break;
+				}
+				dpns->in_subplan = NULL;
+				if (show_subplan_name)
+				{
+					if (subplan->useHashTable)
+						appendStringInfo(buf, "hashed %s)", subplan->plan_name);
+					else
+						appendStringInfo(buf, "%s)", subplan->plan_name);
+				}
 			}
 			break;
 
diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out
new file mode 100644
index f86cf8d..94afdf5
--- a/src/test/regress/expected/aggregates.out
+++ b/src/test/regress/expected/aggregates.out
@@ -738,7 +738,7 @@ select array(select sum(x+y) s
                             QUERY PLAN                             
 -------------------------------------------------------------------
  Function Scan on pg_catalog.generate_series x
-   Output: (SubPlan 1)
+   Output: ARRAY(SubPlan 1)
    Function Call: generate_series(1, 3)
    SubPlan 1
      ->  Sort
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
new file mode 100644
index 130a924..85a3c14
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -1643,7 +1643,7 @@ explain (verbose, costs off) select min(
                                    QUERY PLAN                                    
 ---------------------------------------------------------------------------------
  Result
-   Output: $0
+   Output: (InitPlan 1).$0
    InitPlan 1 (returns $0)
      ->  Limit
            Output: ((1 - matest0.id))
diff --git a/src/test/regress/expected/insert_conflict.out b/src/test/regress/expected/insert_conflict.out
new file mode 100644
index 563c5eb..701217d
--- a/src/test/regress/expected/insert_conflict.out
+++ b/src/test/regress/expected/insert_conflict.out
@@ -50,7 +50,7 @@ explain (costs off) insert into insertco
  Insert on insertconflicttest
    Conflict Resolution: UPDATE
    Conflict Arbiter Indexes: op_index_key, collation_index_key, both_index_key
-   Conflict Filter: (SubPlan 1)
+   Conflict Filter: EXISTS(SubPlan 1)
    ->  Result
    SubPlan 1
      ->  Index Only Scan using both_index_expr_key on insertconflicttest ii
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
new file mode 100644
index 9605400..06b6b8c
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -3028,11 +3028,11 @@ where unique1 in (select unique2 from te
 explain (costs off)
 select a.* from tenk1 a
 where unique1 not in (select unique2 from tenk1 b);
-                       QUERY PLAN                       
---------------------------------------------------------
+                       QUERY PLAN                        
+---------------------------------------------------------
  Seq Scan on tenk1 a
-   Filter: (NOT (hashed SubPlan 1))
-   SubPlan 1
+   Filter: (NOT (ANY (unique1 = (hashed SubPlan 1).$0)))
+   SubPlan 1 (returns $0)
      ->  Index Only Scan using tenk1_unique2 on tenk1 b
 (4 rows)
 
@@ -5278,13 +5278,13 @@ explain (costs off)
 select a.unique1, b.unique2
   from onek a left join onek b on a.unique1 = b.unique2
   where (b.unique2, random() > 0) = any (select q1, random() > 0 from int8_tbl c where c.q1 < b.unique1);
-                        QUERY PLAN                        
-----------------------------------------------------------
+                                                  QUERY PLAN                                                  
+--------------------------------------------------------------------------------------------------------------
  Hash Join
    Hash Cond: (b.unique2 = a.unique1)
    ->  Seq Scan on onek b
-         Filter: (SubPlan 1)
-         SubPlan 1
+         Filter: (ANY ((unique2 = (SubPlan 1).$1) AND ((random() > '0'::double precision) = (SubPlan 1).$2)))
+         SubPlan 1 (returns $1,$2)
            ->  Seq Scan on int8_tbl c
                  Filter: (q1 < b.unique1)
    ->  Hash
@@ -8262,8 +8262,8 @@ lateral (select * from int8_tbl t1,
                                      where q2 = (select greatest(t1.q1,t2.q2))
                                        and (select v.id=0)) offset 0) ss2) ss
          where t1.q1 = ss.q2) ss0;
-                                  QUERY PLAN                                   
--------------------------------------------------------------------------------
+                                                       QUERY PLAN                                                       
+------------------------------------------------------------------------------------------------------------------------
  Nested Loop
    Output: "*VALUES*".column1, t1.q1, t1.q2, ss2.q1, ss2.q2
    ->  Seq Scan on public.int8_tbl t1
@@ -8277,11 +8277,11 @@ lateral (select * from int8_tbl t1,
                Filter: (t1.q1 = ss2.q2)
                ->  Seq Scan on public.int8_tbl t2
                      Output: t2.q1, t2.q2
-                     Filter: (SubPlan 3)
-                     SubPlan 3
+                     Filter: (ANY ((t2.q1 = (SubPlan 3).$5) AND ((random() > '0'::double precision) = (SubPlan 3).$6)))
+                     SubPlan 3 (returns $5,$6)
                        ->  Result
                              Output: t3.q2, (random() > '0'::double precision)
-                             One-Time Filter: $4
+                             One-Time Filter: (InitPlan 2).$4
                              InitPlan 1 (returns $2)
                                ->  Result
                                      Output: GREATEST(t1.q1, t2.q2)
diff --git a/src/test/regress/expected/memoize.out b/src/test/regress/expected/memoize.out
new file mode 100644
index cf6886a..8fb2406
--- a/src/test/regress/expected/memoize.out
+++ b/src/test/regress/expected/memoize.out
@@ -313,7 +313,7 @@ WHERE unique1 < 3
 ----------------------------------------------------------------
  Index Scan using tenk1_unique1 on tenk1 t0
    Index Cond: (unique1 < 3)
-   Filter: (SubPlan 1)
+   Filter: EXISTS(SubPlan 1)
    SubPlan 1
      ->  Nested Loop
            ->  Index Scan using tenk1_hundred on tenk1 t2
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
new file mode 100644
index bf0657b..89312a5
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -3976,7 +3976,7 @@ select * from listp where a = (select 2)
                     QUERY PLAN                    
 --------------------------------------------------
  Seq Scan on listp1 listp (actual rows=0 loops=1)
-   Filter: ((b <> 10) AND (a = $0))
+   Filter: ((b <> 10) AND (a = (InitPlan 1).$0))
    InitPlan 1 (returns $0)
      ->  Result (never executed)
 (4 rows)
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
new file mode 100644
index 6988128..55db827
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -265,10 +265,10 @@ NOTICE:  f_leak => awesome science ficti
 (5 rows)
 
 EXPLAIN (COSTS OFF) SELECT * FROM document WHERE f_leak(dtitle);
-                     QUERY PLAN                     
-----------------------------------------------------
+                         QUERY PLAN                         
+------------------------------------------------------------
  Seq Scan on document
-   Filter: ((dlevel <= $0) AND f_leak(dtitle))
+   Filter: ((dlevel <= (InitPlan 1).$0) AND f_leak(dtitle))
    InitPlan 1 (returns $0)
      ->  Index Scan using uaccount_pkey on uaccount
            Index Cond: (pguser = CURRENT_USER)
@@ -329,10 +329,10 @@ NOTICE:  f_leak => awesome technology bo
 (7 rows)
 
 EXPLAIN (COSTS OFF) SELECT * FROM document WHERE f_leak(dtitle);
-                                          QUERY PLAN                                          
-----------------------------------------------------------------------------------------------
+                                                QUERY PLAN                                                 
+-----------------------------------------------------------------------------------------------------------
  Seq Scan on document
-   Filter: ((cid <> 44) AND (cid <> 44) AND (cid < 50) AND (dlevel <= $0) AND f_leak(dtitle))
+   Filter: ((cid <> 44) AND (cid <> 44) AND (cid < 50) AND (dlevel <= (InitPlan 1).$0) AND f_leak(dtitle))
    InitPlan 1 (returns $0)
      ->  Index Scan using uaccount_pkey on uaccount
            Index Cond: (pguser = CURRENT_USER)
@@ -1059,10 +1059,10 @@ NOTICE:  f_leak => awesome science ficti
 (4 rows)
 
 EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
-                          QUERY PLAN                          
---------------------------------------------------------------
+                                QUERY PLAN                                 
+---------------------------------------------------------------------------
  Seq Scan on part_document_fiction part_document
-   Filter: ((cid < 55) AND (dlevel <= $0) AND f_leak(dtitle))
+   Filter: ((cid < 55) AND (dlevel <= (InitPlan 1).$0) AND f_leak(dtitle))
    InitPlan 1 (returns $0)
      ->  Index Scan using uaccount_pkey on uaccount
            Index Cond: (pguser = CURRENT_USER)
@@ -1137,10 +1137,10 @@ NOTICE:  f_leak => awesome science ficti
 (4 rows)
 
 EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
-                          QUERY PLAN                          
---------------------------------------------------------------
+                                QUERY PLAN                                 
+---------------------------------------------------------------------------
  Seq Scan on part_document_fiction part_document
-   Filter: ((cid < 55) AND (dlevel <= $0) AND f_leak(dtitle))
+   Filter: ((cid < 55) AND (dlevel <= (InitPlan 1).$0) AND f_leak(dtitle))
    InitPlan 1 (returns $0)
      ->  Index Scan using uaccount_pkey on uaccount
            Index Cond: (pguser = CURRENT_USER)
@@ -1437,11 +1437,11 @@ NOTICE:  f_leak => 03b26944890929ff75165
 (1 row)
 
 EXPLAIN (COSTS OFF) SELECT * FROM only s1 WHERE f_leak(b);
-                        QUERY PLAN                         
------------------------------------------------------------
+                         QUERY PLAN                          
+-------------------------------------------------------------
  Seq Scan on s1
-   Filter: ((hashed SubPlan 1) AND f_leak(b))
-   SubPlan 1
+   Filter: ((ANY (a = (hashed SubPlan 1).$0)) AND f_leak(b))
+   SubPlan 1 (returns $0)
      ->  Seq Scan on s2
            Filter: (((x % 2) = 0) AND (y ~~ '%2f%'::text))
 (5 rows)
@@ -1457,11 +1457,11 @@ NOTICE:  f_leak => 03b26944890929ff75165
 (1 row)
 
 EXPLAIN (COSTS OFF) SELECT * FROM s1 WHERE f_leak(b);
-                        QUERY PLAN                         
------------------------------------------------------------
+                         QUERY PLAN                          
+-------------------------------------------------------------
  Seq Scan on s1
-   Filter: ((hashed SubPlan 1) AND f_leak(b))
-   SubPlan 1
+   Filter: ((ANY (a = (hashed SubPlan 1).$0)) AND f_leak(b))
+   SubPlan 1 (returns $0)
      ->  Seq Scan on s2
            Filter: (((x % 2) = 0) AND (y ~~ '%af%'::text))
 (5 rows)
@@ -1480,8 +1480,8 @@ EXPLAIN (COSTS OFF) SELECT (SELECT x FRO
    SubPlan 2
      ->  Limit
            ->  Seq Scan on s1
-                 Filter: (hashed SubPlan 1)
-                 SubPlan 1
+                 Filter: (ANY (a = (hashed SubPlan 1).$1))
+                 SubPlan 1 (returns $1)
                    ->  Seq Scan on s2 s2_1
                          Filter: (((x % 2) = 0) AND (y ~~ '%af%'::text))
 (9 rows)
@@ -2687,11 +2687,11 @@ NOTICE:  f_leak => bbb
 (1 row)
 
 EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
-                              QUERY PLAN                              
-----------------------------------------------------------------------
+                                     QUERY PLAN                                      
+-------------------------------------------------------------------------------------
  Seq Scan on z1
-   Filter: ((NOT (hashed SubPlan 1)) AND ((a % 2) = 0) AND f_leak(b))
-   SubPlan 1
+   Filter: ((NOT (ANY (a = (hashed SubPlan 1).$0))) AND ((a % 2) = 0) AND f_leak(b))
+   SubPlan 1 (returns $0)
      ->  Seq Scan on z1_blacklist
 (4 rows)

 EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
-                              QUERY PLAN                              
-----------------------------------------------------------------------
+                                     QUERY PLAN                                      
+-------------------------------------------------------------------------------------
  Seq Scan on z1
-   Filter: ((NOT (hashed SubPlan 1)) AND ((a % 2) = 0) AND f_leak(b))
-   SubPlan 1
+   Filter: ((NOT (ANY (a = (hashed SubPlan 1).$0))) AND ((a % 2) = 0) AND f_leak(b))
+   SubPlan 1 (returns $0)
      ->  Seq Scan on z1_blacklist
 (4 rows)

 EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
-                              QUERY PLAN                              
-----------------------------------------------------------------------
+                                     QUERY PLAN                                      
+-------------------------------------------------------------------------------------
  Seq Scan on z1
-   Filter: ((NOT (hashed SubPlan 1)) AND ((a % 2) = 0) AND f_leak(b))
-   SubPlan 1
+   Filter: ((NOT (ANY (a = (hashed SubPlan 1).$0))) AND ((a % 2) = 0) AND f_leak(b))
+   SubPlan 1 (returns $0)
      ->  Seq Scan on z1_blacklist
 (4 rows)

 EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
-                              QUERY PLAN                              
-----------------------------------------------------------------------
+                                     QUERY PLAN                                      
+-------------------------------------------------------------------------------------
  Seq Scan on z1
-   Filter: ((NOT (hashed SubPlan 1)) AND ((a % 2) = 1) AND f_leak(b))
-   SubPlan 1
+   Filter: ((NOT (ANY (a = (hashed SubPlan 1).$0))) AND ((a % 2) = 1) AND f_leak(b))
+   SubPlan 1 (returns $0)
      ->  Seq Scan on z1_blacklist
 (4 rows)
 
diff --git a/src/test/regress/expected/rowtypes.out b/src/test/regress/expected/rowtypes.out
new file mode 100644
index 8f3c153..30cb35e
--- a/src/test/regress/expected/rowtypes.out
+++ b/src/test/regress/expected/rowtypes.out
@@ -1260,7 +1260,7 @@ where (select * from (select c as c1) s
    SubPlan 3
      ->  Result
            Output: cte.c
-           One-Time Filter: $2
+           One-Time Filter: (InitPlan 2).$2
            InitPlan 2 (returns $2)
              ->  Result
                    Output: ((cte.c).f1 > 0)
diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out
new file mode 100644
index 7a0d78d..a4aecb2
--- a/src/test/regress/expected/select_parallel.out
+++ b/src/test/regress/expected/select_parallel.out
@@ -291,15 +291,15 @@ alter table tenk2 set (parallel_workers
 explain (costs off)
 	select count(*) from tenk1 where (two, four) not in
 	(select hundred, thousand from tenk2 where thousand > 100);
-                      QUERY PLAN                      
-------------------------------------------------------
+                                                 QUERY PLAN                                                 
+------------------------------------------------------------------------------------------------------------
  Finalize Aggregate
    ->  Gather
          Workers Planned: 4
          ->  Partial Aggregate
                ->  Parallel Seq Scan on tenk1
-                     Filter: (NOT (hashed SubPlan 1))
-                     SubPlan 1
+                     Filter: (NOT (ANY ((two = (hashed SubPlan 1).$0) AND (four = (hashed SubPlan 1).$1))))
+                     SubPlan 1 (returns $0,$1)
                        ->  Seq Scan on tenk2
                              Filter: (thousand > 100)
 (9 rows)
@@ -315,11 +315,11 @@ select count(*) from tenk1 where (two, f
 explain (costs off)
 	select * from tenk1 where (unique1 + random())::integer not in
 	(select ten from tenk2);
-             QUERY PLAN             
-------------------------------------
+                                             QUERY PLAN                                              
+-----------------------------------------------------------------------------------------------------
  Seq Scan on tenk1
-   Filter: (NOT (hashed SubPlan 1))
-   SubPlan 1
+   Filter: (NOT (ANY ((((unique1)::double precision + random()))::integer = (hashed SubPlan 1).$0)))
+   SubPlan 1 (returns $0)
      ->  Seq Scan on tenk2
 (4 rows)
 
@@ -1182,10 +1182,10 @@ ORDER BY 1, 2, 3;
 EXPLAIN (VERBOSE, COSTS OFF)
 SELECT generate_series(1, two), array(select generate_series(1, two))
   FROM tenk1 ORDER BY tenthous;
-                              QUERY PLAN                              
-----------------------------------------------------------------------
+                                QUERY PLAN                                 
+---------------------------------------------------------------------------
  ProjectSet
-   Output: generate_series(1, tenk1.two), (SubPlan 1), tenk1.tenthous
+   Output: generate_series(1, tenk1.two), ARRAY(SubPlan 1), tenk1.tenthous
    ->  Gather Merge
          Output: tenk1.two, tenk1.tenthous
          Workers Planned: 4
diff --git a/src/test/regress/expected/sqljson.out b/src/test/regress/expected/sqljson.out
new file mode 100644
index 5e7da96..14015bb
--- a/src/test/regress/expected/sqljson.out
+++ b/src/test/regress/expected/sqljson.out
@@ -1064,7 +1064,7 @@ SELECT JSON_ARRAY(SELECT i FROM (VALUES
                              QUERY PLAN                              
 ---------------------------------------------------------------------
  Result
-   Output: $0
+   Output: (InitPlan 1).$0
    InitPlan 1 (returns $0)
      ->  Aggregate
            Output: JSON_ARRAYAGG("*VALUES*".column1 RETURNING jsonb)
diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out
new file mode 100644
index e41b728..8be66a1
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -202,6 +202,57 @@ SELECT f1 AS "Correlated Field"
                 3
 (5 rows)
 
+-- Check ROWCOMPARE cases, both correlated and not
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT ROW(1, 2) = (SELECT f1, f2) AS eq FROM SUBSELECT_TBL;
+                         QUERY PLAN                          
+-------------------------------------------------------------
+ Seq Scan on public.subselect_tbl
+   Output: (((1 = (SubPlan 1).$2) AND (2 = (SubPlan 1).$3)))
+   SubPlan 1 (returns $2,$3)
+     ->  Result
+           Output: subselect_tbl.f1, subselect_tbl.f2
+(5 rows)
+
+SELECT ROW(1, 2) = (SELECT f1, f2) AS eq FROM SUBSELECT_TBL;
+ eq 
+----
+ t
+ f
+ f
+ f
+ f
+ f
+ f
+ f
+(8 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT ROW(1, 2) = (SELECT 3, 4) AS eq FROM SUBSELECT_TBL;
+                         QUERY PLAN                          
+-------------------------------------------------------------
+ Seq Scan on public.subselect_tbl
+   Output: ((1 = (InitPlan 1).$0) AND (2 = (InitPlan 1).$1))
+   InitPlan 1 (returns $0,$1)
+     ->  Result
+           Output: 3, 4
+(5 rows)
+
+SELECT ROW(1, 2) = (SELECT 3, 4) AS eq FROM SUBSELECT_TBL;
+ eq 
+----
+ f
+ f
+ f
+ f
+ f
+ f
+ f
+ f
+(8 rows)
+
+SELECT ROW(1, 2) = (SELECT f1, f2 FROM SUBSELECT_TBL);  -- error
+ERROR:  more than one row returned by a subquery used as an expression
 -- Subselects without aliases
 SELECT count FROM (SELECT COUNT(DISTINCT name) FROM road);
  count 
@@ -324,11 +375,11 @@ explain (verbose, costs off) select '42'
 -- check materialization of an initplan reference (bug #14524)
 explain (verbose, costs off)
 select 1 = all (select (select 1));
-            QUERY PLAN             
------------------------------------
+              QUERY PLAN              
+--------------------------------------
  Result
-   Output: (SubPlan 2)
-   SubPlan 2
+   Output: (ALL (1 = (SubPlan 2).$1))
+   SubPlan 2 (returns $1)
      ->  Materialize
            Output: ($0)
            InitPlan 1 (returns $0)
@@ -377,7 +428,7 @@ select * from int4_tbl o where exists
               QUERY PLAN              
 --------------------------------------
  Seq Scan on int4_tbl o
-   Filter: (SubPlan 1)
+   Filter: EXISTS(SubPlan 1)
    SubPlan 1
      ->  Limit
            ->  Seq Scan on int4_tbl i
@@ -840,11 +891,11 @@ select * from outer_text where (f1, f2)
 --
 explain (verbose, costs off)
 select 'foo'::text in (select 'bar'::name union all select 'bar'::name);
-             QUERY PLAN              
--------------------------------------
+                      QUERY PLAN                       
+-------------------------------------------------------
  Result
-   Output: (hashed SubPlan 1)
-   SubPlan 1
+   Output: (ANY ('foo'::text = (hashed SubPlan 1).$0))
+   SubPlan 1 (returns $0)
      ->  Append
            ->  Result
                  Output: 'bar'::name
@@ -864,11 +915,11 @@ select 'foo'::text in (select 'bar'::nam
 --
 explain (verbose, costs off)
 select row(row(row(1))) = any (select row(row(1)));
-                QUERY PLAN                 
--------------------------------------------
+                      QUERY PLAN                      
+------------------------------------------------------
  Result
-   Output: (SubPlan 1)
-   SubPlan 1
+   Output: (ANY ('("(1)")'::record = (SubPlan 1).$0))
+   SubPlan 1 (returns $0)
      ->  Materialize
            Output: '("(1)")'::record
            ->  Result
@@ -907,11 +958,11 @@ language sql as 'select $1::text = $2';
 create operator = (procedure=bogus_int8_text_eq, leftarg=int8, rightarg=text);
 explain (costs off)
 select * from int8_tbl where q1 in (select c1 from inner_text);
-           QUERY PLAN           
---------------------------------
+                      QUERY PLAN                      
+------------------------------------------------------
  Seq Scan on int8_tbl
-   Filter: (hashed SubPlan 1)
-   SubPlan 1
+   Filter: (ANY ((q1)::text = (hashed SubPlan 1).$0))
+   SubPlan 1 (returns $0)
      ->  Seq Scan on inner_text
 (4 rows)
 
@@ -928,11 +979,11 @@ create or replace function bogus_int8_te
 language sql as 'select $1::text = $2 and $1::text = $2';
 explain (costs off)
 select * from int8_tbl where q1 in (select c1 from inner_text);
-           QUERY PLAN           
---------------------------------
+                                           QUERY PLAN                                            
+-------------------------------------------------------------------------------------------------
  Seq Scan on int8_tbl
-   Filter: (hashed SubPlan 1)
-   SubPlan 1
+   Filter: (ANY (((q1)::text = (hashed SubPlan 1).$0) AND ((q1)::text = (hashed SubPlan 1).$0)))
+   SubPlan 1 (returns $0)
      ->  Seq Scan on inner_text
 (4 rows)
 
@@ -949,11 +1000,11 @@ create or replace function bogus_int8_te
 language sql as 'select $2 = $1::text';
 explain (costs off)
 select * from int8_tbl where q1 in (select c1 from inner_text);
-              QUERY PLAN              
---------------------------------------
+                  QUERY PLAN                   
+-----------------------------------------------
  Seq Scan on int8_tbl
-   Filter: (SubPlan 1)
-   SubPlan 1
+   Filter: (ANY ((SubPlan 1).$0 = (q1)::text))
+   SubPlan 1 (returns $0)
      ->  Materialize
            ->  Seq Scan on inner_text
 (5 rows)
@@ -972,12 +1023,12 @@ rollback;  -- to get rid of the bogus op
 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: ((hashed SubPlan 2) OR (ten < 0))
-         SubPlan 2
+         Filter: ((ANY (unique2 = (hashed SubPlan 2).$1)) OR (ten < 0))
+         SubPlan 2 (returns $1)
            ->  Index Only Scan using tenk1_unique1 on tenk1 k
 (5 rows)
 
@@ -997,7 +1048,7 @@ where (exists(select 1 from tenk1 k wher
  Aggregate
    ->  Bitmap Heap Scan on tenk1 t
          Recheck Cond: (thousand = 1)
-         Filter: ((SubPlan 1) OR (ten < 0))
+         Filter: (EXISTS(SubPlan 1) OR (ten < 0))
          ->  Bitmap Index Scan on tenk1_thous_tenthous
                Index Cond: (thousand = 1)
          SubPlan 1
@@ -1022,11 +1073,11 @@ 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: ((SubPlan 1) OR (c3 < 0))
+         Filter: (EXISTS(SubPlan 1) OR (c3 < 0))
          SubPlan 1
            ->  Append
                  ->  Seq Scan on exists_tbl_null t2_1
@@ -1034,8 +1085,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: ((hashed SubPlan 2) OR (c3 < 0))
-         SubPlan 2
+         Filter: ((ANY (c1 = (hashed SubPlan 2).$1)) OR (c3 < 0))
+         SubPlan 2 (returns $1)
            ->  Append
                  ->  Seq Scan on exists_tbl_null t2_4
                  ->  Seq Scan on exists_tbl_def t2_5
@@ -1071,10 +1122,10 @@ where a.thousand = b.thousand
 explain (verbose, costs off)
   select x, x from
     (select (select now()) as x from (values(1),(2)) v(y)) ss;
-        QUERY PLAN         
----------------------------
+                 QUERY PLAN                 
+--------------------------------------------
  Values Scan on "*VALUES*"
-   Output: $0, $1
+   Output: (InitPlan 1).$0, (InitPlan 2).$1
    InitPlan 1 (returns $0)
      ->  Result
            Output: now()
@@ -1091,7 +1142,7 @@ explain (verbose, costs off)
  Subquery Scan on ss
    Output: ss.x, ss.x
    ->  Values Scan on "*VALUES*"
-         Output: $0
+         Output: (InitPlan 1).$0
          InitPlan 1 (returns $0)
            ->  Result
                  Output: random()
@@ -1143,16 +1194,16 @@ where o.ten = 0;
                                                                                          QUERY PLAN                                                                                          
 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Aggregate
-   Output: sum((((hashed SubPlan 1)))::integer)
+   Output: sum((((ANY (i.ten = (hashed SubPlan 1).$1))))::integer)
    ->  Nested Loop
-         Output: ((hashed SubPlan 1))
+         Output: ((ANY (i.ten = (hashed SubPlan 1).$1)))
          ->  Seq Scan on public.onek o
                Output: o.unique1, o.unique2, o.two, o.four, o.ten, o.twenty, o.hundred, o.thousand, o.twothousand, o.fivethous, o.tenthous, o.odd, o.even, o.stringu1, o.stringu2, o.string4
                Filter: (o.ten = 0)
          ->  Index Scan using onek_unique1 on public.onek i
-               Output: (hashed SubPlan 1), random()
+               Output: (ANY (i.ten = (hashed SubPlan 1).$1)), random()
                Index Cond: (i.unique1 = o.unique1)
-               SubPlan 1
+               SubPlan 1 (returns $1)
                  ->  Seq Scan on public.int4_tbl
                        Output: int4_tbl.f1
                        Filter: (int4_tbl.f1 <= o.hundred)
@@ -1319,7 +1370,7 @@ select * from
 ----------------------------------------
  Values Scan on "*VALUES*"
    Output: "*VALUES*".column1
-   SubPlan 1
+   SubPlan 1 (returns $0)
      ->  Values Scan on "*VALUES*_1"
            Output: "*VALUES*_1".column1
 (5 rows)
@@ -1346,12 +1397,12 @@ select * from int4_tbl where
 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Nested Loop Semi Join
    Output: int4_tbl.f1
-   Join Filter: (CASE WHEN (hashed SubPlan 1) THEN int4_tbl.f1 ELSE NULL::integer END = b.ten)
+   Join Filter: (CASE WHEN (ANY (int4_tbl.f1 = (hashed SubPlan 1).$0)) THEN int4_tbl.f1 ELSE NULL::integer END = b.ten)
    ->  Seq Scan on public.int4_tbl
          Output: int4_tbl.f1
    ->  Seq Scan on public.tenk1 b
          Output: b.unique1, b.unique2, b.two, b.four, b.ten, b.twenty, b.hundred, b.thousand, b.twothousand, b.fivethous, b.tenthous, b.odd, b.even, b.stringu1, b.stringu2, b.string4
-   SubPlan 1
+   SubPlan 1 (returns $0)
      ->  Index Only Scan using tenk1_unique1 on public.tenk1 a
            Output: a.unique1
 (10 rows)
@@ -1945,14 +1996,14 @@ select * from tenk1 A where exists
 (select 1 from tenk2 B
 where A.hundred in (select C.hundred FROM tenk2 C
 WHERE c.odd = b.odd));
-           QUERY PLAN            
----------------------------------
+                    QUERY PLAN                     
+---------------------------------------------------
  Nested Loop Semi Join
-   Join Filter: (SubPlan 1)
+   Join Filter: (ANY (a.hundred = (SubPlan 1).$1))
    ->  Seq Scan on tenk1 a
    ->  Materialize
          ->  Seq Scan on tenk2 b
-   SubPlan 1
+   SubPlan 1 (returns $1)
      ->  Seq Scan on tenk2 c
            Filter: (odd = b.odd)
 (8 rows)
@@ -1962,14 +2013,14 @@ WHERE c.odd = b.odd));
 explain (costs off)
 SELECT * FROM tenk1 A LEFT JOIN tenk2 B
 ON A.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = b.odd);
-           QUERY PLAN            
----------------------------------
+                    QUERY PLAN                     
+---------------------------------------------------
  Nested Loop Left Join
-   Join Filter: (SubPlan 1)
+   Join Filter: (ANY (a.hundred = (SubPlan 1).$1))
    ->  Seq Scan on tenk1 a
    ->  Materialize
          ->  Seq Scan on tenk2 b
-   SubPlan 1
+   SubPlan 1 (returns $1)
      ->  Seq Scan on tenk2 c
            Filter: (odd = b.odd)
 (8 rows)
@@ -1979,14 +2030,14 @@ ON A.hundred in (SELECT c.hundred FROM t
 explain (costs off)
 SELECT * FROM tenk1 A LEFT JOIN tenk2 B
 ON B.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = a.odd);
-           QUERY PLAN            
----------------------------------
+                    QUERY PLAN                     
+---------------------------------------------------
  Nested Loop Left Join
-   Join Filter: (SubPlan 1)
+   Join Filter: (ANY (b.hundred = (SubPlan 1).$1))
    ->  Seq Scan on tenk1 a
    ->  Materialize
          ->  Seq Scan on tenk2 b
-   SubPlan 1
+   SubPlan 1 (returns $1)
      ->  Seq Scan on tenk2 c
            Filter: (odd = a.odd)
 (8 rows)
diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out
new file mode 100644
index 794cf9c..f25c042
--- a/src/test/regress/expected/updatable_views.out
+++ b/src/test/regress/expected/updatable_views.out
@@ -3084,8 +3084,8 @@ SELECT * FROM v1 WHERE a=8;
 
@@ -3097,7 +3097,7 @@ UPDATE v1 SET a=100 WHERE snoop(a) AND l
@@ -3107,15 +3107,15 @@ UPDATE v1 SET a=100 WHERE snoop(a) AND l
 
 UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a < 7 AND a != 6;
@@ -3131,8 +3131,8 @@ SELECT * FROM t1 WHERE a=100; -- Nothing
 
@@ -3144,7 +3144,7 @@ UPDATE v1 SET a=a+1 WHERE snoop(a) AND l
@@ -3154,15 +3154,15 @@ UPDATE v1 SET a=a+1 WHERE snoop(a) AND l
 
new file mode 100644
index 2f3601a..7c42ebc