Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rmcXm-008xKF-0i for pgsql-hackers@arkaria.postgresql.org; Tue, 19 Mar 2024 16:42:30 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rmcXk-00GzxF-Kh for pgsql-hackers@arkaria.postgresql.org; Tue, 19 Mar 2024 16:42:29 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rmcXj-00GzwV-Pd for pgsql-hackers@lists.postgresql.org; Tue, 19 Mar 2024 16:42:28 +0000 Received: from sss.pgh.pa.us ([68.162.161.243]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rmcXe-005LqD-5G for pgsql-hackers@lists.postgresql.org; Tue, 19 Mar 2024 16:42:27 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 42JGgKQJ3883483; Tue, 19 Mar 2024 12:42:20 -0400 From: Tom Lane To: Dean Rasheed cc: Aleksander Alekseev , pgsql-hackers@lists.postgresql.org, Chantal Keller Subject: Re: Improving EXPLAIN's display of SubPlan nodes In-reply-to: <3717916.1710806600@sss.pgh.pa.us> References: <2838538.1705692747@sss.pgh.pa.us> <3791430.1705943243@sss.pgh.pa.us> <3831858.1705959108@sss.pgh.pa.us> <3511300.1708112330@sss.pgh.pa.us> <2506269.1710514705@sss.pgh.pa.us> <2982030.1710609907@sss.pgh.pa.us> <3127158.1710685722@sss.pgh.pa.us> <3399753.1710704359@sss.pgh.pa.us> <3691942.1710796200@sss.pgh.pa.us> <3712528.1710803943@sss.pgh.pa.us> <3717916.1710806600@sss.pgh.pa.us> Comments: In-reply-to Tom Lane message dated "Mon, 18 Mar 2024 20:03:20 -0400" MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0" Content-ID: <3883463.1710866522.0@sss.pgh.pa.us> Date: Tue, 19 Mar 2024 12:42:20 -0400 Message-ID: <3883482.1710866540@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------- =_aaaaaaaaaa0 Content-Type: text/plain; charset="us-ascii" Content-ID: <3883463.1710866522.1@sss.pgh.pa.us> I wrote: > I won't update the patch right now, but "(rescan SubPlan N)" > seems like a winner to me. Here's a hopefully-final version that makes that adjustment and tweaks a couple of comments. regards, tom lane ------- =_aaaaaaaaaa0 Content-Type: text/x-diff; name="v6-0001-Improve-EXPLAIN-s-display-of-SubPlan-nodes.patch"; charset="us-ascii" Content-ID: <3883463.1710866522.2@sss.pgh.pa.us> Content-Description: v6-0001-Improve-EXPLAIN-s-display-of-SubPlan-nodes.patch Content-Transfer-Encoding: quoted-printable diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/post= gres_fdw/expected/postgres_fdw.out index 58a603ac56..acbbf3b56c 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -3062,10 +3062,10 @@ select exists(select 1 from pg_enum), sum(c1) from= ft1; QUERY PLAN = -------------------------------------------------- Foreign Scan - Output: $0, (sum(ft1.c1)) + Output: (InitPlan 1).col1, (sum(ft1.c1)) Relations: Aggregate on (public.ft1) Remote SQL: SELECT sum("C 1") FROM "S 1"."T 1" - InitPlan 1 (returns $0) + InitPlan 1 -> Seq Scan on pg_catalog.pg_enum (6 rows) = @@ -3080,8 +3080,8 @@ select exists(select 1 from pg_enum), sum(c1) from f= t1 group by 1; QUERY PLAN = --------------------------------------------------- GroupAggregate - Output: $0, sum(ft1.c1) - InitPlan 1 (returns $0) + Output: (InitPlan 1).col1, sum(ft1.c1) + InitPlan 1 -> Seq Scan on pg_catalog.pg_enum -> Foreign Scan on public.ft1 Output: ft1.c1 @@ -3305,10 +3305,10 @@ select sum(c1) filter (where (c1 / c1) * random() = <=3D 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)) fro= m ft1; - QUERY PLAN = -------------------------------------------------------------------- + QUERY PLAN = = +-------------------------------------------------------------------------= ------ Aggregate - Output: sum(ft1.c2) FILTER (WHERE (hashed SubPlan 1)) + Output: sum(ft1.c2) FILTER (WHERE (ANY (ft1.c2 =3D (hashed SubPlan 1).= col1))) -> Foreign Scan on public.ft1 Output: ft1.c2 Remote SQL: SELECT c2 FROM "S 1"."T 1" @@ -6171,9 +6171,9 @@ UPDATE ft2 AS target SET (c2, c7) =3D ( Update on public.ft2 target Remote SQL: UPDATE "S 1"."T 1" SET c2 =3D $2, c7 =3D $3 WHERE ctid =3D= $1 -> Foreign Scan on public.ft2 target - Output: $1, $2, (SubPlan 1 (returns $1,$2)), target.ctid, target= .* + Output: (SubPlan 1).col1, (SubPlan 1).col2, (rescan SubPlan 1), = target.ctid, target.* Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8, ctid FROM = "S 1"."T 1" WHERE (("C 1" > 1100)) FOR UPDATE - SubPlan 1 (returns $1,$2) + SubPlan 1 -> Foreign Scan on public.ft2 src Output: (src.c2 * 10), src.c7 Remote SQL: SELECT c2, c7 FROM "S 1"."T 1" WHERE (($1::i= nteger =3D "C 1")) @@ -11685,9 +11685,9 @@ SELECT * FROM local_tbl t1 LEFT JOIN (SELECT *, (S= ELECT count(*) FROM async_pt W QUERY PLAN = = -------------------------------------------------------------------------= --------------- Nested Loop Left Join - Output: t1.a, t1.b, t1.c, async_pt.a, async_pt.b, async_pt.c, ($0) + Output: t1.a, t1.b, t1.c, async_pt.a, async_pt.b, async_pt.c, ((InitPl= an 1).col1) Join Filter: (t1.a =3D async_pt.a) - InitPlan 1 (returns $0) + InitPlan 1 -> Aggregate Output: count(*) -> Append @@ -11699,10 +11699,10 @@ SELECT * FROM local_tbl t1 LEFT JOIN (SELECT *, = (SELECT count(*) FROM async_pt W Output: t1.a, t1.b, t1.c -> Append -> Async Foreign Scan on public.async_p1 async_pt_1 - Output: async_pt_1.a, async_pt_1.b, async_pt_1.c, $0 + Output: async_pt_1.a, async_pt_1.b, async_pt_1.c, (InitPla= n 1).col1 Remote SQL: SELECT a, b, c FROM public.base_tbl1 WHERE ((a= < 3000)) -> Async Foreign Scan on public.async_p2 async_pt_2 - Output: async_pt_2.a, async_pt_2.b, async_pt_2.c, $0 + Output: async_pt_2.a, async_pt_2.b, async_pt_2.c, (InitPla= n 1).col1 Remote SQL: SELECT a, b, c FROM public.base_tbl2 WHERE ((a= < 3000)) (20 rows) = @@ -11713,7 +11713,7 @@ SELECT * FROM local_tbl t1 LEFT JOIN (SELECT *, (S= ELECT count(*) FROM async_pt W Nested Loop Left Join (actual rows=3D1 loops=3D1) Join Filter: (t1.a =3D async_pt.a) Rows Removed by Join Filter: 399 - InitPlan 1 (returns $0) + InitPlan 1 -> Aggregate (actual rows=3D1 loops=3D1) -> Append (actual rows=3D400 loops=3D1) -> Async Foreign Scan on async_p1 async_pt_4 (actual ro= ws=3D200 loops=3D1) @@ -11936,11 +11936,11 @@ CREATE FOREIGN TABLE foreign_tbl2 () INHERITS (f= oreign_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) + Filter: (ANY ((base_tbl.a =3D (SubPlan 1).col1) AND ((random() > '0'::= double precision) =3D (SubPlan 1).col2))) SubPlan 1 -> Result Output: base_tbl.a, (random() > '0'::double precision) diff --git a/doc/src/sgml/perform.sgml b/doc/src/sgml/perform.sgml index 90822b3f4c..4b831a6206 100644 --- a/doc/src/sgml/perform.sgml +++ b/doc/src/sgml/perform.sgml @@ -573,8 +573,106 @@ WHERE t1.unique1 < 100 AND t1.unique2 =3D t2.uniq= ue2; which shows that the planner thinks that sorting onek by index-scanning is about 12% more expensive than sequential-scan-and-s= ort. Of course, the next question is whether it's right about that. - We can investigate that using EXPLAIN ANALYZE, as = discussed - below. + We can investigate that using EXPLAIN ANALYZE, as + discussed below. + + + + + subplan + + Some query plans involve subplans, which arise + from sub-SELECTs in the original query. Such + queries can sometimes be transformed into ordinary join plans, but + when they cannot be, we get plans like: + + +EXPLAIN VERBOSE SELECT unique1 +FROM tenk1 t +WHERE t.ten < ALL (SELECT o.ten FROM onek o WHERE o.four =3D t.four); + + QUERY PLAN +-------------------------------------------------------------------&zwsp;= ------ + Seq Scan on public.tenk1 t (cost=3D0.00..586095.00 rows=3D5000 width=3D= 4) + Output: t.unique1 + Filter: (ALL (t.ten < (SubPlan 1).col1)) + SubPlan 1 + -> Seq Scan on public.onek o (cost=3D0.00..116.50 rows=3D250 wi= dth=3D4) + Output: o.ten + Filter: (o.four =3D t.four) + + + This rather artificial example serves to illustrate a couple of + points: values from the outer plan level can be passed down into a + subplan (here, t.four is passed down) and the + results of the sub-select are available to the outer plan. Those + result values are shown by EXPLAIN with notations + like + (subplan_name).colN<= /replaceable>, + which refers to the N'th output column of + the sub-SELECT. + + + + + subplan + hashed + + In the example above, the ALL operator runs the + subplan again for each row of the outer query (which accounts for the + high estimated cost). Some queries can use a hashed + subplan to avoid that: + + +EXPLAIN SELECT * +FROM tenk1 t +WHERE t.unique1 NOT IN (SELECT o.unique1 FROM onek o); + + QUERY PLAN +-------------------------------------------------------------------&zwsp;= ------------------------- + Seq Scan on tenk1 t (cost=3D61.77..531.77 rows=3D5000 width=3D244) + Filter: (NOT (ANY (unique1 =3D (hashed SubPlan 1).col1))) + SubPlan 1 + -> Index Only Scan using onek_unique1 on onek o (cost=3D0.28..5= 9.27 rows=3D1000 width=3D4) +(4 rows) + + + Here, the subplan is run a single time and its output is loaded into + an in-memory hash table, which is then probed by the + outer ANY operator. This requires that the + sub-SELECT not reference any variables of the oute= r + query, and that the ANY's comparison operator be + amenable to hashing. + + + + + initplan + + If, in addition to not referencing any variables of the outer query, + the sub-SELECT cannot return more than one row, + it may instead be implemented as an initplan: + + +EXPLAIN VERBOSE SELECT unique1 +FROM tenk1 t1 WHERE t1.ten =3D (SELECT (random() * 10)::integer); + + QUERY PLAN +------------------------------------------------------------&zwsp;-------= - + Seq Scan on public.tenk1 t1 (cost=3D0.02..470.02 rows=3D1000 width=3D4) + Output: t1.unique1 + Filter: (t1.ten =3D (InitPlan 1).col1) + InitPlan 1 + -> Result (cost=3D0.00..0.02 rows=3D1 width=3D4) + Output: ((random() * '10'::double precision))::integer + + + An initplan is run only once per execution of the outer plan, and its + results are saved for re-use in later rows of the outer plan. So in + this example random() is evaluated only once and + all the values of t1.ten are compared to the same + randomly-chosen integer. That's quite different from what would + happen without the sub-SELECT construct. = diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain= .c index a9d5056af4..926d70afaf 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -116,7 +116,6 @@ static void show_tidbitmap_info(BitmapHeapScanState *p= lanstate, static void show_instrumentation_count(const char *qlabel, int which, PlanState *planstate, ExplainState *es); static void show_foreignscan_info(ForeignScanState *fsstate, ExplainState= *es); -static void show_eval_params(Bitmapset *bms_params, ExplainState *es); static const char *explain_get_index_name(Oid indexId); static bool peek_buffer_usage(ExplainState *es, const BufferUsage *usage)= ; static void show_buffer_usage(ExplainState *es, const BufferUsage *usage)= ; @@ -1914,10 +1913,6 @@ ExplainNode(PlanState *planstate, List *ancestors, ExplainPropertyInteger("Workers Planned", NULL, gather->num_workers, es); = - /* Show params evaluated at gather node */ - if (gather->initParam) - show_eval_params(gather->initParam, es); - if (es->analyze) { int nworkers; @@ -1942,10 +1937,6 @@ ExplainNode(PlanState *planstate, List *ancestors, ExplainPropertyInteger("Workers Planned", NULL, gm->num_workers, es); = - /* Show params evaluated at gather-merge node */ - if (gm->initParam) - show_eval_params(gm->initParam, es); - if (es->analyze) { int nworkers; @@ -3550,29 +3541,6 @@ show_foreignscan_info(ForeignScanState *fsstate, Ex= plainState *es) } } = -/* - * Show initplan params evaluated at Gather or Gather Merge node. - */ -static void -show_eval_params(Bitmapset *bms_params, ExplainState *es) -{ - int paramid =3D -1; - List *params =3D NIL; - - Assert(bms_params); - - while ((paramid =3D bms_next_member(bms_params, paramid)) >=3D 0) - { - char param[32]; - - snprintf(param, sizeof(param), "$%d", paramid); - params =3D lappend(params, pstrdup(param)); - } - - if (params) - ExplainPropertyList("Params Evaluated", params, es); -} - /* * Fetch the name of an index in an EXPLAIN * diff --git a/src/backend/optimizer/plan/subselect.c b/src/backend/optimize= r/plan/subselect.c index b9e0c960bd..d6954a7e86 100644 --- a/src/backend/optimizer/plan/subselect.c +++ b/src/backend/optimizer/plan/subselect.c @@ -560,22 +560,9 @@ build_subplan(PlannerInfo *root, Plan *plan, PlannerI= nfo *subroot, splan->plan_id); = /* Label the subplan for EXPLAIN purposes */ - splan->plan_name =3D palloc(32 + 12 * list_length(splan->setParam)); - sprintf(splan->plan_name, "%s %d", - isInitPlan ? "InitPlan" : "SubPlan", - splan->plan_id); - if (splan->setParam) - { - char *ptr =3D splan->plan_name + strlen(splan->plan_name); - - ptr +=3D sprintf(ptr, " (returns "); - foreach(lc, splan->setParam) - { - ptr +=3D sprintf(ptr, "$%d%s", - lfirst_int(lc), - lnext(splan->setParam, lc) ? "," : ")"); - } - } + splan->plan_name =3D psprintf("%s %d", + isInitPlan ? "InitPlan" : "SubPlan", + splan->plan_id); = /* Lastly, fill in the cost estimates for use later */ cost_subplan(root, splan, plan); @@ -3047,8 +3034,7 @@ SS_make_initplan_from_plan(PlannerInfo *root, node =3D makeNode(SubPlan); node->subLinkType =3D EXPR_SUBLINK; node->plan_id =3D list_length(root->glob->subplans); - node->plan_name =3D psprintf("InitPlan %d (returns $%d)", - node->plan_id, prm->paramid); + node->plan_name =3D psprintf("InitPlan %d", node->plan_id); get_first_col_type(plan, &node->firstColType, &node->firstColTypmod, &node->firstColCollation); node->parallel_safe =3D plan->parallel_safe; diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/rul= eutils.c index f2893d4086..752757be11 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -437,6 +437,10 @@ static void resolve_special_varno(Node *node, deparse= _context *context, rsv_callback callback, void *callback_arg); static Node *find_param_referent(Param *param, deparse_context *context, deparse_namespace **dpns_p, ListCell **ancestor_cell_p); +static SubPlan *find_param_generator(Param *param, deparse_context *conte= xt, + int *column_p); +static SubPlan *find_param_generator_initplan(Param *param, Plan *plan, + int *column_p); static void get_parameter(Param *param, deparse_context *context); static const char *get_simple_binary_op_name(OpExpr *expr); static bool isSimpleNode(Node *node, Node *parentNode, int prettyFlags); @@ -8134,6 +8138,128 @@ find_param_referent(Param *param, deparse_context = *context, return NULL; } = +/* + * Try to find a subplan/initplan that emits the value for a PARAM_EXEC P= aram. + * + * If successful, return the generating subplan/initplan and set *column_= p + * to the subplan's 0-based output column number. + * Otherwise, return NULL. + */ +static SubPlan * +find_param_generator(Param *param, deparse_context *context, int *column_= p) +{ + /* Initialize output parameter to prevent compiler warnings */ + *column_p =3D 0; + + /* + * If it's a PARAM_EXEC parameter, search the current plan node as well = as + * ancestor nodes looking for a subplan or initplan that emits the value + * for the Param. It could appear in the setParams of an initplan or + * MULTIEXPR_SUBLINK subplan, or in the paramIds of an ancestral SubPlan= . + */ + if (param->paramkind =3D=3D PARAM_EXEC) + { + SubPlan *result; + deparse_namespace *dpns; + ListCell *lc; + + dpns =3D (deparse_namespace *) linitial(context->namespaces); + + /* First check the innermost plan node's initplans */ + result =3D find_param_generator_initplan(param, dpns->plan, column_p); + if (result) + return result; + + /* + * The plan's targetlist might contain MULTIEXPR_SUBLINK SubPlans, + * which can be referenced by Params elsewhere in the targetlist. + * (Such Params should always be in the same targetlist, so there's no + * need to do this work at upper plan nodes.) + */ + foreach_node(TargetEntry, tle, dpns->plan->targetlist) + { + if (tle->expr && IsA(tle->expr, SubPlan)) + { + SubPlan *subplan =3D (SubPlan *) tle->expr; + + if (subplan->subLinkType =3D=3D MULTIEXPR_SUBLINK) + { + foreach_int(paramid, subplan->setParam) + { + if (paramid =3D=3D param->paramid) + { + /* Found a match, so return it. */ + *column_p =3D foreach_current_index(paramid); + return subplan; + } + } + } + } + } + + /* No luck, so check the ancestor nodes */ + foreach(lc, dpns->ancestors) + { + Node *ancestor =3D (Node *) lfirst(lc); + + /* + * If ancestor is a SubPlan, check the paramIds it provides. + */ + if (IsA(ancestor, SubPlan)) + { + SubPlan *subplan =3D (SubPlan *) ancestor; + + foreach_int(paramid, subplan->paramIds) + { + if (paramid =3D=3D param->paramid) + { + /* Found a match, so return it. */ + *column_p =3D foreach_current_index(paramid); + return subplan; + } + } + + /* SubPlan isn't a kind of Plan, so skip the rest */ + continue; + } + + /* + * Otherwise, it's some kind of Plan node, so check its initplans. + */ + result =3D find_param_generator_initplan(param, (Plan *) ancestor, + column_p); + if (result) + return result; + + /* No luck, crawl up to next ancestor */ + } + } + + /* No generator found */ + return NULL; +} + +/* + * Subroutine for find_param_generator: search one Plan node's initplans + */ +static SubPlan * +find_param_generator_initplan(Param *param, Plan *plan, int *column_p) +{ + foreach_node(SubPlan, subplan, plan->initPlan) + { + foreach_int(paramid, subplan->setParam) + { + if (paramid =3D=3D param->paramid) + { + /* Found a match, so return it. */ + *column_p =3D foreach_current_index(paramid); + return subplan; + } + } + } + return NULL; +} + /* * Display a Param appropriately. */ @@ -8143,12 +8269,13 @@ get_parameter(Param *param, deparse_context *conte= xt) Node *expr; deparse_namespace *dpns; ListCell *ancestor_cell; + SubPlan *subplan; + int column; = /* * If it's a PARAM_EXEC parameter, try to locate the expression from whi= ch - * the parameter was computed. Note that failing to find a referent isn= 't - * an error, since the Param might well be a subplan output rather than = an - * input. + * the parameter was computed. This stanza handles only cases in which + * the Param represents an input to the subplan we are currently in. */ expr =3D find_param_referent(param, context, &dpns, &ancestor_cell); if (expr) @@ -8192,6 +8319,24 @@ get_parameter(Param *param, deparse_context *contex= t) return; } = + /* + * Alternatively, maybe it's a subplan output, which we print as a + * reference to the subplan. (We could drill down into the subplan and + * print the relevant targetlist expression, but that has been deemed to= o + * confusing since it would violate normal SQL scope rules. Also, we're + * relying on this reference to show that the testexpr containing the + * Param has anything to do with that subplan at all.) + */ + subplan =3D find_param_generator(param, context, &column); + if (subplan) + { + appendStringInfo(context->buf, "(%s%s).col%d", + subplan->useHashTable ? "hashed " : "", + subplan->plan_name, column + 1); + + return; + } + /* * If it's an external parameter, see if the outermost namespace provide= s * function argument names. @@ -8240,7 +8385,12 @@ get_parameter(Param *param, deparse_context *contex= t) = /* * Not PARAM_EXEC, or couldn't find referent: just print $N. + * + * It's a bug if we get here for anything except PARAM_EXTERN Params, bu= t + * in production builds printing $N seems more useful than failing. */ + Assert(param->paramkind =3D=3D PARAM_EXTERN); + appendStringInfo(context->buf, "$%d", param->paramid); } = @@ -8881,12 +9031,79 @@ 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 (if any), and + * we also note whether the subplan will be hashed. */ - if (subplan->useHashTable) - appendStringInfo(buf, "(hashed %s)", subplan->plan_name); + switch (subplan->subLinkType) + { + case EXISTS_SUBLINK: + appendStringInfoString(buf, "EXISTS("); + Assert(subplan->testexpr =3D=3D NULL); + break; + case ALL_SUBLINK: + appendStringInfoString(buf, "(ALL "); + Assert(subplan->testexpr !=3D NULL); + break; + case ANY_SUBLINK: + appendStringInfoString(buf, "(ANY "); + Assert(subplan->testexpr !=3D NULL); + break; + case ROWCOMPARE_SUBLINK: + /* Parenthesizing the testexpr seems sufficient */ + appendStringInfoChar(buf, '('); + Assert(subplan->testexpr !=3D NULL); + break; + case EXPR_SUBLINK: + /* No need to decorate these subplan references */ + appendStringInfoChar(buf, '('); + Assert(subplan->testexpr =3D=3D NULL); + break; + case MULTIEXPR_SUBLINK: + /* MULTIEXPR isn't executed in the normal way */ + appendStringInfoString(buf, "(rescan "); + Assert(subplan->testexpr =3D=3D NULL); + break; + case ARRAY_SUBLINK: + appendStringInfoString(buf, "ARRAY("); + Assert(subplan->testexpr =3D=3D NULL); + break; + case CTE_SUBLINK: + /* This case is unreachable within expressions */ + appendStringInfoString(buf, "CTE("); + Assert(subplan->testexpr =3D=3D NULL); + break; + } + + if (subplan->testexpr !=3D NULL) + { + deparse_namespace *dpns; + + /* + * Push SubPlan into ancestors list while deparsing + * testexpr, so that we can handle PARAM_EXEC references + * to the SubPlan's paramIds. (This makes it look like + * the SubPlan is an "ancestor" of the current plan node, + * which is a little weird, but it does no harm.) In this + * path, we don't need to mention the SubPlan explicitly, + * because the referencing Params will show its existence. + */ + dpns =3D (deparse_namespace *) linitial(context->namespaces); + dpns->ancestors =3D lcons(subplan, dpns->ancestors); + + get_rule_expr(subplan->testexpr, context, showimplicit); + appendStringInfoChar(buf, ')'); + + dpns->ancestors =3D list_delete_first(dpns->ancestors); + } else - appendStringInfo(buf, "(%s)", subplan->plan_name); + { + /* No referencing Params, so show the SubPlan's 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/e= xpected/aggregates.out index f86cf8d258..d54a255e58 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 @@ -915,7 +915,7 @@ explain (costs off) QUERY PLAN = ------------------------------------------------------------ Result - InitPlan 1 (returns $0) + InitPlan 1 -> Limit -> Index Only Scan using tenk1_unique1 on tenk1 Index Cond: (unique1 IS NOT NULL) @@ -932,7 +932,7 @@ explain (costs off) QUERY PLAN = --------------------------------------------------------------------- Result - InitPlan 1 (returns $0) + InitPlan 1 -> Limit -> Index Only Scan Backward using tenk1_unique1 on tenk1 Index Cond: (unique1 IS NOT NULL) @@ -949,7 +949,7 @@ explain (costs off) QUERY PLAN = ------------------------------------------------------------------------ Result - InitPlan 1 (returns $0) + InitPlan 1 -> Limit -> Index Only Scan Backward using tenk1_unique1 on tenk1 Index Cond: ((unique1 IS NOT NULL) AND (unique1 < 42)) @@ -966,7 +966,7 @@ explain (costs off) QUERY PLAN = ------------------------------------------------------------------------ Result - InitPlan 1 (returns $0) + InitPlan 1 -> Limit -> Index Only Scan Backward using tenk1_unique1 on tenk1 Index Cond: ((unique1 IS NOT NULL) AND (unique1 > 42)) @@ -989,7 +989,7 @@ explain (costs off) QUERY PLAN = = -------------------------------------------------------------------------= -- Result - InitPlan 1 (returns $0) + InitPlan 1 -> Limit -> Index Only Scan Backward using tenk1_unique1 on tenk1 Index Cond: ((unique1 IS NOT NULL) AND (unique1 > 42000)= ) @@ -1008,7 +1008,7 @@ explain (costs off) QUERY PLAN = = -------------------------------------------------------------------------= --- Result - InitPlan 1 (returns $0) + InitPlan 1 -> Limit -> Index Only Scan Backward using tenk1_thous_tenthous on ten= k1 Index Cond: ((thousand =3D 33) AND (tenthous IS NOT NULL= )) @@ -1025,7 +1025,7 @@ explain (costs off) QUERY PLAN = = -------------------------------------------------------------------------= - Result - InitPlan 1 (returns $0) + InitPlan 1 -> Limit -> Index Only Scan using tenk1_thous_tenthous on tenk1 Index Cond: ((thousand =3D 33) AND (tenthous IS NOT NULL= )) @@ -1046,7 +1046,7 @@ explain (costs off) Seq Scan on int4_tbl SubPlan 2 -> Result - InitPlan 1 (returns $1) + InitPlan 1 -> Limit -> Index Only Scan using tenk1_unique1 on tenk1 Index Cond: ((unique1 IS NOT NULL) AND (unique1 = > int4_tbl.f1)) @@ -1069,8 +1069,8 @@ explain (costs off) QUERY PLAN = --------------------------------------------------------------------- HashAggregate - Group Key: $0 - InitPlan 1 (returns $0) + Group Key: (InitPlan 1).col1 + InitPlan 1 -> Limit -> Index Only Scan Backward using tenk1_unique2 on tenk1 Index Cond: (unique2 IS NOT NULL) @@ -1088,8 +1088,8 @@ explain (costs off) QUERY PLAN = --------------------------------------------------------------------- Sort - Sort Key: ($0) - InitPlan 1 (returns $0) + Sort Key: ((InitPlan 1).col1) + InitPlan 1 -> Limit -> Index Only Scan Backward using tenk1_unique2 on tenk1 Index Cond: (unique2 IS NOT NULL) @@ -1107,8 +1107,8 @@ explain (costs off) QUERY PLAN = --------------------------------------------------------------------- Sort - Sort Key: ($0) - InitPlan 1 (returns $0) + Sort Key: ((InitPlan 1).col1) + InitPlan 1 -> Limit -> Index Only Scan Backward using tenk1_unique2 on tenk1 Index Cond: (unique2 IS NOT NULL) @@ -1126,8 +1126,8 @@ explain (costs off) QUERY PLAN = --------------------------------------------------------------------- Sort - Sort Key: (($0 + 1)) - InitPlan 1 (returns $0) + Sort Key: (((InitPlan 1).col1 + 1)) + InitPlan 1 -> Limit -> Index Only Scan Backward using tenk1_unique2 on tenk1 Index Cond: (unique2 IS NOT NULL) @@ -1146,7 +1146,7 @@ explain (costs off) --------------------------------------------------------------------- Sort Sort Key: (generate_series(1, 3)) DESC - InitPlan 1 (returns $0) + InitPlan 1 -> Limit -> Index Only Scan Backward using tenk1_unique2 on tenk1 Index Cond: (unique2 IS NOT NULL) @@ -1168,7 +1168,7 @@ explain (costs off) QUERY PLAN = ---------------------------------------------------- Result - InitPlan 1 (returns $0) + InitPlan 1 -> Limit -> Result One-Time Filter: (100 IS NOT NULL) @@ -1199,7 +1199,7 @@ explain (costs off) QUERY PLAN = = -------------------------------------------------------------------------= -------------------- Result - InitPlan 1 (returns $0) + InitPlan 1 -> Limit -> Merge Append Sort Key: minmaxtest.f1 @@ -1210,7 +1210,7 @@ explain (costs off) -> Index Only Scan Backward using minmaxtest2i on minma= xtest2 minmaxtest_3 Index Cond: (f1 IS NOT NULL) -> Index Only Scan using minmaxtest3i on minmaxtest3 mi= nmaxtest_4 - InitPlan 2 (returns $1) + InitPlan 2 -> Limit -> Merge Append Sort Key: minmaxtest_5.f1 DESC @@ -1235,7 +1235,7 @@ explain (costs off) QUERY PLAN = = -------------------------------------------------------------------------= -------------------- Unique - InitPlan 1 (returns $0) + InitPlan 1 -> Limit -> Merge Append Sort Key: minmaxtest.f1 @@ -1246,7 +1246,7 @@ explain (costs off) -> Index Only Scan Backward using minmaxtest2i on minma= xtest2 minmaxtest_3 Index Cond: (f1 IS NOT NULL) -> Index Only Scan using minmaxtest3i on minmaxtest3 mi= nmaxtest_4 - InitPlan 2 (returns $1) + InitPlan 2 -> Limit -> Merge Append Sort Key: minmaxtest_5.f1 DESC @@ -1258,7 +1258,7 @@ explain (costs off) Index Cond: (f1 IS NOT NULL) -> Index Only Scan Backward using minmaxtest3i on minma= xtest3 minmaxtest_9 -> Sort - Sort Key: ($0), ($1) + Sort Key: ((InitPlan 1).col1), ((InitPlan 2).col1) -> Result (26 rows) = diff --git a/src/test/regress/expected/groupingsets.out b/src/test/regress= /expected/groupingsets.out index a3b9aaca84..e1f0660810 100644 --- a/src/test/regress/expected/groupingsets.out +++ b/src/test/regress/expected/groupingsets.out @@ -559,7 +559,7 @@ explain (costs off) QUERY PLAN = ------------------------------------------------------------ Result - InitPlan 1 (returns $0) + InitPlan 1 -> Limit -> Index Only Scan using tenk1_unique1 on tenk1 Index Cond: (unique1 IS NOT NULL) @@ -2109,14 +2109,14 @@ order by a, b, c; -- test handling of outer GroupingFunc within subqueries explain (costs off) select (select grouping(v1)) from (values ((select 1))) v(v1) group by cu= be(v1); - QUERY PLAN = ---------------------------- + QUERY PLAN = +------------------------------- MixedAggregate - Hash Key: $2 + Hash Key: (InitPlan 3).col1 Group Key: () - InitPlan 1 (returns $1) + InitPlan 1 -> Result - InitPlan 3 (returns $2) + InitPlan 3 -> Result -> Result SubPlan 2 @@ -2132,12 +2132,12 @@ select (select grouping(v1)) from (values ((select= 1))) v(v1) group by cube(v1); = explain (costs off) select (select grouping(v1)) from (values ((select 1))) v(v1) group by v1= ; - QUERY PLAN = ---------------------------- + QUERY PLAN = +---------------- GroupAggregate - InitPlan 1 (returns $1) + InitPlan 1 -> Result - InitPlan 3 (returns $2) + InitPlan 3 -> Result -> Result SubPlan 2 diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expe= cted/inherit.out index 130a924228..7837126bd2 100644 --- a/src/test/regress/expected/inherit.out +++ b/src/test/regress/expected/inherit.out @@ -1643,8 +1643,8 @@ explain (verbose, costs off) select min(1-id) from m= atest0; QUERY PLAN = = -------------------------------------------------------------------------= -------- Result - Output: $0 - InitPlan 1 (returns $0) + Output: (InitPlan 1).col1 + InitPlan 1 -> Limit Output: ((1 - matest0.id)) -> Result @@ -1800,7 +1800,7 @@ SELECT min(x) FROM QUERY PLAN = -------------------------------------------------------------------- Result - InitPlan 1 (returns $0) + InitPlan 1 -> Limit -> Merge Append Sort Key: a.unique1 @@ -1818,7 +1818,7 @@ SELECT min(y) FROM QUERY PLAN = -------------------------------------------------------------------- Result - InitPlan 1 (returns $0) + InitPlan 1 -> Limit -> Merge Append Sort Key: a.unique1 @@ -1898,19 +1898,19 @@ insert into inhpar select x, x::text from generate= _series(1,5) x; insert into inhcld select x::text, x from generate_series(6,10) x; explain (verbose, costs off) update inhpar i set (f1, f2) =3D (select i.f1, i.f2 || '-' from int4_tbl = limit 1); - QUERY PLAN = -------------------------------------------------------------------------- + QUERY PLAN = = +-------------------------------------------------------------------------= ------------------- Update on public.inhpar i Update on public.inhpar i_1 Update on public.inhcld i_2 -> Result - Output: $2, $3, (SubPlan 1 (returns $2,$3)), i.tableoid, i.ctid + Output: (SubPlan 1).col1, (SubPlan 1).col2, (rescan SubPlan 1), = i.tableoid, i.ctid -> Append -> Seq Scan on public.inhpar i_1 Output: i_1.f1, i_1.f2, i_1.tableoid, i_1.ctid -> Seq Scan on public.inhcld i_2 Output: i_2.f1, i_2.f2, i_2.tableoid, i_2.ctid - SubPlan 1 (returns $2,$3) + SubPlan 1 -> Limit Output: (i.f1), (((i.f2)::text || '-'::text)) -> Seq Scan on public.int4_tbl @@ -1946,21 +1946,21 @@ alter table inhpar attach partition inhcld2 for va= lues from (5) to (100); insert into inhpar select x, x::text from generate_series(1,10) x; explain (verbose, costs off) update inhpar i set (f1, f2) =3D (select i.f1, i.f2 || '-' from int4_tbl = limit 1); - QUERY PLAN = = --------------------------------------------------------------------------= ---------- + QUERY PLAN = = +-------------------------------------------------------------------------= ----------------------------- Update on public.inhpar i Update on public.inhcld1 i_1 Update on public.inhcld2 i_2 -> Append -> Seq Scan on public.inhcld1 i_1 - Output: $2, $3, (SubPlan 1 (returns $2,$3)), i_1.tableoid,= i_1.ctid - SubPlan 1 (returns $2,$3) + Output: (SubPlan 1).col1, (SubPlan 1).col2, (rescan SubPla= n 1), i_1.tableoid, i_1.ctid + SubPlan 1 -> Limit Output: (i_1.f1), (((i_1.f2)::text || '-'::text)) -> Seq Scan on public.int4_tbl Output: i_1.f1, ((i_1.f2)::text || '-'::text= ) -> Seq Scan on public.inhcld2 i_2 - Output: $2, $3, (SubPlan 1 (returns $2,$3)), i_2.tableoid,= i_2.ctid + Output: (SubPlan 1).col1, (SubPlan 1).col2, (rescan SubPla= n 1), i_2.tableoid, i_2.ctid (13 rows) = update inhpar i set (f1, f2) =3D (select i.f1, i.f2 || '-' from int4_tbl = limit 1); @@ -2845,11 +2845,11 @@ explain (costs off) select min(a), max(a) from par= ted_minmax where b =3D '12345'; QUERY PLAN = = -------------------------------------------------------------------------= ----------------------- Result - InitPlan 1 (returns $0) + InitPlan 1 -> Limit -> Index Only Scan using parted_minmax1i on parted_minmax1 pa= rted_minmax Index Cond: ((a IS NOT NULL) AND (b =3D '12345'::text)) - InitPlan 2 (returns $1) + InitPlan 2 -> Limit -> Index Only Scan Backward using parted_minmax1i on parted_m= inmax1 parted_minmax_1 Index Cond: ((a IS NOT NULL) AND (b =3D '12345'::text)) diff --git a/src/test/regress/expected/insert_conflict.out b/src/test/regr= ess/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 value= s(0, 'Crowberry') on con Insert on insertconflicttest Conflict Resolution: UPDATE Conflict Arbiter Indexes: op_index_key, collation_index_key, both_inde= x_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/expecte= d/join.out index 9605400021..63cddac0d6 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -3028,10 +3028,10 @@ 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)) + Filter: (NOT (ANY (unique1 =3D (hashed SubPlan 1).col1))) SubPlan 1 -> Index Only Scan using tenk1_unique2 on tenk1 b (4 rows) @@ -5278,12 +5278,12 @@ explain (costs off) select a.unique1, b.unique2 from onek a left join onek b on a.unique1 =3D b.unique2 where (b.unique2, random() > 0) =3D any (select q1, random() > 0 from i= nt8_tbl c where c.q1 < b.unique1); - QUERY PLAN = ----------------------------------------------------------- + QUERY PLAN = = +-------------------------------------------------------------------------= ----------------------------------------- Hash Join Hash Cond: (b.unique2 =3D a.unique1) -> Seq Scan on onek b - Filter: (SubPlan 1) + Filter: (ANY ((unique2 =3D (SubPlan 1).col1) AND ((random() > '0= '::double precision) =3D (SubPlan 1).col2))) SubPlan 1 -> Seq Scan on int8_tbl c Filter: (q1 < b.unique1) @@ -8262,8 +8262,8 @@ lateral (select * from int8_tbl t1, where q2 =3D (select greatest(t1.q1,= t2.q2)) and (select v.id=3D0)) offset 0) s= s2) ss where t1.q1 =3D 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,20 +8277,20 @@ lateral (select * from int8_tbl t1, Filter: (t1.q1 =3D ss2.q2) -> Seq Scan on public.int8_tbl t2 Output: t2.q1, t2.q2 - Filter: (SubPlan 3) + Filter: (ANY ((t2.q1 =3D (SubPlan 3).col1) AND ((ran= dom() > '0'::double precision) =3D (SubPlan 3).col2))) SubPlan 3 -> Result Output: t3.q2, (random() > '0'::double preci= sion) - One-Time Filter: $4 - InitPlan 1 (returns $2) + One-Time Filter: (InitPlan 2).col1 + InitPlan 1 -> Result Output: GREATEST(t1.q1, t2.q2) - InitPlan 2 (returns $4) + InitPlan 2 -> Result Output: ("*VALUES*".column1 =3D 0) -> Seq Scan on public.int8_tbl t3 Output: t3.q1, t3.q2 - Filter: (t3.q2 =3D $2) + Filter: (t3.q2 =3D (InitPlan 1).col1) (27 rows) = select * from (values (0), (1)) v(id), diff --git a/src/test/regress/expected/memoize.out b/src/test/regress/expe= cted/memoize.out index 1c8d996740..0fd103c06b 100644 --- a/src/test/regress/expected/memoize.out +++ b/src/test/regress/expected/memoize.out @@ -342,7 +342,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/merge.out b/src/test/regress/expect= ed/merge.out index 07561f0240..cec7f11f9f 100644 --- a/src/test/regress/expected/merge.out +++ b/src/test/regress/expected/merge.out @@ -1697,10 +1697,10 @@ WHEN MATCHED AND t.c > s.cnt THEN -> Seq Scan on public.ref r_2 Output: r_2.ab, r_2.cd Filter: ((r_2.ab =3D (s.a + s.b)) AND (r_2.cd =3D (s.c -= s.d))) - SubPlan 3 (returns $9,$10) + SubPlan 3 -> Result - Output: s.b, $8 - InitPlan 2 (returns $8) + Output: s.b, (InitPlan 2).col1 + InitPlan 2 -> Aggregate Output: count(*) -> Seq Scan on public.ref r_1 diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regr= ess/expected/partition_prune.out index 9c20a24982..7ca98397ae 100644 --- a/src/test/regress/expected/partition_prune.out +++ b/src/test/regress/expected/partition_prune.out @@ -2176,36 +2176,36 @@ explain (analyze, costs off, summary off, timing o= ff) execute ab_q1 (2, 4); prepare ab_q2 (int, int) as select a from ab where a between $1 and $2 and b < (select 3); explain (analyze, costs off, summary off, timing off) execute ab_q2 (2, 2= ); - QUERY PLAN = ---------------------------------------------------------- + QUERY PLAN = +----------------------------------------------------------------------- Append (actual rows=3D0 loops=3D1) Subplans Removed: 6 - InitPlan 1 (returns $0) + InitPlan 1 -> Result (actual rows=3D1 loops=3D1) -> Seq Scan on ab_a2_b1 ab_1 (actual rows=3D0 loops=3D1) - Filter: ((a >=3D $1) AND (a <=3D $2) AND (b < $0)) + Filter: ((a >=3D $1) AND (a <=3D $2) AND (b < (InitPlan 1).col1)= ) -> Seq Scan on ab_a2_b2 ab_2 (actual rows=3D0 loops=3D1) - Filter: ((a >=3D $1) AND (a <=3D $2) AND (b < $0)) + Filter: ((a >=3D $1) AND (a <=3D $2) AND (b < (InitPlan 1).col1)= ) -> Seq Scan on ab_a2_b3 ab_3 (never executed) - Filter: ((a >=3D $1) AND (a <=3D $2) AND (b < $0)) + Filter: ((a >=3D $1) AND (a <=3D $2) AND (b < (InitPlan 1).col1)= ) (10 rows) = -- As above, but swap the PARAM_EXEC Param to the first partition level prepare ab_q3 (int, int) as select a from ab where b between $1 and $2 and a < (select 3); explain (analyze, costs off, summary off, timing off) execute ab_q3 (2, 2= ); - QUERY PLAN = ---------------------------------------------------------- + QUERY PLAN = +----------------------------------------------------------------------- Append (actual rows=3D0 loops=3D1) Subplans Removed: 6 - InitPlan 1 (returns $0) + InitPlan 1 -> Result (actual rows=3D1 loops=3D1) -> Seq Scan on ab_a1_b2 ab_1 (actual rows=3D0 loops=3D1) - Filter: ((b >=3D $1) AND (b <=3D $2) AND (a < $0)) + Filter: ((b >=3D $1) AND (b <=3D $2) AND (a < (InitPlan 1).col1)= ) -> Seq Scan on ab_a2_b2 ab_2 (actual rows=3D0 loops=3D1) - Filter: ((b >=3D $1) AND (b <=3D $2) AND (a < $0)) + Filter: ((b >=3D $1) AND (b <=3D $2) AND (a < (InitPlan 1).col1)= ) -> Seq Scan on ab_a3_b2 ab_3 (never executed) - Filter: ((b >=3D $1) AND (b <=3D $2) AND (a < $0)) + Filter: ((b >=3D $1) AND (b <=3D $2) AND (a < (InitPlan 1).col1)= ) (10 rows) = -- @@ -2411,25 +2411,24 @@ select explain_parallel_append('execute ab_q5 (33,= 44, 55)'); = -- Test Parallel Append with PARAM_EXEC Params select explain_parallel_append('select count(*) from ab where (a =3D (sel= ect 1) or a =3D (select 3)) and b =3D 2'); - explain_parallel_append = = --------------------------------------------------------------------------= ----- + explain_parallel_append = = +-------------------------------------------------------------------------= ----------------------- Aggregate (actual rows=3DN loops=3DN) - InitPlan 1 (returns $0) + InitPlan 1 -> Result (actual rows=3DN loops=3DN) - InitPlan 2 (returns $1) + InitPlan 2 -> Result (actual rows=3DN loops=3DN) -> Gather (actual rows=3DN loops=3DN) Workers Planned: 2 - Params Evaluated: $0, $1 Workers Launched: N -> Parallel Append (actual rows=3DN loops=3DN) -> Parallel Seq Scan on ab_a1_b2 ab_1 (actual rows=3DN lo= ops=3DN) - Filter: ((b =3D 2) AND ((a =3D $0) OR (a =3D $1))) + Filter: ((b =3D 2) AND ((a =3D (InitPlan 1).col1) OR= (a =3D (InitPlan 2).col1))) -> Parallel Seq Scan on ab_a2_b2 ab_2 (never executed) - Filter: ((b =3D 2) AND ((a =3D $0) OR (a =3D $1))) + Filter: ((b =3D 2) AND ((a =3D (InitPlan 1).col1) OR= (a =3D (InitPlan 2).col1))) -> Parallel Seq Scan on ab_a3_b2 ab_3 (actual rows=3DN lo= ops=3DN) - Filter: ((b =3D 2) AND ((a =3D $0) OR (a =3D $1))) -(16 rows) + Filter: ((b =3D 2) AND ((a =3D (InitPlan 1).col1) OR= (a =3D (InitPlan 2).col1))) +(15 rows) = -- Test pruning during parallel nested loop query create table lprt_a (a int not null); @@ -2629,57 +2628,57 @@ select * from ab where a =3D (select max(a) from l= prt_a) and b =3D (select max(a)-1 QUERY PLAN = ------------------------------------------------------------------------- Append (actual rows=3D0 loops=3D1) - InitPlan 1 (returns $0) + InitPlan 1 -> Aggregate (actual rows=3D1 loops=3D1) -> Seq Scan on lprt_a (actual rows=3D102 loops=3D1) - InitPlan 2 (returns $1) + InitPlan 2 -> Aggregate (actual rows=3D1 loops=3D1) -> Seq Scan on lprt_a lprt_a_1 (actual rows=3D102 loops=3D1) -> Bitmap Heap Scan on ab_a1_b1 ab_1 (never executed) - Recheck Cond: (a =3D $0) - Filter: (b =3D $1) + Recheck Cond: (a =3D (InitPlan 1).col1) + Filter: (b =3D (InitPlan 2).col1) -> Bitmap Index Scan on ab_a1_b1_a_idx (never executed) - Index Cond: (a =3D $0) + Index Cond: (a =3D (InitPlan 1).col1) -> Bitmap Heap Scan on ab_a1_b2 ab_2 (never executed) - Recheck Cond: (a =3D $0) - Filter: (b =3D $1) + Recheck Cond: (a =3D (InitPlan 1).col1) + Filter: (b =3D (InitPlan 2).col1) -> Bitmap Index Scan on ab_a1_b2_a_idx (never executed) - Index Cond: (a =3D $0) + Index Cond: (a =3D (InitPlan 1).col1) -> Bitmap Heap Scan on ab_a1_b3 ab_3 (never executed) - Recheck Cond: (a =3D $0) - Filter: (b =3D $1) + Recheck Cond: (a =3D (InitPlan 1).col1) + Filter: (b =3D (InitPlan 2).col1) -> Bitmap Index Scan on ab_a1_b3_a_idx (never executed) - Index Cond: (a =3D $0) + Index Cond: (a =3D (InitPlan 1).col1) -> Bitmap Heap Scan on ab_a2_b1 ab_4 (never executed) - Recheck Cond: (a =3D $0) - Filter: (b =3D $1) + Recheck Cond: (a =3D (InitPlan 1).col1) + Filter: (b =3D (InitPlan 2).col1) -> Bitmap Index Scan on ab_a2_b1_a_idx (never executed) - Index Cond: (a =3D $0) + Index Cond: (a =3D (InitPlan 1).col1) -> Bitmap Heap Scan on ab_a2_b2 ab_5 (never executed) - Recheck Cond: (a =3D $0) - Filter: (b =3D $1) + Recheck Cond: (a =3D (InitPlan 1).col1) + Filter: (b =3D (InitPlan 2).col1) -> Bitmap Index Scan on ab_a2_b2_a_idx (never executed) - Index Cond: (a =3D $0) + Index Cond: (a =3D (InitPlan 1).col1) -> Bitmap Heap Scan on ab_a2_b3 ab_6 (never executed) - Recheck Cond: (a =3D $0) - Filter: (b =3D $1) + Recheck Cond: (a =3D (InitPlan 1).col1) + Filter: (b =3D (InitPlan 2).col1) -> Bitmap Index Scan on ab_a2_b3_a_idx (never executed) - Index Cond: (a =3D $0) + Index Cond: (a =3D (InitPlan 1).col1) -> Bitmap Heap Scan on ab_a3_b1 ab_7 (never executed) - Recheck Cond: (a =3D $0) - Filter: (b =3D $1) + Recheck Cond: (a =3D (InitPlan 1).col1) + Filter: (b =3D (InitPlan 2).col1) -> Bitmap Index Scan on ab_a3_b1_a_idx (never executed) - Index Cond: (a =3D $0) + Index Cond: (a =3D (InitPlan 1).col1) -> Bitmap Heap Scan on ab_a3_b2 ab_8 (actual rows=3D0 loops=3D1) - Recheck Cond: (a =3D $0) - Filter: (b =3D $1) + Recheck Cond: (a =3D (InitPlan 1).col1) + Filter: (b =3D (InitPlan 2).col1) -> Bitmap Index Scan on ab_a3_b2_a_idx (actual rows=3D0 loops=3D= 1) - Index Cond: (a =3D $0) + Index Cond: (a =3D (InitPlan 1).col1) -> Bitmap Heap Scan on ab_a3_b3 ab_9 (never executed) - Recheck Cond: (a =3D $0) - Filter: (b =3D $1) + Recheck Cond: (a =3D (InitPlan 1).col1) + Filter: (b =3D (InitPlan 2).col1) -> Bitmap Index Scan on ab_a3_b3_a_idx (never executed) - Index Cond: (a =3D $0) + Index Cond: (a =3D (InitPlan 1).col1) (52 rows) = -- Test run-time partition pruning with UNION ALL parents @@ -2688,42 +2687,42 @@ select * from (select * from ab where a =3D 1 unio= n all select * from ab) ab where QUERY PLAN = = -------------------------------------------------------------------------= ------ Append (actual rows=3D0 loops=3D1) - InitPlan 1 (returns $0) + InitPlan 1 -> Result (actual rows=3D1 loops=3D1) -> Append (actual rows=3D0 loops=3D1) -> Bitmap Heap Scan on ab_a1_b1 ab_11 (actual rows=3D0 loops=3D= 1) Recheck Cond: (a =3D 1) - Filter: (b =3D $0) + Filter: (b =3D (InitPlan 1).col1) -> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=3D0 l= oops=3D1) Index Cond: (a =3D 1) -> Bitmap Heap Scan on ab_a1_b2 ab_12 (never executed) Recheck Cond: (a =3D 1) - Filter: (b =3D $0) + Filter: (b =3D (InitPlan 1).col1) -> Bitmap Index Scan on ab_a1_b2_a_idx (never executed) Index Cond: (a =3D 1) -> Bitmap Heap Scan on ab_a1_b3 ab_13 (never executed) Recheck Cond: (a =3D 1) - Filter: (b =3D $0) + Filter: (b =3D (InitPlan 1).col1) -> Bitmap Index Scan on ab_a1_b3_a_idx (never executed) Index Cond: (a =3D 1) -> Seq Scan on ab_a1_b1 ab_1 (actual rows=3D0 loops=3D1) - Filter: (b =3D $0) + Filter: (b =3D (InitPlan 1).col1) -> Seq Scan on ab_a1_b2 ab_2 (never executed) - Filter: (b =3D $0) + Filter: (b =3D (InitPlan 1).col1) -> Seq Scan on ab_a1_b3 ab_3 (never executed) - Filter: (b =3D $0) + Filter: (b =3D (InitPlan 1).col1) -> Seq Scan on ab_a2_b1 ab_4 (actual rows=3D0 loops=3D1) - Filter: (b =3D $0) + Filter: (b =3D (InitPlan 1).col1) -> Seq Scan on ab_a2_b2 ab_5 (never executed) - Filter: (b =3D $0) + Filter: (b =3D (InitPlan 1).col1) -> Seq Scan on ab_a2_b3 ab_6 (never executed) - Filter: (b =3D $0) + Filter: (b =3D (InitPlan 1).col1) -> Seq Scan on ab_a3_b1 ab_7 (actual rows=3D0 loops=3D1) - Filter: (b =3D $0) + Filter: (b =3D (InitPlan 1).col1) -> Seq Scan on ab_a3_b2 ab_8 (never executed) - Filter: (b =3D $0) + Filter: (b =3D (InitPlan 1).col1) -> Seq Scan on ab_a3_b3 ab_9 (never executed) - Filter: (b =3D $0) + Filter: (b =3D (InitPlan 1).col1) (37 rows) = -- A case containing a UNION ALL with a non-partitioned child. @@ -2732,44 +2731,44 @@ select * from (select * from ab where a =3D 1 unio= n all (values(10,5)) union all s QUERY PLAN = = -------------------------------------------------------------------------= ------ Append (actual rows=3D0 loops=3D1) - InitPlan 1 (returns $0) + InitPlan 1 -> Result (actual rows=3D1 loops=3D1) -> Append (actual rows=3D0 loops=3D1) -> Bitmap Heap Scan on ab_a1_b1 ab_11 (actual rows=3D0 loops=3D= 1) Recheck Cond: (a =3D 1) - Filter: (b =3D $0) + Filter: (b =3D (InitPlan 1).col1) -> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=3D0 l= oops=3D1) Index Cond: (a =3D 1) -> Bitmap Heap Scan on ab_a1_b2 ab_12 (never executed) Recheck Cond: (a =3D 1) - Filter: (b =3D $0) + Filter: (b =3D (InitPlan 1).col1) -> Bitmap Index Scan on ab_a1_b2_a_idx (never executed) Index Cond: (a =3D 1) -> Bitmap Heap Scan on ab_a1_b3 ab_13 (never executed) Recheck Cond: (a =3D 1) - Filter: (b =3D $0) + Filter: (b =3D (InitPlan 1).col1) -> Bitmap Index Scan on ab_a1_b3_a_idx (never executed) Index Cond: (a =3D 1) -> Result (actual rows=3D0 loops=3D1) - One-Time Filter: (5 =3D $0) + One-Time Filter: (5 =3D (InitPlan 1).col1) -> Seq Scan on ab_a1_b1 ab_1 (actual rows=3D0 loops=3D1) - Filter: (b =3D $0) + Filter: (b =3D (InitPlan 1).col1) -> Seq Scan on ab_a1_b2 ab_2 (never executed) - Filter: (b =3D $0) + Filter: (b =3D (InitPlan 1).col1) -> Seq Scan on ab_a1_b3 ab_3 (never executed) - Filter: (b =3D $0) + Filter: (b =3D (InitPlan 1).col1) -> Seq Scan on ab_a2_b1 ab_4 (actual rows=3D0 loops=3D1) - Filter: (b =3D $0) + Filter: (b =3D (InitPlan 1).col1) -> Seq Scan on ab_a2_b2 ab_5 (never executed) - Filter: (b =3D $0) + Filter: (b =3D (InitPlan 1).col1) -> Seq Scan on ab_a2_b3 ab_6 (never executed) - Filter: (b =3D $0) + Filter: (b =3D (InitPlan 1).col1) -> Seq Scan on ab_a3_b1 ab_7 (actual rows=3D0 loops=3D1) - Filter: (b =3D $0) + Filter: (b =3D (InitPlan 1).col1) -> Seq Scan on ab_a3_b2 ab_8 (never executed) - Filter: (b =3D $0) + Filter: (b =3D (InitPlan 1).col1) -> Seq Scan on ab_a3_b3 ab_9 (never executed) - Filter: (b =3D $0) + Filter: (b =3D (InitPlan 1).col1) (39 rows) = -- Another UNION ALL test, but containing a mix of exec init and exec run= -time pruning. @@ -2787,27 +2786,27 @@ union all ) ab where a =3D $1 and b =3D (select -10); -- Ensure the xy_1 subplan is not pruned. explain (analyze, costs off, summary off, timing off) execute ab_q6(1); - QUERY PLAN = --------------------------------------------------- + QUERY PLAN = +-------------------------------------------------------- Append (actual rows=3D0 loops=3D1) Subplans Removed: 12 - InitPlan 1 (returns $0) + InitPlan 1 -> Result (actual rows=3D1 loops=3D1) -> Seq Scan on ab_a1_b1 ab_1 (never executed) - Filter: ((a =3D $1) AND (b =3D $0)) + Filter: ((a =3D $1) AND (b =3D (InitPlan 1).col1)) -> Seq Scan on ab_a1_b2 ab_2 (never executed) - Filter: ((a =3D $1) AND (b =3D $0)) + Filter: ((a =3D $1) AND (b =3D (InitPlan 1).col1)) -> Seq Scan on ab_a1_b3 ab_3 (never executed) - Filter: ((a =3D $1) AND (b =3D $0)) + Filter: ((a =3D $1) AND (b =3D (InitPlan 1).col1)) -> Seq Scan on xy_1 (actual rows=3D0 loops=3D1) - Filter: ((x =3D $1) AND (y =3D $0)) + Filter: ((x =3D $1) AND (y =3D (InitPlan 1).col1)) Rows Removed by Filter: 1 -> Seq Scan on ab_a1_b1 ab_4 (never executed) - Filter: ((a =3D $1) AND (b =3D $0)) + Filter: ((a =3D $1) AND (b =3D (InitPlan 1).col1)) -> Seq Scan on ab_a1_b2 ab_5 (never executed) - Filter: ((a =3D $1) AND (b =3D $0)) + Filter: ((a =3D $1) AND (b =3D (InitPlan 1).col1)) -> Seq Scan on ab_a1_b3 ab_6 (never executed) - Filter: ((a =3D $1) AND (b =3D $0)) + Filter: ((a =3D $1) AND (b =3D (InitPlan 1).col1)) (19 rows) = -- Ensure we see just the xy_1 row. @@ -2886,7 +2885,7 @@ update ab_a1 set b =3D 3 from ab_a2 where ab_a2.b =3D= (select 1); Update on ab_a1_b1 ab_a1_1 Update on ab_a1_b2 ab_a1_2 Update on ab_a1_b3 ab_a1_3 - InitPlan 1 (returns $0) + InitPlan 1 -> Result (actual rows=3D1 loops=3D1) -> Nested Loop (actual rows=3D3 loops=3D1) -> Append (actual rows=3D3 loops=3D1) @@ -2896,11 +2895,11 @@ update ab_a1 set b =3D 3 from ab_a2 where ab_a2.b = =3D (select 1); -> Materialize (actual rows=3D1 loops=3D3) -> Append (actual rows=3D1 loops=3D1) -> Seq Scan on ab_a2_b1 ab_a2_1 (actual rows=3D1 lo= ops=3D1) - Filter: (b =3D $0) + Filter: (b =3D (InitPlan 1).col1) -> Seq Scan on ab_a2_b2 ab_a2_2 (never executed) - Filter: (b =3D $0) + Filter: (b =3D (InitPlan 1).col1) -> Seq Scan on ab_a2_b3 ab_a2_3 (never executed) - Filter: (b =3D $0) + Filter: (b =3D (InitPlan 1).col1) (19 rows) = select tableoid::regclass, * from ab; @@ -3234,12 +3233,12 @@ select * from listp where a =3D (select null::int)= ; QUERY PLAN = ------------------------------------------------------ Append (actual rows=3D0 loops=3D1) - InitPlan 1 (returns $0) + InitPlan 1 -> Result (actual rows=3D1 loops=3D1) -> Seq Scan on listp_1_1 listp_1 (never executed) - Filter: (a =3D $0) + Filter: (a =3D (InitPlan 1).col1) -> Seq Scan on listp_2_1 listp_2 (never executed) - Filter: (a =3D $0) + Filter: (a =3D (InitPlan 1).col1) (7 rows) = drop table listp; @@ -3377,14 +3376,14 @@ prepare ps1 as select * from mc3p where a =3D $1 and abs(b) < (select 3); explain (analyze, costs off, summary off, timing off) execute ps1(1); - QUERY PLAN = --------------------------------------------------------- + QUERY PLAN = +------------------------------------------------------------- Append (actual rows=3D1 loops=3D1) Subplans Removed: 2 - InitPlan 1 (returns $0) + InitPlan 1 -> Result (actual rows=3D1 loops=3D1) -> Seq Scan on mc3p1 mc3p_1 (actual rows=3D1 loops=3D1) - Filter: ((a =3D $1) AND (abs(b) < $0)) + Filter: ((a =3D $1) AND (abs(b) < (InitPlan 1).col1)) (6 rows) = deallocate ps1; @@ -3392,16 +3391,16 @@ prepare ps2 as select * from mc3p where a <=3D $1 and abs(b) < (select 3); explain (analyze, costs off, summary off, timing off) execute ps2(1); - QUERY PLAN = --------------------------------------------------------- + QUERY PLAN = +-------------------------------------------------------------- Append (actual rows=3D2 loops=3D1) Subplans Removed: 1 - InitPlan 1 (returns $0) + InitPlan 1 -> Result (actual rows=3D1 loops=3D1) -> Seq Scan on mc3p0 mc3p_1 (actual rows=3D1 loops=3D1) - Filter: ((a <=3D $1) AND (abs(b) < $0)) + Filter: ((a <=3D $1) AND (abs(b) < (InitPlan 1).col1)) -> Seq Scan on mc3p1 mc3p_2 (actual rows=3D1 loops=3D1) - Filter: ((a <=3D $1) AND (abs(b) < $0)) + Filter: ((a <=3D $1) AND (abs(b) < (InitPlan 1).col1)) (8 rows) = deallocate ps2; @@ -3417,14 +3416,14 @@ select * from boolp where a =3D (select value from= boolvalues where value); QUERY PLAN = ----------------------------------------------------------- Append (actual rows=3D0 loops=3D1) - InitPlan 1 (returns $0) + InitPlan 1 -> Seq Scan on boolvalues (actual rows=3D1 loops=3D1) Filter: value Rows Removed by Filter: 1 -> Seq Scan on boolp_f boolp_1 (never executed) - Filter: (a =3D $0) + Filter: (a =3D (InitPlan 1).col1) -> Seq Scan on boolp_t boolp_2 (actual rows=3D0 loops=3D1) - Filter: (a =3D $0) + Filter: (a =3D (InitPlan 1).col1) (9 rows) = explain (analyze, costs off, summary off, timing off) @@ -3432,14 +3431,14 @@ select * from boolp where a =3D (select value from= boolvalues where not value); QUERY PLAN = ----------------------------------------------------------- Append (actual rows=3D0 loops=3D1) - InitPlan 1 (returns $0) + InitPlan 1 -> Seq Scan on boolvalues (actual rows=3D1 loops=3D1) Filter: (NOT value) Rows Removed by Filter: 1 -> Seq Scan on boolp_f boolp_1 (actual rows=3D0 loops=3D1) - Filter: (a =3D $0) + Filter: (a =3D (InitPlan 1).col1) -> Seq Scan on boolp_t boolp_2 (never executed) - Filter: (a =3D $0) + Filter: (a =3D (InitPlan 1).col1) (9 rows) = drop table boolp; @@ -3528,18 +3527,18 @@ explain (analyze, costs off, summary off, timing o= ff) select * from ma_test wher -------------------------------------------------------------------------= ---------------------- Merge Append (actual rows=3D20 loops=3D1) Sort Key: ma_test.b - InitPlan 2 (returns $1) + InitPlan 2 -> Result (actual rows=3D1 loops=3D1) - InitPlan 1 (returns $0) + InitPlan 1 -> Limit (actual rows=3D1 loops=3D1) -> Index Scan using ma_test_p2_b_idx on ma_test_p2 (a= ctual rows=3D1 loops=3D1) Index Cond: (b IS NOT NULL) -> Index Scan using ma_test_p1_b_idx on ma_test_p1 ma_test_1 (never e= xecuted) - Filter: (a >=3D $1) + Filter: (a >=3D (InitPlan 2).col1) -> Index Scan using ma_test_p2_b_idx on ma_test_p2 ma_test_2 (actual = rows=3D10 loops=3D1) - Filter: (a >=3D $1) + Filter: (a >=3D (InitPlan 2).col1) -> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_3 (actual = rows=3D10 loops=3D1) - Filter: (a >=3D $1) + Filter: (a >=3D (InitPlan 2).col1) (14 rows) = reset enable_seqscan; @@ -3908,17 +3907,17 @@ from ( select 1, 1, 1 ) s(a, b, c) where s.a =3D 1 and s.b =3D 1 and s.c =3D (select 1); - QUERY PLAN = ----------------------------------------------------- + QUERY PLAN = +------------------------------------------------------------------- Append - InitPlan 1 (returns $0) + InitPlan 1 -> Result -> Seq Scan on p1 p - Filter: ((a =3D 1) AND (b =3D 1) AND (c =3D $0)) + Filter: ((a =3D 1) AND (b =3D 1) AND (c =3D (InitPlan 1).col1)) -> Seq Scan on q111 q1 - Filter: ((a =3D 1) AND (b =3D 1) AND (c =3D $0)) + Filter: ((a =3D 1) AND (b =3D 1) AND (c =3D (InitPlan 1).col1)) -> Result - One-Time Filter: (1 =3D $0) + One-Time Filter: (1 =3D (InitPlan 1).col1) (9 rows) = select * @@ -3946,18 +3945,18 @@ from ( ) s(a, b, c) where s.a =3D $1 and s.b =3D $2 and s.c =3D (select 1); explain (costs off) execute q (1, 1); - QUERY PLAN = ---------------------------------------------------------------- + QUERY PLAN = = +-------------------------------------------------------------------------= ----- Append Subplans Removed: 1 - InitPlan 1 (returns $0) + InitPlan 1 -> Result -> Seq Scan on p1 p - Filter: ((a =3D $1) AND (b =3D $2) AND (c =3D $0)) + Filter: ((a =3D $1) AND (b =3D $2) AND (c =3D (InitPlan 1).col1)= ) -> Seq Scan on q111 q1 - Filter: ((a =3D $1) AND (b =3D $2) AND (c =3D $0)) + Filter: ((a =3D $1) AND (b =3D $2) AND (c =3D (InitPlan 1).col1)= ) -> Result - One-Time Filter: ((1 =3D $1) AND (1 =3D $2) AND (1 =3D $0)) + One-Time Filter: ((1 =3D $1) AND (1 =3D $2) AND (1 =3D (InitPlan= 1).col1)) (10 rows) = execute q (1, 1); @@ -3975,11 +3974,11 @@ create table listp2 partition of listp for values = in(2) partition by list(b); create table listp2_10 partition of listp2 for values in (10); explain (analyze, costs off, summary off, timing off) select * from listp where a =3D (select 2) and b <> 10; - QUERY PLAN = --------------------------------------------------- + QUERY PLAN = +--------------------------------------------------- Seq Scan on listp1 listp (actual rows=3D0 loops=3D1) - Filter: ((b <> 10) AND (a =3D $0)) - InitPlan 1 (returns $0) + Filter: ((b <> 10) AND (a =3D (InitPlan 1).col1)) + InitPlan 1 -> Result (never executed) (4 rows) = @@ -4044,16 +4043,15 @@ select explain_parallel_append('select * from list= p where a =3D (select 1);'); ---------------------------------------------------------------------- Gather (actual rows=3DN loops=3DN) Workers Planned: 2 - Params Evaluated: $0 Workers Launched: N - InitPlan 1 (returns $0) + InitPlan 1 -> Result (actual rows=3DN loops=3DN) -> Parallel Append (actual rows=3DN loops=3DN) -> Seq Scan on listp_12_1 listp_1 (actual rows=3DN loops=3DN) - Filter: (a =3D $0) + Filter: (a =3D (InitPlan 1).col1) -> Parallel Seq Scan on listp_12_2 listp_2 (never executed) - Filter: (a =3D $0) -(11 rows) + Filter: (a =3D (InitPlan 1).col1) +(10 rows) = -- Like the above but throw some more complexity at the planner by adding -- a UNION ALL. We expect both sides of the union not to scan the @@ -4069,19 +4067,19 @@ select * from listp where a =3D (select 2);'); Workers Launched: N -> Parallel Append (actual rows=3DN loops=3DN) -> Parallel Append (actual rows=3DN loops=3DN) - InitPlan 2 (returns $1) + InitPlan 2 -> Result (actual rows=3DN loops=3DN) -> Seq Scan on listp_12_1 listp_1 (never executed) - Filter: (a =3D $1) + Filter: (a =3D (InitPlan 2).col1) -> Parallel Seq Scan on listp_12_2 listp_2 (actual rows=3D= N loops=3DN) - Filter: (a =3D $1) + Filter: (a =3D (InitPlan 2).col1) -> Parallel Append (actual rows=3DN loops=3DN) - InitPlan 1 (returns $0) + InitPlan 1 -> Result (actual rows=3DN loops=3DN) -> Seq Scan on listp_12_1 listp_4 (actual rows=3DN loops=3D= N) - Filter: (a =3D $0) + Filter: (a =3D (InitPlan 1).col1) -> Parallel Seq Scan on listp_12_2 listp_5 (never execute= d) - Filter: (a =3D $0) + Filter: (a =3D (InitPlan 1).col1) (18 rows) = drop table listp; @@ -4104,20 +4102,20 @@ select * from rangep where b IN((select 1),(select= 2)) order by a; QUERY PLAN = = -------------------------------------------------------------------------= ----------------------------------- Append (actual rows=3D0 loops=3D1) - InitPlan 1 (returns $0) + InitPlan 1 -> Result (actual rows=3D1 loops=3D1) - InitPlan 2 (returns $1) + InitPlan 2 -> Result (actual rows=3D1 loops=3D1) -> Merge Append (actual rows=3D0 loops=3D1) Sort Key: rangep_2.a -> Index Scan using rangep_0_to_100_1_a_idx on rangep_0_to_100_= 1 rangep_2 (actual rows=3D0 loops=3D1) - Filter: (b =3D ANY (ARRAY[$0, $1])) + Filter: (b =3D ANY (ARRAY[(InitPlan 1).col1, (InitPlan 2).= col1])) -> Index Scan using rangep_0_to_100_2_a_idx on rangep_0_to_100_= 2 rangep_3 (actual rows=3D0 loops=3D1) - Filter: (b =3D ANY (ARRAY[$0, $1])) + Filter: (b =3D ANY (ARRAY[(InitPlan 1).col1, (InitPlan 2).= col1])) -> Index Scan using rangep_0_to_100_3_a_idx on rangep_0_to_100_= 3 rangep_4 (never executed) - Filter: (b =3D ANY (ARRAY[$0, $1])) + Filter: (b =3D ANY (ARRAY[(InitPlan 1).col1, (InitPlan 2).= col1])) -> Index Scan using rangep_100_to_200_a_idx on rangep_100_to_200 rang= ep_5 (actual rows=3D0 loops=3D1) - Filter: (b =3D ANY (ARRAY[$0, $1])) + Filter: (b =3D ANY (ARRAY[(InitPlan 1).col1, (InitPlan 2).col1])= ) (15 rows) = reset enable_sort; diff --git a/src/test/regress/expected/portals.out b/src/test/regress/expe= cted/portals.out index f71e0b3d41..06726ed4ab 100644 --- a/src/test/regress/expected/portals.out +++ b/src/test/regress/expected/portals.out @@ -1472,18 +1472,18 @@ rollback; -- Check handling of non-backwards-scan-capable plans with scroll cursors begin; explain (costs off) declare c1 cursor for select (select 42) as x; - QUERY PLAN = ---------------------------- + QUERY PLAN = +---------------- Result - InitPlan 1 (returns $0) + InitPlan 1 -> Result (3 rows) = explain (costs off) declare c1 scroll cursor for select (select 42) as x; - QUERY PLAN = ---------------------------- + QUERY PLAN = +---------------- Materialize - InitPlan 1 (returns $0) + InitPlan 1 -> Result -> Result (4 rows) diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/= expected/rowsecurity.out index d507a2c8ca..527cf7e7bf 100644 --- a/src/test/regress/expected/rowsecurity.out +++ b/src/test/regress/expected/rowsecurity.out @@ -265,27 +265,27 @@ NOTICE: f_leak =3D> awesome science fiction (5 rows) = EXPLAIN (COSTS OFF) SELECT * FROM document WHERE f_leak(dtitle); - QUERY PLAN = ----------------------------------------------------- + QUERY PLAN = +-------------------------------------------------------------- Seq Scan on document - Filter: ((dlevel <=3D $0) AND f_leak(dtitle)) - InitPlan 1 (returns $0) + Filter: ((dlevel <=3D (InitPlan 1).col1) AND f_leak(dtitle)) + InitPlan 1 -> Index Scan using uaccount_pkey on uaccount Index Cond: (pguser =3D CURRENT_USER) (5 rows) = EXPLAIN (COSTS OFF) SELECT * FROM document NATURAL JOIN category WHERE f_= leak(dtitle); - QUERY PLAN = ------------------------------------------------------------ + QUERY PLAN = = +-------------------------------------------------------------------------= - Hash Join Hash Cond: (category.cid =3D document.cid) - InitPlan 1 (returns $0) + InitPlan 1 -> Index Scan using uaccount_pkey on uaccount Index Cond: (pguser =3D CURRENT_USER) -> Seq Scan on category -> Hash -> Seq Scan on document - Filter: ((dlevel <=3D $0) AND f_leak(dtitle)) + Filter: ((dlevel <=3D (InitPlan 1).col1) AND f_leak(dtitle= )) (9 rows) = -- viewpoint from regress_rls_dave @@ -329,27 +329,27 @@ NOTICE: f_leak =3D> awesome technology book (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 <=3D $= 0) AND f_leak(dtitle)) - InitPlan 1 (returns $0) + Filter: ((cid <> 44) AND (cid <> 44) AND (cid < 50) AND (dlevel <=3D (= InitPlan 1).col1) AND f_leak(dtitle)) + InitPlan 1 -> Index Scan using uaccount_pkey on uaccount Index Cond: (pguser =3D CURRENT_USER) (5 rows) = EXPLAIN (COSTS OFF) SELECT * FROM document NATURAL JOIN category WHERE f_= leak(dtitle); - QUERY PLAN = = --------------------------------------------------------------------------= --------------------------------- + QUERY PLAN = = +-------------------------------------------------------------------------= ------------------------------------------------ Hash Join Hash Cond: (category.cid =3D document.cid) - InitPlan 1 (returns $0) + InitPlan 1 -> Index Scan using uaccount_pkey on uaccount Index Cond: (pguser =3D CURRENT_USER) -> Seq Scan on category -> Hash -> Seq Scan on document - Filter: ((cid <> 44) AND (cid <> 44) AND (cid < 50) AND (d= level <=3D $0) AND f_leak(dtitle)) + Filter: ((cid <> 44) AND (cid <> 44) AND (cid < 50) AND (d= level <=3D (InitPlan 1).col1) AND f_leak(dtitle)) (9 rows) = -- 44 would technically fail for both p2r and p1r, but we should get an e= rror @@ -987,18 +987,18 @@ NOTICE: f_leak =3D> my first satire (4 rows) = EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle); - QUERY PLAN = ------------------------------------------------------------- + QUERY PLAN = +-------------------------------------------------------------------- Append - InitPlan 1 (returns $0) + InitPlan 1 -> Index Scan using uaccount_pkey on uaccount Index Cond: (pguser =3D CURRENT_USER) -> Seq Scan on part_document_fiction part_document_1 - Filter: ((dlevel <=3D $0) AND f_leak(dtitle)) + Filter: ((dlevel <=3D (InitPlan 1).col1) AND f_leak(dtitle)) -> Seq Scan on part_document_satire part_document_2 - Filter: ((dlevel <=3D $0) AND f_leak(dtitle)) + Filter: ((dlevel <=3D (InitPlan 1).col1) AND f_leak(dtitle)) -> Seq Scan on part_document_nonfiction part_document_3 - Filter: ((dlevel <=3D $0) AND f_leak(dtitle)) + Filter: ((dlevel <=3D (InitPlan 1).col1) AND f_leak(dtitle)) (10 rows) = -- viewpoint from regress_rls_carol @@ -1029,18 +1029,18 @@ NOTICE: f_leak =3D> awesome technology book (10 rows) = EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle); - QUERY PLAN = ------------------------------------------------------------- + QUERY PLAN = +-------------------------------------------------------------------- Append - InitPlan 1 (returns $0) + InitPlan 1 -> Index Scan using uaccount_pkey on uaccount Index Cond: (pguser =3D CURRENT_USER) -> Seq Scan on part_document_fiction part_document_1 - Filter: ((dlevel <=3D $0) AND f_leak(dtitle)) + Filter: ((dlevel <=3D (InitPlan 1).col1) AND f_leak(dtitle)) -> Seq Scan on part_document_satire part_document_2 - Filter: ((dlevel <=3D $0) AND f_leak(dtitle)) + Filter: ((dlevel <=3D (InitPlan 1).col1) AND f_leak(dtitle)) -> Seq Scan on part_document_nonfiction part_document_3 - Filter: ((dlevel <=3D $0) AND f_leak(dtitle)) + Filter: ((dlevel <=3D (InitPlan 1).col1) AND f_leak(dtitle)) (10 rows) = -- viewpoint from regress_rls_dave @@ -1059,11 +1059,11 @@ NOTICE: f_leak =3D> awesome science fiction (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 <=3D $0) AND f_leak(dtitle)) - InitPlan 1 (returns $0) + Filter: ((cid < 55) AND (dlevel <=3D (InitPlan 1).col1) AND f_leak(dti= tle)) + InitPlan 1 -> Index Scan using uaccount_pkey on uaccount Index Cond: (pguser =3D CURRENT_USER) (5 rows) @@ -1137,11 +1137,11 @@ NOTICE: f_leak =3D> awesome science fiction (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 <=3D $0) AND f_leak(dtitle)) - InitPlan 1 (returns $0) + Filter: ((cid < 55) AND (dlevel <=3D (InitPlan 1).col1) AND f_leak(dti= tle)) + InitPlan 1 -> Index Scan using uaccount_pkey on uaccount Index Cond: (pguser =3D CURRENT_USER) (5 rows) @@ -1176,18 +1176,18 @@ NOTICE: f_leak =3D> awesome technology book (11 rows) = EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle); - QUERY PLAN = ------------------------------------------------------------- + QUERY PLAN = +-------------------------------------------------------------------- Append - InitPlan 1 (returns $0) + InitPlan 1 -> Index Scan using uaccount_pkey on uaccount Index Cond: (pguser =3D CURRENT_USER) -> Seq Scan on part_document_fiction part_document_1 - Filter: ((dlevel <=3D $0) AND f_leak(dtitle)) + Filter: ((dlevel <=3D (InitPlan 1).col1) AND f_leak(dtitle)) -> Seq Scan on part_document_satire part_document_2 - Filter: ((dlevel <=3D $0) AND f_leak(dtitle)) + Filter: ((dlevel <=3D (InitPlan 1).col1) AND f_leak(dtitle)) -> Seq Scan on part_document_nonfiction part_document_3 - Filter: ((dlevel <=3D $0) AND f_leak(dtitle)) + Filter: ((dlevel <=3D (InitPlan 1).col1) AND f_leak(dtitle)) (10 rows) = -- only owner can change policies @@ -1437,10 +1437,10 @@ NOTICE: f_leak =3D> 03b26944890929ff751653acb2f2a= f79 (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)) + Filter: ((ANY (a =3D (hashed SubPlan 1).col1)) AND f_leak(b)) SubPlan 1 -> Seq Scan on s2 Filter: (((x % 2) =3D 0) AND (y ~~ '%2f%'::text)) @@ -1457,10 +1457,10 @@ NOTICE: f_leak =3D> 03b26944890929ff751653acb2f2a= f79 (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)) + Filter: ((ANY (a =3D (hashed SubPlan 1).col1)) AND f_leak(b)) SubPlan 1 -> Seq Scan on s2 Filter: (((x % 2) =3D 0) AND (y ~~ '%af%'::text)) @@ -1480,7 +1480,7 @@ EXPLAIN (COSTS OFF) SELECT (SELECT x FROM s1 LIMIT 1= ) xx, * FROM s2 WHERE y like SubPlan 2 -> Limit -> Seq Scan on s1 - Filter: (hashed SubPlan 1) + Filter: (ANY (a =3D (hashed SubPlan 1).col1)) SubPlan 1 -> Seq Scan on s2 s2_1 Filter: (((x % 2) =3D 0) AND (y ~~ '%af%'::text)= ) @@ -2717,10 +2717,10 @@ NOTICE: f_leak =3D> 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) =3D 0) AND f_leak(b)) + Filter: ((NOT (ANY (a =3D (hashed SubPlan 1).col1))) AND ((a % 2) =3D = 0) AND f_leak(b)) SubPlan 1 -> Seq Scan on z1_blacklist (4 rows) @@ -2735,10 +2735,10 @@ NOTICE: f_leak =3D> 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) =3D 0) AND f_leak(b)) + Filter: ((NOT (ANY (a =3D (hashed SubPlan 1).col1))) AND ((a % 2) =3D = 0) AND f_leak(b)) SubPlan 1 -> Seq Scan on z1_blacklist (4 rows) @@ -2907,10 +2907,10 @@ NOTICE: f_leak =3D> 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) =3D 0) AND f_leak(b)) + Filter: ((NOT (ANY (a =3D (hashed SubPlan 1).col1))) AND ((a % 2) =3D = 0) AND f_leak(b)) SubPlan 1 -> Seq Scan on z1_blacklist (4 rows) @@ -2933,10 +2933,10 @@ NOTICE: f_leak =3D> 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) =3D 1) AND f_leak(b)) + Filter: ((NOT (ANY (a =3D (hashed SubPlan 1).col1))) AND ((a % 2) =3D = 1) AND f_leak(b)) SubPlan 1 -> Seq Scan on z1_blacklist (4 rows) diff --git a/src/test/regress/expected/rowtypes.out b/src/test/regress/exp= ected/rowtypes.out index 8f3c153bac..b400b58f76 100644 --- a/src/test/regress/expected/rowtypes.out +++ b/src/test/regress/expected/rowtypes.out @@ -1249,8 +1249,8 @@ with cte(c) as materialized (select row(1, 2)), select * from cte2 as t where (select * from (select c as c1) s where (select (c1).f1 > 0)) is not null; - QUERY PLAN = --------------------------------------------- + QUERY PLAN = +---------------------------------------------- CTE Scan on cte Output: cte.c Filter: ((SubPlan 3) IS NOT NULL) @@ -1260,8 +1260,8 @@ where (select * from (select c as c1) s SubPlan 3 -> Result Output: cte.c - One-Time Filter: $2 - InitPlan 2 (returns $2) + One-Time Filter: (InitPlan 2).col1 + InitPlan 2 -> Result Output: ((cte.c).f1 > 0) (13 rows) diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regr= ess/expected/select_parallel.out index 7a0d78dfe3..4ffc5b4c56 100644 --- a/src/test/regress/expected/select_parallel.out +++ b/src/test/regress/expected/select_parallel.out @@ -291,14 +291,14 @@ alter table tenk2 set (parallel_workers =3D 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)) + Filter: (NOT (ANY ((two =3D (hashed SubPlan 1).col1)= AND (four =3D (hashed SubPlan 1).col2)))) SubPlan 1 -> Seq Scan on tenk2 Filter: (thousand > 100) @@ -315,10 +315,10 @@ 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)) + Filter: (NOT (ANY ((((unique1)::double precision + random()))::integer= =3D (hashed SubPlan 1).col1))) SubPlan 1 -> Seq Scan on tenk2 (4 rows) @@ -335,7 +335,7 @@ explain (costs off) QUERY PLAN = ------------------------------------------------------ Aggregate - InitPlan 1 (returns $2) + InitPlan 1 -> Finalize Aggregate -> Gather Workers Planned: 2 @@ -343,10 +343,9 @@ explain (costs off) -> Parallel Seq Scan on tenk2 -> Gather Workers Planned: 4 - Params Evaluated: $2 -> Parallel Seq Scan on tenk1 - Filter: (unique1 =3D $2) -(12 rows) + Filter: (unique1 =3D (InitPlan 1).col1) +(11 rows) = select count(*) from tenk1 where tenk1.unique1 =3D (Select max(tenk2.unique1) from tenk2); @@ -1150,27 +1149,25 @@ ORDER BY 1; -> Append -> Gather Workers Planned: 4 - Params Evaluated: $1 - InitPlan 1 (returns $1) + InitPlan 1 -> Limit -> Gather Workers Planned: 4 -> Parallel Seq Scan on tenk1 tenk1_2 Filter: (fivethous =3D 1) -> Parallel Seq Scan on tenk1 - Filter: (fivethous =3D $1) + Filter: (fivethous =3D (InitPlan 1).col1) -> Gather Workers Planned: 4 - Params Evaluated: $3 - InitPlan 2 (returns $3) + InitPlan 2 -> Limit -> Gather Workers Planned: 4 -> Parallel Seq Scan on tenk1 tenk1_3 Filter: (fivethous =3D 1) -> Parallel Seq Scan on tenk1 tenk1_1 - Filter: (fivethous =3D $3) -(25 rows) + Filter: (fivethous =3D (InitPlan 2).col1) +(23 rows) = -- test interaction with SRFs SELECT * FROM information_schema.foreign_data_wrapper_options @@ -1182,10 +1179,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.tenthou= s -> Gather Merge Output: tenk1.two, tenk1.tenthous Workers Planned: 4 diff --git a/src/test/regress/expected/sqljson.out b/src/test/regress/expe= cted/sqljson.out index 5e7da96be5..cbf8542d8d 100644 --- a/src/test/regress/expected/sqljson.out +++ b/src/test/regress/expected/sqljson.out @@ -1064,8 +1064,8 @@ SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (N= ULL), (4)) foo(i) RETURNING QUERY PLAN = --------------------------------------------------------------------- Result - Output: $0 - InitPlan 1 (returns $0) + Output: (InitPlan 1).col1 + InitPlan 1 -> Aggregate Output: JSON_ARRAYAGG("*VALUES*".column1 RETURNING jsonb) -> Values Scan on "*VALUES*" diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/ex= pected/subselect.out index e41b728df8..29b11f11aa 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) =3D (SELECT f1, f2) AS eq FROM SUBSELECT_TBL; + QUERY PLAN = +----------------------------------------------------------------- + Seq Scan on public.subselect_tbl + Output: (((1 =3D (SubPlan 1).col1) AND (2 =3D (SubPlan 1).col2))) + SubPlan 1 + -> Result + Output: subselect_tbl.f1, subselect_tbl.f2 +(5 rows) + +SELECT ROW(1, 2) =3D (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) =3D (SELECT 3, 4) AS eq FROM SUBSELECT_TBL; + QUERY PLAN = +----------------------------------------------------------------- + Seq Scan on public.subselect_tbl + Output: ((1 =3D (InitPlan 1).col1) AND (2 =3D (InitPlan 1).col2)) + InitPlan 1 + -> Result + Output: 3, 4 +(5 rows) + +SELECT ROW(1, 2) =3D (SELECT 3, 4) AS eq FROM SUBSELECT_TBL; + eq = +---- + f + f + f + f + f + f + f + f +(8 rows) + +SELECT ROW(1, 2) =3D (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,18 +375,18 @@ explain (verbose, costs off) select '42' union all s= elect 43; -- check materialization of an initplan reference (bug #14524) explain (verbose, costs off) select 1 =3D all (select (select 1)); - QUERY PLAN = ------------------------------------ + QUERY PLAN = +------------------------------------------- Result - Output: (SubPlan 2) + Output: (ALL (1 =3D (SubPlan 2).col1)) SubPlan 2 -> Materialize - Output: ($0) - InitPlan 1 (returns $0) + Output: ((InitPlan 1).col1) + InitPlan 1 -> Result Output: 1 -> Result - Output: $0 + Output: (InitPlan 1).col1 (10 rows) = select 1 =3D all (select (select 1)); @@ -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,10 +891,10 @@ select * from outer_text where (f1, f2) not in (sele= ct * 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) + Output: (ANY ('foo'::text =3D (hashed SubPlan 1).col1)) SubPlan 1 -> Append -> Result @@ -864,10 +915,10 @@ select 'foo'::text in (select 'bar'::name union all = select 'bar'::name); -- explain (verbose, costs off) select row(row(row(1))) =3D any (select row(row(1))); - QUERY PLAN = -------------------------------------------- + QUERY PLAN = +-------------------------------------------------------- Result - Output: (SubPlan 1) + Output: (ANY ('("(1)")'::record =3D (SubPlan 1).col1)) SubPlan 1 -> Materialize Output: '("(1)")'::record @@ -907,10 +958,10 @@ language sql as 'select $1::text =3D $2'; create operator =3D (procedure=3Dbogus_int8_text_eq, leftarg=3Dint8, righ= targ=3Dtext); 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) + Filter: (ANY ((q1)::text =3D (hashed SubPlan 1).col1)) SubPlan 1 -> Seq Scan on inner_text (4 rows) @@ -928,10 +979,10 @@ create or replace function bogus_int8_text_eq(int8, = text) returns boolean language sql as 'select $1::text =3D $2 and $1::text =3D $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) + Filter: (ANY (((q1)::text =3D (hashed SubPlan 1).col1) AND ((q1)::text= =3D (hashed SubPlan 1).col1))) SubPlan 1 -> Seq Scan on inner_text (4 rows) @@ -949,10 +1000,10 @@ create or replace function bogus_int8_text_eq(int8,= text) returns boolean language sql as 'select $2 =3D $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) + Filter: (ANY ((SubPlan 1).col1 =3D (q1)::text)) SubPlan 1 -> Materialize -> Seq Scan on inner_text @@ -972,11 +1023,11 @@ 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 =3D t.unique2) or ten= < 0); - QUERY PLAN = --------------------------------------------------------------- + QUERY PLAN = = +-------------------------------------------------------------------------= - Aggregate -> Seq Scan on tenk1 t - Filter: ((hashed SubPlan 2) OR (ten < 0)) + Filter: ((ANY (unique2 =3D (hashed SubPlan 2).col1)) OR (ten < 0= )) SubPlan 2 -> 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 =3D= t.unique2) or ten < 0) Aggregate -> Bitmap Heap Scan on tenk1 t Recheck Cond: (thousand =3D 1) - Filter: ((SubPlan 1) OR (ten < 0)) + Filter: (EXISTS(SubPlan 1) OR (ten < 0)) -> Bitmap Index Scan on tenk1_thous_tenthous Index Cond: (thousand =3D 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 =3D 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,7 +1085,7 @@ select * from exists_tbl t1 -> Seq Scan on exists_tbl_def t2_2 Filter: (t1_1.c1 =3D c2) -> Seq Scan on exists_tbl_def t1_2 - Filter: ((hashed SubPlan 2) OR (c3 < 0)) + Filter: ((ANY (c1 =3D (hashed SubPlan 2).col1)) OR (c3 < 0)) SubPlan 2 -> Append -> Seq Scan on exists_tbl_null t2_4 @@ -1071,14 +1122,14 @@ where a.thousand =3D 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 - InitPlan 1 (returns $0) + Output: (InitPlan 1).col1, (InitPlan 2).col1 + InitPlan 1 -> Result Output: now() - InitPlan 2 (returns $1) + InitPlan 2 -> Result Output: now() (8 rows) @@ -1086,13 +1137,13 @@ explain (verbose, costs off) explain (verbose, costs off) select x, x from (select (select random()) as x from (values(1),(2)) v(y)) ss; - QUERY PLAN = ----------------------------------- + QUERY PLAN = +----------------------------------- Subquery Scan on ss Output: ss.x, ss.x -> Values Scan on "*VALUES*" - Output: $0 - InitPlan 1 (returns $0) + Output: (InitPlan 1).col1 + InitPlan 1 -> Result Output: random() (7 rows) @@ -1143,14 +1194,14 @@ where o.ten =3D 0; = QUERY PLAN = = -------------------------------------------------------------------------= --------------------------------------------------------------------------= ------------------------------------------ Aggregate - Output: sum((((hashed SubPlan 1)))::integer) + Output: sum((((ANY (i.ten =3D (hashed SubPlan 1).col1))))::integer) -> Nested Loop - Output: ((hashed SubPlan 1)) + Output: ((ANY (i.ten =3D (hashed SubPlan 1).col1))) -> Seq Scan on public.onek o Output: o.unique1, o.unique2, o.two, o.four, o.ten, o.twen= ty, o.hundred, o.thousand, o.twothousand, o.fivethous, o.tenthous, o.odd, = o.even, o.stringu1, o.stringu2, o.string4 Filter: (o.ten =3D 0) -> Index Scan using onek_unique1 on public.onek i - Output: (hashed SubPlan 1), random() + Output: (ANY (i.ten =3D (hashed SubPlan 1).col1)), random(= ) Index Cond: (i.unique1 =3D o.unique1) SubPlan 1 -> Seq Scan on public.int4_tbl @@ -1346,7 +1397,7 @@ 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 =3D b.ten) + Join Filter: (CASE WHEN (ANY (int4_tbl.f1 =3D (hashed SubPlan 1).col1)= ) THEN int4_tbl.f1 ELSE NULL::integer END =3D b.ten) -> Seq Scan on public.int4_tbl Output: int4_tbl.f1 -> Seq Scan on public.tenk1 b @@ -1945,10 +1996,10 @@ select * from tenk1 A where exists (select 1 from tenk2 B where A.hundred in (select C.hundred FROM tenk2 C WHERE c.odd =3D b.odd)); - QUERY PLAN = ---------------------------------- + QUERY PLAN = +----------------------------------------------------- Nested Loop Semi Join - Join Filter: (SubPlan 1) + Join Filter: (ANY (a.hundred =3D (SubPlan 1).col1)) -> Seq Scan on tenk1 a -> Materialize -> Seq Scan on tenk2 b @@ -1962,10 +2013,10 @@ WHERE c.odd =3D 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 =3D b.odd); - QUERY PLAN = ---------------------------------- + QUERY PLAN = +----------------------------------------------------- Nested Loop Left Join - Join Filter: (SubPlan 1) + Join Filter: (ANY (a.hundred =3D (SubPlan 1).col1)) -> Seq Scan on tenk1 a -> Materialize -> Seq Scan on tenk2 b @@ -1979,10 +2030,10 @@ ON A.hundred in (SELECT c.hundred FROM tenk2 C WHE= RE c.odd =3D 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 =3D a.odd); - QUERY PLAN = ---------------------------------- + QUERY PLAN = +----------------------------------------------------- Nested Loop Left Join - Join Filter: (SubPlan 1) + Join Filter: (ANY (b.hundred =3D (SubPlan 1).col1)) -> Seq Scan on tenk1 a -> Materialize -> Seq Scan on tenk2 b @@ -2045,7 +2096,7 @@ ON B.hundred in (SELECT min(c.hundred) FROM tenk2 C = WHERE c.odd =3D b.odd); -> Subquery Scan on "ANY_subquery" Filter: (b.hundred =3D "ANY_subquery".min) -> Result - InitPlan 1 (returns $1) + InitPlan 1 -> Limit -> Index Scan using tenk2_hundr= ed on tenk2 c Index Cond: (hundred IS NO= T NULL) diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regr= ess/expected/updatable_views.out index 1062c341d8..713bf84c70 100644 --- a/src/test/regress/expected/updatable_views.out +++ b/src/test/regress/expected/updatable_views.out @@ -3038,18 +3038,18 @@ EXPLAIN (costs off) INSERT INTO rw_view1 VALUES (2= , 'New row 2'); QUERY PLAN = ----------------------------------------------------------- Insert on base_tbl - InitPlan 1 (returns $0) + InitPlan 1 -> Index Only Scan using base_tbl_pkey on base_tbl t Index Cond: (id =3D 2) -> Result - One-Time Filter: ($0 IS NOT TRUE) + One-Time Filter: ((InitPlan 1).col1 IS NOT TRUE) = Update on base_tbl - InitPlan 1 (returns $0) + InitPlan 1 -> Index Only Scan using base_tbl_pkey on base_tbl t Index Cond: (id =3D 2) -> Result - One-Time Filter: $0 + One-Time Filter: (InitPlan 1).col1 -> Index Scan using base_tbl_pkey on base_tbl Index Cond: (id =3D 2) (15 rows) @@ -3108,8 +3108,8 @@ SELECT * FROM v1 WHERE a=3D8; = EXPLAIN (VERBOSE, COSTS OFF) UPDATE v1 SET a=3D100 WHERE snoop(a) AND leakproof(a) AND a < 7 AND a !=3D= 6; - QUERY PLAN = = --------------------------------------------------------------------------= ---------------------------- + QUERY PLAN = = +-------------------------------------------------------------------------= ---------------------------------- Update on public.t1 Update on public.t1 t1_1 Update on public.t11 t1_2 @@ -3121,7 +3121,7 @@ UPDATE v1 SET a=3D100 WHERE snoop(a) AND leakproof(a= ) AND a < 7 AND a !=3D 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 sno= op(t1_1.a) AND leakproof(t1_1.a)) SubPlan 1 -> Append -> Seq Scan on public.t12 t12_1 @@ -3131,15 +3131,15 @@ UPDATE v1 SET a=3D100 WHERE snoop(a) AND leakproof= (a) AND a < 7 AND a !=3D 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 sno= op(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 sno= op(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 sno= op(t1_4.a) AND leakproof(t1_4.a)) (30 rows) = UPDATE v1 SET a=3D100 WHERE snoop(a) AND leakproof(a) AND a < 7 AND a !=3D= 6; @@ -3155,8 +3155,8 @@ SELECT * FROM t1 WHERE a=3D100; -- Nothing should ha= ve been changed to 100 = EXPLAIN (VERBOSE, COSTS OFF) UPDATE v1 SET a=3Da+1 WHERE snoop(a) AND leakproof(a) AND a =3D 8; - QUERY PLAN = = --------------------------------------------------------------------------= ---------- + QUERY PLAN = = +-------------------------------------------------------------------------= ---------------- Update on public.t1 Update on public.t1 t1_1 Update on public.t11 t1_2 @@ -3168,7 +3168,7 @@ UPDATE v1 SET a=3Da+1 WHERE snoop(a) AND leakproof(a= ) AND a =3D 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 =3D 8)) - Filter: ((SubPlan 1) AND snoop(t1_1.a) AND leakproof= (t1_1.a)) + Filter: (EXISTS(SubPlan 1) AND snoop(t1_1.a) AND lea= kproof(t1_1.a)) SubPlan 1 -> Append -> Seq Scan on public.t12 t12_1 @@ -3178,15 +3178,15 @@ UPDATE v1 SET a=3Da+1 WHERE snoop(a) AND leakproof= (a) AND a =3D 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 =3D 8)) - Filter: ((SubPlan 1) AND snoop(t1_2.a) AND leakproof= (t1_2.a)) + Filter: (EXISTS(SubPlan 1) AND snoop(t1_2.a) AND lea= kproof(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 =3D 8)) - Filter: ((SubPlan 1) AND snoop(t1_3.a) AND leakproof= (t1_3.a)) + Filter: (EXISTS(SubPlan 1) AND snoop(t1_3.a) AND lea= kproof(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 =3D 8)) - Filter: ((SubPlan 1) AND snoop(t1_4.a) AND leakproof= (t1_4.a)) + Filter: (EXISTS(SubPlan 1) AND snoop(t1_4.a) AND lea= kproof(t1_4.a)) (30 rows) = UPDATE v1 SET a=3Da+1 WHERE snoop(a) AND leakproof(a) AND a =3D 8; @@ -3370,10 +3370,10 @@ CREATE RULE v1_upd_rule AS ON UPDATE TO v1 DO INST= EAD CREATE VIEW v2 WITH (security_barrier =3D true) AS SELECT * FROM v1 WHERE EXISTS (SELECT 1); EXPLAIN (COSTS OFF) UPDATE v2 SET a =3D 1; - QUERY PLAN = ---------------------------------------------------- + QUERY PLAN = +-------------------------------------------------------------- Update on t1 - InitPlan 1 (returns $0) + InitPlan 1 -> Result -> Merge Join Merge Cond: (t1.a =3D v1.a) @@ -3384,7 +3384,7 @@ EXPLAIN (COSTS OFF) UPDATE v2 SET a =3D 1; Sort Key: v1.a -> Subquery Scan on v1 -> Result - One-Time Filter: $0 + One-Time Filter: (InitPlan 1).col1 -> Seq Scan on t1 t1_1 (14 rows) = diff --git a/src/test/regress/expected/update.out b/src/test/regress/expec= ted/update.out index cb0b033da6..1b27d132d7 100644 --- a/src/test/regress/expected/update.out +++ b/src/test/regress/expected/update.out @@ -178,15 +178,15 @@ EXPLAIN (VERBOSE, COSTS OFF) UPDATE update_test t SET (a, b) =3D (SELECT b, a FROM update_test s WHERE s.a =3D t.a) WHERE CURRENT_USER =3D SESSION_USER; - QUERY PLAN = -------------------------------------------------------------- + QUERY PLAN = = +-------------------------------------------------------------------------= ------- Update on public.update_test t -> Result - Output: $1, $2, (SubPlan 1 (returns $1,$2)), t.ctid + Output: (SubPlan 1).col1, (SubPlan 1).col2, (rescan SubPlan 1), = t.ctid One-Time Filter: (CURRENT_USER =3D SESSION_USER) -> Seq Scan on public.update_test t Output: t.a, t.ctid - SubPlan 1 (returns $1,$2) + SubPlan 1 -> Seq Scan on public.update_test s Output: s.b, s.a Filter: (s.a =3D t.a) diff --git a/src/test/regress/expected/window.out b/src/test/regress/expec= ted/window.out index 60de2cbf96..e46710cf31 100644 --- a/src/test/regress/expected/window.out +++ b/src/test/regress/expected/window.out @@ -4279,7 +4279,7 @@ WHERE c =3D 1; Subquery Scan on emp Filter: (emp.c =3D 1) -> WindowAgg - InitPlan 1 (returns $0) + InitPlan 1 -> Result -> Sort Sort Key: empsalary.empno DESC diff --git a/src/test/regress/expected/with.out b/src/test/regress/expecte= d/with.out index 6de347b528..7bef181d78 100644 --- a/src/test/regress/expected/with.out +++ b/src/test/regress/expected/with.out @@ -3100,7 +3100,7 @@ WHEN NOT MATCHED THEN INSERT VALUES(o.k, o.v); CTE cte_init -> Result Output: 1, 'cte_init val'::text - InitPlan 2 (returns $1) + InitPlan 2 -> Limit Output: ((cte_init.b || ' merge update'::text)) -> CTE Scan on cte_init @@ -3143,11 +3143,11 @@ WHEN NOT MATCHED THEN INSERT VALUES(o.a, o.b || (S= ELECT merge_source_cte.*::text CTE merge_source_cte -> Result Output: 15, 'merge_source_cte val'::text - InitPlan 2 (returns $1) + InitPlan 2 -> CTE Scan on merge_source_cte merge_source_cte_1 Output: ((merge_source_cte_1.b || (merge_source_cte_1.*)::text= ) || ' merge update'::text) Filter: (merge_source_cte_1.a =3D 15) - InitPlan 3 (returns $2) + InitPlan 3 -> CTE Scan on merge_source_cte merge_source_cte_2 Output: ((merge_source_cte_2.*)::text || ' merge insert'::text= ) -> Hash Right Join diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/sub= select.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) =3D (SELECT f1, f2) AS eq FROM SUBSELECT_TBL; + +SELECT ROW(1, 2) =3D (SELECT f1, f2) AS eq FROM SUBSELECT_TBL; + +EXPLAIN (VERBOSE, COSTS OFF) +SELECT ROW(1, 2) =3D (SELECT 3, 4) AS eq FROM SUBSELECT_TBL; + +SELECT ROW(1, 2) =3D (SELECT 3, 4) AS eq FROM SUBSELECT_TBL; + +SELECT ROW(1, 2) =3D (SELECT f1, f2 FROM SUBSELECT_TBL); -- error + -- Subselects without aliases = SELECT count FROM (SELECT COUNT(DISTINCT name) FROM road); ------- =_aaaaaaaaaa0--