diff options
55 files changed, 1775 insertions, 859 deletions
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index 6dc04e916dc..f2f8130af87 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -3175,13 +3175,13 @@ select sum(c1) from ft1 group by c2 having avg(c1 * (random() <= 1)::int) > 100 -- of an initplan) can be trouble, per bug #15781 explain (verbose, costs off) select exists(select 1 from pg_enum), sum(c1) from ft1; - QUERY PLAN --------------------------------------------------- + QUERY PLAN +--------------------------------------------------- Foreign Scan - Output: (InitPlan 1).col1, (sum(ft1.c1)) + Output: (InitPlan exists_1).col1, (sum(ft1.c1)) Relations: Aggregate on (public.ft1) Remote SQL: SELECT sum("C 1") FROM "S 1"."T 1" - InitPlan 1 + InitPlan exists_1 -> Seq Scan on pg_catalog.pg_enum (6 rows) @@ -3196,8 +3196,8 @@ select exists(select 1 from pg_enum), sum(c1) from ft1 group by 1; QUERY PLAN --------------------------------------------------- GroupAggregate - Output: (InitPlan 1).col1, sum(ft1.c1) - InitPlan 1 + Output: (InitPlan exists_1).col1, sum(ft1.c1) + InitPlan exists_1 -> Seq Scan on pg_catalog.pg_enum -> Foreign Scan on public.ft1 Output: ft1.c1 @@ -3356,15 +3356,15 @@ select distinct (select count(*) filter (where t2.c2 = 6 and t2.c1 < 10) from ft QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------ Unique - Output: ((SubPlan 1)) + Output: ((SubPlan expr_1)) -> Sort - Output: ((SubPlan 1)) - Sort Key: ((SubPlan 1)) + Output: ((SubPlan expr_1)) + Sort Key: ((SubPlan expr_1)) -> Foreign Scan - Output: (SubPlan 1) + Output: (SubPlan expr_1) Relations: Aggregate on (public.ft2 t2) Remote SQL: SELECT count(*) FILTER (WHERE ((c2 = 6) AND ("C 1" < 10))) FROM "S 1"."T 1" WHERE (((c2 % 6) = 0)) - SubPlan 1 + SubPlan expr_1 -> Foreign Scan on public.ft1 t1 Output: (count(*) FILTER (WHERE ((t2.c2 = 6) AND (t2.c1 < 10)))) Remote SQL: SELECT NULL FROM "S 1"."T 1" WHERE (("C 1" = 6)) @@ -3382,14 +3382,14 @@ select distinct (select count(t1.c1) filter (where t2.c2 = 6 and t2.c1 < 10) fro QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------ Unique - Output: ((SubPlan 1)) + Output: ((SubPlan expr_1)) -> Sort - Output: ((SubPlan 1)) - Sort Key: ((SubPlan 1)) + Output: ((SubPlan expr_1)) + Sort Key: ((SubPlan expr_1)) -> Foreign Scan on public.ft2 t2 - Output: (SubPlan 1) + Output: (SubPlan expr_1) Remote SQL: SELECT "C 1", c2 FROM "S 1"."T 1" WHERE (((c2 % 6) = 0)) - SubPlan 1 + SubPlan expr_1 -> Foreign Scan Output: (count(t1.c1) FILTER (WHERE ((t2.c2 = 6) AND (t2.c1 < 10)))) Relations: Aggregate on (public.ft1 t1) @@ -3421,14 +3421,14 @@ select sum(c1) filter (where (c1 / c1) * random() <= 1) from ft1 group by c2 ord explain (verbose, costs off) select sum(c2) filter (where c2 in (select c2 from ft1 where c2 < 5)) from ft1; - QUERY PLAN -------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------- Aggregate - Output: sum(ft1.c2) FILTER (WHERE (ANY (ft1.c2 = (hashed SubPlan 1).col1))) + Output: sum(ft1.c2) FILTER (WHERE (ANY (ft1.c2 = (hashed SubPlan any_1).col1))) -> Foreign Scan on public.ft1 Output: ft1.c2 Remote SQL: SELECT c2 FROM "S 1"."T 1" - SubPlan 1 + SubPlan any_1 -> Foreign Scan on public.ft1 ft1_1 Output: ft1_1.c2 Remote SQL: SELECT c2 FROM "S 1"."T 1" WHERE ((c2 < 5)) @@ -6444,14 +6444,14 @@ UPDATE ft2 AS target SET (c2, c7) = ( FROM ft2 AS src WHERE target.c1 = src.c1 ) WHERE c1 > 1100; - QUERY PLAN ------------------------------------------------------------------------------------------------------------------------ + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------- Update on public.ft2 target Remote SQL: UPDATE "S 1"."T 1" SET c2 = $2, c7 = $3 WHERE ctid = $1 -> Foreign Scan on public.ft2 target - Output: (SubPlan 1).col1, (SubPlan 1).col2, (rescan SubPlan 1), target.ctid, target.* + Output: (SubPlan multiexpr_1).col1, (SubPlan multiexpr_1).col2, (rescan SubPlan multiexpr_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 + SubPlan multiexpr_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::integer = "C 1")) @@ -12132,12 +12132,12 @@ INSERT INTO local_tbl VALUES (1505, 505, 'foo'); ANALYZE local_tbl; EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM local_tbl t1 LEFT JOIN (SELECT *, (SELECT count(*) FROM async_pt WHERE a < 3000) FROM async_pt WHERE a < 3000) t2 ON t1.a = t2.a; - QUERY PLAN ----------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------ Nested Loop Left Join - Output: t1.a, t1.b, t1.c, async_pt.a, async_pt.b, async_pt.c, ((InitPlan 1).col1) + Output: t1.a, t1.b, t1.c, async_pt.a, async_pt.b, async_pt.c, ((InitPlan expr_1).col1) Join Filter: (t1.a = async_pt.a) - InitPlan 1 + InitPlan expr_1 -> Aggregate Output: count(*) -> Append @@ -12149,10 +12149,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, (InitPlan 1).col1 + Output: async_pt_1.a, async_pt_1.b, async_pt_1.c, (InitPlan expr_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, (InitPlan 1).col1 + Output: async_pt_2.a, async_pt_2.b, async_pt_2.c, (InitPlan expr_1).col1 Remote SQL: SELECT a, b, c FROM public.base_tbl2 WHERE ((a < 3000)) (20 rows) @@ -12163,7 +12163,7 @@ SELECT * FROM local_tbl t1 LEFT JOIN (SELECT *, (SELECT count(*) FROM async_pt W Nested Loop Left Join (actual rows=1.00 loops=1) Join Filter: (t1.a = async_pt.a) Rows Removed by Join Filter: 399 - InitPlan 1 + InitPlan expr_1 -> Aggregate (actual rows=1.00 loops=1) -> Append (actual rows=400.00 loops=1) -> Async Foreign Scan on async_p1 async_pt_4 (actual rows=200.00 loops=1) @@ -12386,12 +12386,12 @@ CREATE FOREIGN TABLE foreign_tbl2 () INHERITS (foreign_tbl) SERVER loopback OPTIONS (table_name 'base_tbl'); EXPLAIN (VERBOSE, COSTS OFF) SELECT a FROM base_tbl WHERE (a, random() > 0) IN (SELECT a, random() > 0 FROM foreign_tbl); - QUERY PLAN ---------------------------------------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------- Seq Scan on public.base_tbl Output: base_tbl.a - Filter: (ANY ((base_tbl.a = (SubPlan 1).col1) AND ((random() > '0'::double precision) = (SubPlan 1).col2))) - SubPlan 1 + Filter: (ANY ((base_tbl.a = (SubPlan any_1).col1) AND ((random() > '0'::double precision) = (SubPlan any_1).col2))) + SubPlan any_1 -> Result Output: base_tbl.a, (random() > '0'::double precision) -> Append diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index 786aa2ac5f6..6e3aac3d815 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -5698,7 +5698,7 @@ FROM pg_stat_get_backend_idset() AS backendid; </para> <para> Total time spent sleeping due to cost-based delay (see - <xref linkend="runtime-config-resource-vacuum-cost"/>, in milliseconds + <xref linkend="runtime-config-resource-vacuum-cost"/>), in milliseconds (if <xref linkend="guc-track-cost-delay-timing"/> is enabled, otherwise zero). </para></entry> diff --git a/src/backend/access/gin/gininsert.c b/src/backend/access/gin/gininsert.c index e9d4b27427e..1d3ab22556d 100644 --- a/src/backend/access/gin/gininsert.c +++ b/src/backend/access/gin/gininsert.c @@ -218,7 +218,8 @@ addItemPointersToLeafTuple(GinState *ginstate, ItemPointerData *newItems, *oldItems; int oldNPosting, - newNPosting; + newNPosting, + nwritten; GinPostingList *compressedList; Assert(!GinIsPostingTree(old)); @@ -235,18 +236,19 @@ addItemPointersToLeafTuple(GinState *ginstate, /* Compress the posting list, and try to a build tuple with room for it */ res = NULL; - compressedList = ginCompressPostingList(newItems, newNPosting, GinMaxItemSize, - NULL); - pfree(newItems); - if (compressedList) + compressedList = ginCompressPostingList(newItems, newNPosting, GinMaxItemSize, &nwritten); + if (nwritten == newNPosting) { res = GinFormTuple(ginstate, attnum, key, category, (char *) compressedList, SizeOfGinPostingList(compressedList), newNPosting, false); - pfree(compressedList); } + + pfree(newItems); + pfree(compressedList); + if (!res) { /* posting list would be too big, convert to posting tree */ @@ -293,17 +295,19 @@ buildFreshLeafTuple(GinState *ginstate, { IndexTuple res = NULL; GinPostingList *compressedList; + int nwritten; /* try to build a posting list tuple with all the items */ - compressedList = ginCompressPostingList(items, nitem, GinMaxItemSize, NULL); - if (compressedList) + compressedList = ginCompressPostingList(items, nitem, GinMaxItemSize, &nwritten); + if (nwritten == nitem) { res = GinFormTuple(ginstate, attnum, key, category, (char *) compressedList, SizeOfGinPostingList(compressedList), nitem, false); - pfree(compressedList); } + pfree(compressedList); + if (!res) { /* posting list would be too big, build posting tree */ diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index 207f86f1d39..06191cd8a85 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -4901,6 +4901,7 @@ ExplainSubPlans(List *plans, List *ancestors, { SubPlanState *sps = (SubPlanState *) lfirst(lst); SubPlan *sp = sps->subplan; + char *cooked_plan_name; /* * There can be multiple SubPlan nodes referencing the same physical @@ -4924,8 +4925,20 @@ ExplainSubPlans(List *plans, List *ancestors, */ ancestors = lcons(sp, ancestors); + /* + * The plan has a name like exists_1 or rowcompare_2, but here we want + * to prefix that with CTE, InitPlan, or SubPlan, as appropriate, for + * display purposes. + */ + if (sp->subLinkType == CTE_SUBLINK) + cooked_plan_name = psprintf("CTE %s", sp->plan_name); + else if (sp->isInitPlan) + cooked_plan_name = psprintf("InitPlan %s", sp->plan_name); + else + cooked_plan_name = psprintf("SubPlan %s", sp->plan_name); + ExplainNode(sps->planstate, ancestors, - relationship, sp->plan_name, es); + relationship, cooked_plan_name, es); ancestors = list_delete_first(ancestors); } diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c index cf667c81211..0698aae37a7 100644 --- a/src/backend/executor/nodeWindowAgg.c +++ b/src/backend/executor/nodeWindowAgg.c @@ -1501,8 +1501,9 @@ row_is_in_frame(WindowObject winobj, int64 pos, TupleTableSlot *slot, /* following row that is not peer is out of frame */ if (pos > winstate->currentpos) { - if (fetch_tuple) - window_gettupleslot(winobj, pos, slot); + if (fetch_tuple) /* need to fetch tuple? */ + if (!window_gettupleslot(winobj, pos, slot)) + return -1; if (!are_peers(winstate, slot, winstate->ss.ss_ScanTupleSlot)) return -1; } @@ -3721,6 +3722,7 @@ WinGetFuncArgInPartition(WindowObject winobj, int argno, int notnull_offset; int notnull_relpos; int forward; + bool myisout; Assert(WindowObjectIsValid(winobj)); winstate = winobj->winstate; @@ -3759,63 +3761,60 @@ WinGetFuncArgInPartition(WindowObject winobj, int argno, if (!null_treatment) /* IGNORE NULLS is not specified */ { + /* get tupple and evaluate in a partition */ datum = gettuple_eval_partition(winobj, argno, - abs_pos, isnull, isout); - if (!*isout && set_mark) + abs_pos, isnull, &myisout); + if (!myisout && set_mark) WinSetMarkPosition(winobj, abs_pos); + if (isout) + *isout = myisout; return datum; } + myisout = false; + datum = 0; + /* * Get the next nonnull value in the partition, moving forward or backward * until we find a value or reach the partition's end. */ do { + int nn_info; /* NOT NULL info */ + abs_pos += forward; - if (abs_pos < 0) - { - /* out of partition */ - if (isout) - *isout = true; - *isnull = true; - datum = 0; + if (abs_pos < 0) /* apparently out of partition */ break; - } - switch (get_notnull_info(winobj, abs_pos)) + /* check NOT NULL cached info */ + nn_info = get_notnull_info(winobj, abs_pos); + if (nn_info == NN_NOTNULL) /* this row is known to be NOT NULL */ + notnull_offset++; + + else if (nn_info == NN_NULL) /* this row is known to be NULL */ + continue; /* keep on moving forward or backward */ + + else /* need to check NULL or not */ { - case NN_NOTNULL: /* this row is known to be NOT NULL */ - notnull_offset++; - if (notnull_offset >= notnull_relpos) - { - /* prepare to exit this loop */ - datum = gettuple_eval_partition(winobj, argno, - abs_pos, isnull, isout); - } - break; - case NN_NULL: /* this row is known to be NULL */ - if (isout) - *isout = false; - *isnull = true; - datum = 0; - break; - default: /* need to check NULL or not */ - datum = gettuple_eval_partition(winobj, argno, - abs_pos, isnull, isout); - if (*isout) /* out of partition? */ - return datum; - - if (!*isnull) - notnull_offset++; - /* record the row status */ - put_notnull_info(winobj, abs_pos, *isnull); + /* get tupple and evaluate in a partition */ + datum = gettuple_eval_partition(winobj, argno, + abs_pos, isnull, &myisout); + if (myisout) /* out of partition? */ break; + if (!*isnull) + notnull_offset++; + /* record the row status */ + put_notnull_info(winobj, abs_pos, *isnull); } } while (notnull_offset < notnull_relpos); - if (!*isout && set_mark) + /* get tupple and evaluate in a partition */ + datum = gettuple_eval_partition(winobj, argno, + abs_pos, isnull, &myisout); + if (!myisout && set_mark) WinSetMarkPosition(winobj, abs_pos); + if (isout) + *isout = myisout; return datum; } diff --git a/src/backend/optimizer/geqo/geqo_eval.c b/src/backend/optimizer/geqo/geqo_eval.c index f07d1dc8ac6..7fcb1aa70d1 100644 --- a/src/backend/optimizer/geqo/geqo_eval.c +++ b/src/backend/optimizer/geqo/geqo_eval.c @@ -162,7 +162,7 @@ geqo_eval(PlannerInfo *root, Gene *tour, int num_gene) RelOptInfo * gimme_tree(PlannerInfo *root, Gene *tour, int num_gene) { - GeqoPrivateData *private = (GeqoPrivateData *) root->join_search_private; + GeqoPrivateData *private = GetGeqoPrivateData(root); List *clumps; int rel_count; diff --git a/src/backend/optimizer/geqo/geqo_main.c b/src/backend/optimizer/geqo/geqo_main.c index 38402ce58db..0064556087a 100644 --- a/src/backend/optimizer/geqo/geqo_main.c +++ b/src/backend/optimizer/geqo/geqo_main.c @@ -47,6 +47,8 @@ int Geqo_generations; double Geqo_selection_bias; double Geqo_seed; +/* GEQO is treated as an in-core planner extension */ +int Geqo_planner_extension_id = -1; static int gimme_pool_size(int nr_rel); static int gimme_number_generations(int pool_size); @@ -98,10 +100,16 @@ geqo(PlannerInfo *root, int number_of_rels, List *initial_rels) int mutations = 0; #endif + if (Geqo_planner_extension_id < 0) + Geqo_planner_extension_id = GetPlannerExtensionId("geqo"); + /* set up private information */ - root->join_search_private = &private; + SetPlannerInfoExtensionState(root, Geqo_planner_extension_id, &private); private.initial_rels = initial_rels; +/* inform core planner that we may replan */ + root->assumeReplanning = true; + /* initialize private number generator */ geqo_set_seed(root, Geqo_seed); @@ -304,7 +312,7 @@ geqo(PlannerInfo *root, int number_of_rels, List *initial_rels) free_pool(root, pool); /* ... clear root pointer to our private storage */ - root->join_search_private = NULL; + SetPlannerInfoExtensionState(root, Geqo_planner_extension_id, NULL); return best_rel; } diff --git a/src/backend/optimizer/geqo/geqo_random.c b/src/backend/optimizer/geqo/geqo_random.c index 6c7a411f69f..46d28baa2e6 100644 --- a/src/backend/optimizer/geqo/geqo_random.c +++ b/src/backend/optimizer/geqo/geqo_random.c @@ -15,11 +15,10 @@ #include "optimizer/geqo_random.h" - void geqo_set_seed(PlannerInfo *root, double seed) { - GeqoPrivateData *private = (GeqoPrivateData *) root->join_search_private; + GeqoPrivateData *private = GetGeqoPrivateData(root); pg_prng_fseed(&private->random_state, seed); } @@ -27,7 +26,7 @@ geqo_set_seed(PlannerInfo *root, double seed) double geqo_rand(PlannerInfo *root) { - GeqoPrivateData *private = (GeqoPrivateData *) root->join_search_private; + GeqoPrivateData *private = GetGeqoPrivateData(root); return pg_prng_double(&private->random_state); } @@ -35,7 +34,7 @@ geqo_rand(PlannerInfo *root) int geqo_randint(PlannerInfo *root, int upper, int lower) { - GeqoPrivateData *private = (GeqoPrivateData *) root->join_search_private; + GeqoPrivateData *private = GetGeqoPrivateData(root); /* * In current usage, "lower" is never negative so we can just use diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c index d7ff36d89be..1f82239b4e0 100644 --- a/src/backend/optimizer/path/allpaths.c +++ b/src/backend/optimizer/path/allpaths.c @@ -2529,6 +2529,7 @@ set_subquery_pathlist(PlannerInfo *root, RelOptInfo *rel, RelOptInfo *sub_final_rel; Bitmapset *run_cond_attrs = NULL; ListCell *lc; + char *plan_name; /* * Must copy the Query so that planning doesn't mess up the RTE contents @@ -2671,8 +2672,9 @@ set_subquery_pathlist(PlannerInfo *root, RelOptInfo *rel, Assert(root->plan_params == NIL); /* Generate a subroot and Paths for the subquery */ - rel->subroot = subquery_planner(root->glob, subquery, root, false, - tuple_fraction, NULL); + plan_name = choose_plan_name(root->glob, rte->eref->aliasname, false); + rel->subroot = subquery_planner(root->glob, subquery, plan_name, + root, false, tuple_fraction, NULL); /* Isolate the params needed by this specific subplan */ rel->subplan_params = root->plan_params; diff --git a/src/backend/optimizer/plan/analyzejoins.c b/src/backend/optimizer/plan/analyzejoins.c index 2a3dea88a94..6a3c030e8ef 100644 --- a/src/backend/optimizer/plan/analyzejoins.c +++ b/src/backend/optimizer/plan/analyzejoins.c @@ -1425,17 +1425,14 @@ innerrel_is_unique_ext(PlannerInfo *root, * * However, in normal planning mode, caching this knowledge is totally * pointless; it won't be queried again, because we build up joinrels - * from smaller to larger. It is useful in GEQO mode, where the - * knowledge can be carried across successive planning attempts; and - * it's likely to be useful when using join-search plugins, too. Hence - * cache when join_search_private is non-NULL. (Yeah, that's a hack, - * but it seems reasonable.) + * from smaller to larger. It's only useful when using GEQO or + * another planner extension that attempts planning multiple times. * * Also, allow callers to override that heuristic and force caching; * that's useful for reduce_unique_semijoins, which calls here before * the normal join search starts. */ - if (force_cache || root->join_search_private) + if (force_cache || root->assumeReplanning) { old_context = MemoryContextSwitchTo(root->planner_cxt); innerrel->non_unique_for_rels = diff --git a/src/backend/optimizer/plan/planagg.c b/src/backend/optimizer/plan/planagg.c index 2ef0bb7f663..a2ac58d246e 100644 --- a/src/backend/optimizer/plan/planagg.c +++ b/src/backend/optimizer/plan/planagg.c @@ -38,6 +38,7 @@ #include "optimizer/pathnode.h" #include "optimizer/paths.h" #include "optimizer/planmain.h" +#include "optimizer/planner.h" #include "optimizer/subselect.h" #include "optimizer/tlist.h" #include "parser/parse_clause.h" @@ -339,6 +340,8 @@ build_minmax_path(PlannerInfo *root, MinMaxAggInfo *mminfo, memcpy(subroot, root, sizeof(PlannerInfo)); subroot->query_level++; subroot->parent_root = root; + subroot->plan_name = choose_plan_name(root->glob, "minmax", true); + /* reset subplan-related stuff */ subroot->plan_params = NIL; subroot->outer_params = NULL; diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c index 41bd8353430..0c9397a36c3 100644 --- a/src/backend/optimizer/plan/planner.c +++ b/src/backend/optimizer/plan/planner.c @@ -439,7 +439,8 @@ standard_planner(Query *parse, const char *query_string, int cursorOptions, } /* primary planning entry point (may recurse for subqueries) */ - root = subquery_planner(glob, parse, NULL, false, tuple_fraction, NULL); + root = subquery_planner(glob, parse, NULL, NULL, false, tuple_fraction, + NULL); /* Select best Path and turn it into a Plan */ final_rel = fetch_upper_rel(root, UPPERREL_FINAL, NULL); @@ -630,6 +631,7 @@ standard_planner(Query *parse, const char *query_string, int cursorOptions, * * glob is the global state for the current planner run. * parse is the querytree produced by the parser & rewriter. + * plan_name is the name to assign to this subplan (NULL at the top level). * parent_root is the immediate parent Query's info (NULL at the top level). * hasRecursion is true if this is a recursive WITH query. * tuple_fraction is the fraction of tuples we expect will be retrieved. @@ -656,9 +658,9 @@ standard_planner(Query *parse, const char *query_string, int cursorOptions, *-------------------- */ PlannerInfo * -subquery_planner(PlannerGlobal *glob, Query *parse, PlannerInfo *parent_root, - bool hasRecursion, double tuple_fraction, - SetOperationStmt *setops) +subquery_planner(PlannerGlobal *glob, Query *parse, char *plan_name, + PlannerInfo *parent_root, bool hasRecursion, + double tuple_fraction, SetOperationStmt *setops) { PlannerInfo *root; List *newWithCheckOptions; @@ -673,6 +675,7 @@ subquery_planner(PlannerGlobal *glob, Query *parse, PlannerInfo *parent_root, root->parse = parse; root->glob = glob; root->query_level = parent_root ? parent_root->query_level + 1 : 1; + root->plan_name = plan_name; root->parent_root = parent_root; root->plan_params = NIL; root->outer_params = NULL; @@ -703,6 +706,7 @@ subquery_planner(PlannerGlobal *glob, Query *parse, PlannerInfo *parent_root, root->hasAlternativeSubPlans = false; root->placeholdersFrozen = false; root->hasRecursion = hasRecursion; + root->assumeReplanning = false; if (hasRecursion) root->wt_param_id = assign_special_exec_param(root); else @@ -8833,3 +8837,70 @@ create_partial_unique_paths(PlannerInfo *root, RelOptInfo *input_rel, sjinfo, unique_rel); } } + +/* + * Choose a unique name for some subroot. + * + * Modifies glob->subplanNames to track names already used. + */ +char * +choose_plan_name(PlannerGlobal *glob, const char *name, bool always_number) +{ + unsigned n; + + /* + * If a numeric suffix is not required, then search the list of + * previously-assigned names for a match. If none is found, then we can + * use the provided name without modification. + */ + if (!always_number) + { + bool found = false; + + foreach_ptr(char, subplan_name, glob->subplanNames) + { + if (strcmp(subplan_name, name) == 0) + { + found = true; + break; + } + } + + if (!found) + { + /* pstrdup here is just to avoid cast-away-const */ + char *chosen_name = pstrdup(name); + + glob->subplanNames = lappend(glob->subplanNames, chosen_name); + return chosen_name; + } + } + + /* + * If a numeric suffix is required or if the un-suffixed name is already + * in use, then loop until we find a positive integer that produces a + * novel name. + */ + for (n = 1; true; ++n) + { + char *proposed_name = psprintf("%s_%u", name, n); + bool found = false; + + foreach_ptr(char, subplan_name, glob->subplanNames) + { + if (strcmp(subplan_name, proposed_name) == 0) + { + found = true; + break; + } + } + + if (!found) + { + glob->subplanNames = lappend(glob->subplanNames, proposed_name); + return proposed_name; + } + + pfree(proposed_name); + } +} diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c index 6950eff2c5b..ccdc9bc264a 100644 --- a/src/backend/optimizer/plan/setrefs.c +++ b/src/backend/optimizer/plan/setrefs.c @@ -1034,16 +1034,35 @@ set_plan_refs(PlannerInfo *root, Plan *plan, int rtoffset) * expected to occur here, it seems safer to special-case * it here and keep the assertions that ROWID_VARs * shouldn't be seen by fix_scan_expr. + * + * We also must handle the case where set operations have + * been short-circuited resulting in a dummy Result node. + * prepunion.c uses varno==0 for the set op targetlist. + * See generate_setop_tlist() and generate_setop_tlist(). + * Here we rewrite these to use varno==1, which is the + * varno of the first set-op child. Without this, EXPLAIN + * will have trouble displaying targetlists of dummy set + * operations. */ foreach(l, splan->plan.targetlist) { TargetEntry *tle = (TargetEntry *) lfirst(l); Var *var = (Var *) tle->expr; - if (var && IsA(var, Var) && var->varno == ROWID_VAR) - tle->expr = (Expr *) makeNullConst(var->vartype, - var->vartypmod, - var->varcollid); + if (var && IsA(var, Var)) + { + if (var->varno == ROWID_VAR) + tle->expr = (Expr *) makeNullConst(var->vartype, + var->vartypmod, + var->varcollid); + else if (var->varno == 0) + tle->expr = (Expr *) makeVar(1, + var->varattno, + var->vartype, + var->vartypmod, + var->varcollid, + var->varlevelsup); + } } splan->plan.targetlist = diff --git a/src/backend/optimizer/plan/subselect.c b/src/backend/optimizer/plan/subselect.c index fae18548e07..14192a13236 100644 --- a/src/backend/optimizer/plan/subselect.c +++ b/src/backend/optimizer/plan/subselect.c @@ -103,6 +103,7 @@ static Bitmapset *finalize_plan(PlannerInfo *root, Bitmapset *scan_params); static bool finalize_primnode(Node *node, finalize_primnode_context *context); static bool finalize_agg_primnode(Node *node, finalize_primnode_context *context); +static const char *sublinktype_to_string(SubLinkType subLinkType); /* @@ -172,6 +173,7 @@ make_subplan(PlannerInfo *root, Query *orig_subquery, Plan *plan; List *plan_params; Node *result; + const char *sublinkstr = sublinktype_to_string(subLinkType); /* * Copy the source Query node. This is a quick and dirty kluge to resolve @@ -218,8 +220,9 @@ make_subplan(PlannerInfo *root, Query *orig_subquery, Assert(root->plan_params == NIL); /* Generate Paths for the subquery */ - subroot = subquery_planner(root->glob, subquery, root, false, - tuple_fraction, NULL); + subroot = subquery_planner(root->glob, subquery, + choose_plan_name(root->glob, sublinkstr, true), + root, false, tuple_fraction, NULL); /* Isolate the params needed by this specific subplan */ plan_params = root->plan_params; @@ -264,9 +267,12 @@ make_subplan(PlannerInfo *root, Query *orig_subquery, &newtestexpr, ¶mIds); if (subquery) { + char *plan_name; + /* Generate Paths for the ANY subquery; we'll need all rows */ - subroot = subquery_planner(root->glob, subquery, root, false, 0.0, - NULL); + plan_name = choose_plan_name(root->glob, sublinkstr, true); + subroot = subquery_planner(root->glob, subquery, plan_name, + root, false, 0.0, NULL); /* Isolate the params needed by this specific subplan */ plan_params = root->plan_params; @@ -324,15 +330,16 @@ build_subplan(PlannerInfo *root, Plan *plan, Path *path, { Node *result; SubPlan *splan; - bool isInitPlan; ListCell *lc; /* - * Initialize the SubPlan node. Note plan_id, plan_name, and cost fields - * are set further down. + * Initialize the SubPlan node. + * + * Note: plan_id and cost fields are set further down. */ splan = makeNode(SubPlan); splan->subLinkType = subLinkType; + splan->plan_name = subroot->plan_name; splan->testexpr = NULL; splan->paramIds = NIL; get_first_col_type(plan, &splan->firstColType, &splan->firstColTypmod, @@ -391,7 +398,7 @@ build_subplan(PlannerInfo *root, Plan *plan, Path *path, Assert(testexpr == NULL); prm = generate_new_exec_param(root, BOOLOID, -1, InvalidOid); splan->setParam = list_make1_int(prm->paramid); - isInitPlan = true; + splan->isInitPlan = true; result = (Node *) prm; } else if (splan->parParam == NIL && subLinkType == EXPR_SUBLINK) @@ -406,7 +413,7 @@ build_subplan(PlannerInfo *root, Plan *plan, Path *path, exprTypmod((Node *) te->expr), exprCollation((Node *) te->expr)); splan->setParam = list_make1_int(prm->paramid); - isInitPlan = true; + splan->isInitPlan = true; result = (Node *) prm; } else if (splan->parParam == NIL && subLinkType == ARRAY_SUBLINK) @@ -426,7 +433,7 @@ build_subplan(PlannerInfo *root, Plan *plan, Path *path, exprTypmod((Node *) te->expr), exprCollation((Node *) te->expr)); splan->setParam = list_make1_int(prm->paramid); - isInitPlan = true; + splan->isInitPlan = true; result = (Node *) prm; } else if (splan->parParam == NIL && subLinkType == ROWCOMPARE_SUBLINK) @@ -442,7 +449,7 @@ build_subplan(PlannerInfo *root, Plan *plan, Path *path, testexpr, params); splan->setParam = list_copy(splan->paramIds); - isInitPlan = true; + splan->isInitPlan = true; /* * The executable expression is returned to become part of the outer @@ -476,12 +483,12 @@ build_subplan(PlannerInfo *root, Plan *plan, Path *path, /* It can be an initplan if there are no parParams. */ if (splan->parParam == NIL) { - isInitPlan = true; + splan->isInitPlan = true; result = (Node *) makeNullConst(RECORDOID, -1, InvalidOid); } else { - isInitPlan = false; + splan->isInitPlan = false; result = (Node *) splan; } } @@ -536,7 +543,7 @@ build_subplan(PlannerInfo *root, Plan *plan, Path *path, plan = materialize_finished_plan(plan); result = (Node *) splan; - isInitPlan = false; + splan->isInitPlan = false; } /* @@ -547,7 +554,7 @@ build_subplan(PlannerInfo *root, Plan *plan, Path *path, root->glob->subroots = lappend(root->glob->subroots, subroot); splan->plan_id = list_length(root->glob->subplans); - if (isInitPlan) + if (splan->isInitPlan) root->init_plans = lappend(root->init_plans, splan); /* @@ -557,15 +564,10 @@ build_subplan(PlannerInfo *root, Plan *plan, Path *path, * there's no point since it won't get re-run without parameter changes * anyway. The input of a hashed subplan doesn't need REWIND either. */ - if (splan->parParam == NIL && !isInitPlan && !splan->useHashTable) + if (splan->parParam == NIL && !splan->isInitPlan && !splan->useHashTable) root->glob->rewindPlanIDs = bms_add_member(root->glob->rewindPlanIDs, splan->plan_id); - /* Label the subplan for EXPLAIN purposes */ - splan->plan_name = psprintf("%s %d", - isInitPlan ? "InitPlan" : "SubPlan", - splan->plan_id); - /* Lastly, fill in the cost estimates for use later */ cost_subplan(root, splan, plan); @@ -965,8 +967,9 @@ SS_process_ctes(PlannerInfo *root) * Generate Paths for the CTE query. Always plan for full retrieval * --- we don't have enough info to predict otherwise. */ - subroot = subquery_planner(root->glob, subquery, root, - cte->cterecursive, 0.0, NULL); + subroot = subquery_planner(root->glob, subquery, + choose_plan_name(root->glob, cte->ctename, false), + root, cte->cterecursive, 0.0, NULL); /* * Since the current query level doesn't yet contain any RTEs, it @@ -989,10 +992,11 @@ SS_process_ctes(PlannerInfo *root) * Make a SubPlan node for it. This is just enough unlike * build_subplan that we can't share code. * - * Note plan_id, plan_name, and cost fields are set further down. + * Note: plan_id and cost fields are set further down. */ splan = makeNode(SubPlan); splan->subLinkType = CTE_SUBLINK; + splan->plan_name = subroot->plan_name; splan->testexpr = NULL; splan->paramIds = NIL; get_first_col_type(plan, &splan->firstColType, &splan->firstColTypmod, @@ -1039,9 +1043,6 @@ SS_process_ctes(PlannerInfo *root) root->cte_plan_ids = lappend_int(root->cte_plan_ids, splan->plan_id); - /* Label the subplan for EXPLAIN purposes */ - splan->plan_name = psprintf("CTE %s", cte->ctename); - /* Lastly, fill in the cost estimates for use later */ cost_subplan(root, splan, plan); } @@ -3185,7 +3186,8 @@ SS_make_initplan_from_plan(PlannerInfo *root, node = makeNode(SubPlan); node->subLinkType = EXPR_SUBLINK; node->plan_id = list_length(root->glob->subplans); - node->plan_name = psprintf("InitPlan %d", node->plan_id); + node->plan_name = subroot->plan_name; + node->isInitPlan = true; get_first_col_type(plan, &node->firstColType, &node->firstColTypmod, &node->firstColCollation); node->parallel_safe = plan->parallel_safe; @@ -3201,3 +3203,32 @@ SS_make_initplan_from_plan(PlannerInfo *root, /* Set costs of SubPlan using info from the plan tree */ cost_subplan(subroot, node, plan); } + +/* + * Get a string equivalent of a given subLinkType. + */ +static const char * +sublinktype_to_string(SubLinkType subLinkType) +{ + switch (subLinkType) + { + case EXISTS_SUBLINK: + return "exists"; + case ALL_SUBLINK: + return "all"; + case ANY_SUBLINK: + return "any"; + case ROWCOMPARE_SUBLINK: + return "rowcompare"; + case EXPR_SUBLINK: + return "expr"; + case MULTIEXPR_SUBLINK: + return "multiexpr"; + case ARRAY_SUBLINK: + return "array"; + case CTE_SUBLINK: + return "cte"; + } + Assert(false); + return "???"; +} diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c index 35e8d3c183b..481d8011791 100644 --- a/src/backend/optimizer/prep/prepjointree.c +++ b/src/backend/optimizer/prep/prepjointree.c @@ -1356,6 +1356,7 @@ pull_up_simple_subquery(PlannerInfo *root, Node *jtnode, RangeTblEntry *rte, subroot->parse = subquery; subroot->glob = root->glob; subroot->query_level = root->query_level; + subroot->plan_name = root->plan_name; subroot->parent_root = root->parent_root; subroot->plan_params = NIL; subroot->outer_params = NULL; @@ -1383,6 +1384,7 @@ pull_up_simple_subquery(PlannerInfo *root, Node *jtnode, RangeTblEntry *rte, subroot->qual_security_level = 0; subroot->placeholdersFrozen = false; subroot->hasRecursion = false; + subroot->assumeReplanning = false; subroot->wt_param_id = -1; subroot->non_recursive_path = NULL; /* We don't currently need a top JoinDomain for the subroot */ diff --git a/src/backend/optimizer/prep/prepunion.c b/src/backend/optimizer/prep/prepunion.c index 547dbd53540..55665824179 100644 --- a/src/backend/optimizer/prep/prepunion.c +++ b/src/backend/optimizer/prep/prepunion.c @@ -232,6 +232,7 @@ recurse_set_operations(Node *setOp, PlannerInfo *root, PlannerInfo *subroot; List *tlist; bool trivial_tlist; + char *plan_name; Assert(subquery != NULL); @@ -246,7 +247,9 @@ recurse_set_operations(Node *setOp, PlannerInfo *root, * parentOp, pass that down to encourage subquery_planner to consider * suitably-sorted Paths. */ - subroot = rel->subroot = subquery_planner(root->glob, subquery, root, + plan_name = choose_plan_name(root->glob, "setop", true); + subroot = rel->subroot = subquery_planner(root->glob, subquery, + plan_name, root, false, root->tuple_fraction, parentOp); @@ -826,7 +829,6 @@ generate_union_paths(SetOperationStmt *op, PlannerInfo *root, /* If all UNION children were dummy rels, make the resulting rel dummy */ if (cheapest_pathlist == NIL) { - result_rel->reltarget = create_pathtarget(root, list_nth(tlist_list, 0)); mark_dummy_rel(result_rel); return result_rel; @@ -1186,6 +1188,69 @@ generate_nonunion_paths(SetOperationStmt *op, PlannerInfo *root, result_rel->reltarget = create_setop_pathtarget(root, tlist, list_make2(lpath, rpath)); + /* Check for provably empty setop inputs and add short-circuit paths. */ + if (op->op == SETOP_EXCEPT) + { + /* + * For EXCEPTs, if the left side is dummy then there's no need to + * inspect the right-hand side as scanning the right to find tuples to + * remove won't make the left-hand input any more empty. + */ + if (is_dummy_rel(lrel)) + { + mark_dummy_rel(result_rel); + + return result_rel; + } + + /* Handle EXCEPTs with dummy right input */ + if (is_dummy_rel(rrel)) + { + if (op->all) + { + Path *apath; + + /* + * EXCEPT ALL: If the right-hand input is dummy then we can + * simply scan the left-hand input. To keep createplan.c + * happy, use a single child Append to handle the translation + * between the set op targetlist and the targetlist of the + * left input. The Append will be removed in setrefs.c. + */ + apath = (Path *) create_append_path(root, result_rel, list_make1(lpath), + NIL, NIL, NULL, 0, false, -1); + + add_path(result_rel, apath); + + return result_rel; + } + else + { + /* + * To make EXCEPT with a dummy RHS work means having to + * deduplicate the left input. That could be done with + * AggPaths, but it doesn't seem worth the effort. Let the + * normal path generation code below handle this one. + */ + } + } + } + else + { + /* + * For INTERSECT, if either input is a dummy rel then we can mark the + * result_rel as dummy since intersecting with an empty relation can + * never yield any results. This is true regardless of INTERSECT or + * INTERSECT ALL. + */ + if (is_dummy_rel(lrel) || is_dummy_rel(rrel)) + { + mark_dummy_rel(result_rel); + + return result_rel; + } + } + /* * Estimate number of distinct groups that we'll need hashtable entries * for; this is the size of the left-hand input for EXCEPT, or the smaller diff --git a/src/backend/optimizer/util/Makefile b/src/backend/optimizer/util/Makefile index 4fb115cb118..87b4c3c0869 100644 --- a/src/backend/optimizer/util/Makefile +++ b/src/backend/optimizer/util/Makefile @@ -15,6 +15,7 @@ include $(top_builddir)/src/Makefile.global OBJS = \ appendinfo.o \ clauses.o \ + extendplan.o \ inherit.o \ joininfo.o \ orclauses.o \ diff --git a/src/backend/optimizer/util/extendplan.c b/src/backend/optimizer/util/extendplan.c new file mode 100644 index 00000000000..03d32277ba1 --- /dev/null +++ b/src/backend/optimizer/util/extendplan.c @@ -0,0 +1,183 @@ +/*------------------------------------------------------------------------- + * + * extendplan.c + * Extend core planner objects with additional private state + * + * Portions Copyright (c) 1996-2025, PostgreSQL Global Development Group + * Portions Copyright (c) 1994-5, Regents of the University of California + * + * The interfaces defined in this file make it possible for loadable + * modules to store their own private state inside of key planner data + * structures -- specifically, the PlannerGlobal, PlannerInfo, and + * RelOptInfo structures. This can make it much easier to write + * reasonably efficient planner extensions; for instance, code that + * uses set_join_pathlist_hook can arrange to compute a key intermediate + * result once per joinrel rather than on every call. + * + * IDENTIFICATION + * src/backend/optimizer/util/extendplan.c + * + *------------------------------------------------------------------------- + */ +#include "postgres.h" + +#include "optimizer/extendplan.h" +#include "port/pg_bitutils.h" +#include "utils/memutils.h" + +static const char **PlannerExtensionNameArray = NULL; +static int PlannerExtensionNamesAssigned = 0; +static int PlannerExtensionNamesAllocated = 0; + +/* + * Map the name of a planner extension to an integer ID. + * + * Within the lifetime of a particular backend, the same name will be mapped + * to the same ID every time. IDs are not stable across backends. Use the ID + * that you get from this function to call the remaining functions in this + * file. + */ +int +GetPlannerExtensionId(const char *extension_name) +{ + /* Search for an existing extension by this name; if found, return ID. */ + for (int i = 0; i < PlannerExtensionNamesAssigned; ++i) + if (strcmp(PlannerExtensionNameArray[i], extension_name) == 0) + return i; + + /* If there is no array yet, create one. */ + if (PlannerExtensionNameArray == NULL) + { + PlannerExtensionNamesAllocated = 16; + PlannerExtensionNameArray = (const char **) + MemoryContextAlloc(TopMemoryContext, + PlannerExtensionNamesAllocated + * sizeof(char *)); + } + + /* If there's an array but it's currently full, expand it. */ + if (PlannerExtensionNamesAssigned >= PlannerExtensionNamesAllocated) + { + int i = pg_nextpower2_32(PlannerExtensionNamesAssigned + 1); + + PlannerExtensionNameArray = (const char **) + repalloc(PlannerExtensionNameArray, i * sizeof(char *)); + PlannerExtensionNamesAllocated = i; + } + + /* Assign and return new ID. */ + PlannerExtensionNameArray[PlannerExtensionNamesAssigned] = extension_name; + return PlannerExtensionNamesAssigned++; +} + +/* + * Store extension-specific state into a PlannerGlobal. + */ +void +SetPlannerGlobalExtensionState(PlannerGlobal *glob, int extension_id, + void *opaque) +{ + Assert(extension_id >= 0); + + /* If there is no array yet, create one. */ + if (glob->extension_state == NULL) + { + MemoryContext planner_cxt; + Size sz; + + planner_cxt = GetMemoryChunkContext(glob); + glob->extension_state_allocated = + Max(4, pg_nextpower2_32(extension_id + 1)); + sz = glob->extension_state_allocated * sizeof(void *); + glob->extension_state = MemoryContextAllocZero(planner_cxt, sz); + } + + /* If there's an array but it's currently full, expand it. */ + if (extension_id >= glob->extension_state_allocated) + { + int i; + + i = pg_nextpower2_32(extension_id + 1); + glob->extension_state = (void **) + repalloc0(glob->extension_state, + glob->extension_state_allocated * sizeof(void *), + i * sizeof(void *)); + glob->extension_state_allocated = i; + } + + glob->extension_state[extension_id] = opaque; +} + +/* + * Store extension-specific state into a PlannerInfo. + */ +void +SetPlannerInfoExtensionState(PlannerInfo *root, int extension_id, + void *opaque) +{ + Assert(extension_id >= 0); + + /* If there is no array yet, create one. */ + if (root->extension_state == NULL) + { + Size sz; + + root->extension_state_allocated = + Max(4, pg_nextpower2_32(extension_id + 1)); + sz = root->extension_state_allocated * sizeof(void *); + root->extension_state = MemoryContextAllocZero(root->planner_cxt, sz); + } + + /* If there's an array but it's currently full, expand it. */ + if (extension_id >= root->extension_state_allocated) + { + int i; + + i = pg_nextpower2_32(extension_id + 1); + root->extension_state = (void **) + repalloc0(root->extension_state, + root->extension_state_allocated * sizeof(void *), + i * sizeof(void *)); + root->extension_state_allocated = i; + } + + root->extension_state[extension_id] = opaque; +} + +/* + * Store extension-specific state into a RelOptInfo. + */ +void +SetRelOptInfoExtensionState(RelOptInfo *rel, int extension_id, + void *opaque) +{ + Assert(extension_id >= 0); + + /* If there is no array yet, create one. */ + if (rel->extension_state == NULL) + { + MemoryContext planner_cxt; + Size sz; + + planner_cxt = GetMemoryChunkContext(rel); + rel->extension_state_allocated = + Max(4, pg_nextpower2_32(extension_id + 1)); + sz = rel->extension_state_allocated * sizeof(void *); + rel->extension_state = MemoryContextAllocZero(planner_cxt, sz); + } + + /* If there's an array but it's currently full, expand it. */ + if (extension_id >= rel->extension_state_allocated) + { + int i; + + i = pg_nextpower2_32(extension_id + 1); + rel->extension_state = (void **) + repalloc0(rel->extension_state, + rel->extension_state_allocated * sizeof(void *), + i * sizeof(void *)); + rel->extension_state_allocated = i; + } + + rel->extension_state[extension_id] = opaque; +} diff --git a/src/backend/optimizer/util/meson.build b/src/backend/optimizer/util/meson.build index b3bf913d096..f71f56e37a1 100644 --- a/src/backend/optimizer/util/meson.build +++ b/src/backend/optimizer/util/meson.build @@ -3,6 +3,7 @@ backend_sources += files( 'appendinfo.c', 'clauses.c', + 'extendplan.c', 'inherit.c', 'joininfo.c', 'orclauses.c', diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 21663af6979..050eef97a4c 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -8751,8 +8751,16 @@ get_parameter(Param *param, deparse_context *context) subplan = find_param_generator(param, context, &column); if (subplan) { - appendStringInfo(context->buf, "(%s%s).col%d", + const char *nameprefix; + + if (subplan->isInitPlan) + nameprefix = "InitPlan "; + else + nameprefix = "SubPlan "; + + appendStringInfo(context->buf, "(%s%s%s).col%d", subplan->useHashTable ? "hashed " : "", + nameprefix, subplan->plan_name, column + 1); return; @@ -9589,11 +9597,19 @@ get_rule_expr(Node *node, deparse_context *context, } else { + const char *nameprefix; + /* No referencing Params, so show the SubPlan's name */ + if (subplan->isInitPlan) + nameprefix = "InitPlan "; + else + nameprefix = "SubPlan "; if (subplan->useHashTable) - appendStringInfo(buf, "hashed %s)", subplan->plan_name); + appendStringInfo(buf, "hashed %s%s)", + nameprefix, subplan->plan_name); else - appendStringInfo(buf, "%s)", subplan->plan_name); + appendStringInfo(buf, "%s%s)", + nameprefix, subplan->plan_name); } } break; @@ -9613,11 +9629,18 @@ get_rule_expr(Node *node, deparse_context *context, foreach(lc, asplan->subplans) { SubPlan *splan = lfirst_node(SubPlan, lc); + const char *nameprefix; + if (splan->isInitPlan) + nameprefix = "InitPlan "; + else + nameprefix = "SubPlan "; if (splan->useHashTable) - appendStringInfo(buf, "hashed %s", splan->plan_name); + appendStringInfo(buf, "hashed %s%s", nameprefix, + splan->plan_name); else - appendStringInfoString(buf, splan->plan_name); + appendStringInfo(buf, "%s%s", nameprefix, + splan->plan_name); if (lnext(asplan->subplans, lc)) appendStringInfoString(buf, " or "); } diff --git a/src/bin/psql/meson.build b/src/bin/psql/meson.build index f795ff28271..d344053c23b 100644 --- a/src/bin/psql/meson.build +++ b/src/bin/psql/meson.build @@ -77,6 +77,7 @@ tests += { 't/001_basic.pl', 't/010_tab_completion.pl', 't/020_cancel.pl', + 't/030_pager.pl', ], }, } diff --git a/src/bin/psql/t/030_pager.pl b/src/bin/psql/t/030_pager.pl new file mode 100644 index 00000000000..afe97355c44 --- /dev/null +++ b/src/bin/psql/t/030_pager.pl @@ -0,0 +1,106 @@ + +# Copyright (c) 2021-2025, PostgreSQL Global Development Group + +use strict; +use warnings FATAL => 'all'; + +use PostgreSQL::Test::Cluster; +use PostgreSQL::Test::Utils; +use Test::More; +use Data::Dumper; + +# If we don't have IO::Pty, forget it, because IPC::Run depends on that +# to support pty connections +eval { require IO::Pty; }; +if ($@) +{ + plan skip_all => 'IO::Pty is needed to run this test'; +} + +# Check that "wc -l" does what we expect, else forget it +my $wcstdin = "foo bar\nbaz\n"; +my ($wcstdout, $wcstderr); +my $result = IPC::Run::run [ 'wc', '-l' ], + '<' => \$wcstdin, + '>' => \$wcstdout, + '2>' => \$wcstderr; +chomp $wcstdout; +if ($wcstdout !~ /^ *2$/ || $wcstderr ne '') +{ + note "wc stdout = '$wcstdout'\n"; + note "wc stderr = '$wcstderr'\n"; + plan skip_all => '"wc -l" is needed to run this test'; +} + +# We set up "wc -l" as the pager so we can tell whether psql used the pager +$ENV{PSQL_PAGER} = "wc -l"; + +# start a new server +my $node = PostgreSQL::Test::Cluster->new('main'); +$node->init; +$node->start; + +# fire up an interactive psql session +my $h = $node->interactive_psql('postgres'); + +# set the pty's window size to known values +# (requires undesirable chumminess with the innards of IPC::Run) +for my $pty (values %{ $h->{run}->{PTYS} }) +{ + $pty->set_winsize(24, 80); +} + +# Simple test case: type something and see if psql responds as expected +sub do_command +{ + my ($send, $pattern, $annotation) = @_; + + # report test failures from caller location + local $Test::Builder::Level = $Test::Builder::Level + 1; + + # restart per-command timer + $h->{timeout}->start($PostgreSQL::Test::Utils::timeout_default); + + # send the data to be sent and wait for its result + my $out = $h->query_until($pattern, $send); + my $okay = ($out =~ $pattern && !$h->{timeout}->is_expired); + ok($okay, $annotation); + # for debugging, log actual output if it didn't match + local $Data::Dumper::Terse = 1; + local $Data::Dumper::Useqq = 1; + diag 'Actual output was ' . Dumper($out) . "Did not match \"$pattern\"\n" + if !$okay; + return; +} + +# Test invocation of the pager +# +# Note that interactive_psql starts psql with --no-align --tuples-only, +# and that the output string will include psql's prompts and command echo. + +do_command( + "SELECT 'test' AS t FROM generate_series(1,23);\n", + qr/^test\r?$/m, + "execute SELECT query that needs no pagination"); + +do_command( + "SELECT 'test' AS t FROM generate_series(1,24);\n", + qr/^ *24\r?$/m, + "execute SELECT query that needs pagination"); + +do_command( + "\\pset expanded\nSELECT generate_series(1,20) as g;\n", + qr/^ *39\r?$/m, + "execute SELECT query that needs pagination in expanded mode"); + +do_command( + "\\pset tuples_only off\n\\d+ information_schema.referential_constraints\n", + qr/^ *\d+\r?$/m, + "execute command with footer that needs pagination"); + +# send psql an explicit \q to shut it down, else pty won't close properly +$h->quit or die "psql returned $?"; + +# done +$node->stop; +done_testing(); diff --git a/src/fe_utils/print.c b/src/fe_utils/print.c index 4af0f32f2fc..73847d3d6b3 100644 --- a/src/fe_utils/print.c +++ b/src/fe_utils/print.c @@ -33,6 +33,11 @@ #include "fe_utils/mbprint.h" #include "fe_utils/print.h" +/* Presently, count_table_lines() is only used within #ifdef TIOCGWINSZ */ +#ifdef TIOCGWINSZ +#define NEED_COUNT_TABLE_LINES +#endif + /* * If the calling program doesn't have any mechanism for setting * cancel_pressed, it will have no effect. @@ -266,9 +271,20 @@ static const unicodeStyleFormat unicode_style = { /* Local functions */ static int strlen_max_width(unsigned char *str, int *target_width, int encoding); -static void IsPagerNeeded(const printTableContent *cont, int extra_lines, bool expanded, +static FILE *PageOutputInternal(int lines, const printTableOpt *topt, + const printTableContent *cont, + const unsigned int *width_wrap, + bool vertical); +static void IsPagerNeeded(const printTableContent *cont, + const unsigned int *width_wrap, + bool vertical, FILE **fout, bool *is_pager); - +#ifdef NEED_COUNT_TABLE_LINES +static int count_table_lines(const printTableContent *cont, + const unsigned int *width_wrap, + bool vertical, + int threshold); +#endif static void print_aligned_vertical(const printTableContent *cont, FILE *fout, bool is_pager); @@ -656,8 +672,6 @@ print_aligned_text(const printTableContent *cont, FILE *fout, bool is_pager) unsigned char **format_buf; unsigned int width_total; unsigned int total_header_width; - unsigned int extra_row_output_lines = 0; - unsigned int extra_output_lines = 0; const char *const *ptr; @@ -722,17 +736,12 @@ print_aligned_text(const printTableContent *cont, FILE *fout, bool is_pager) max_nl_lines[i] = nl_lines; if (bytes_required > max_bytes[i]) max_bytes[i] = bytes_required; - if (nl_lines > extra_row_output_lines) - extra_row_output_lines = nl_lines; width_header[i] = width; } - /* Add height of tallest header column */ - extra_output_lines += extra_row_output_lines; - extra_row_output_lines = 0; /* scan all cells, find maximum width, compute cell_count */ - for (i = 0, ptr = cont->cells; *ptr; ptr++, i++, cell_count++) + for (i = 0, ptr = cont->cells; *ptr; ptr++, cell_count++) { int width, nl_lines, @@ -741,14 +750,18 @@ print_aligned_text(const printTableContent *cont, FILE *fout, bool is_pager) pg_wcssize((const unsigned char *) *ptr, strlen(*ptr), encoding, &width, &nl_lines, &bytes_required); - if (width > max_width[i % col_count]) - max_width[i % col_count] = width; - if (nl_lines > max_nl_lines[i % col_count]) - max_nl_lines[i % col_count] = nl_lines; - if (bytes_required > max_bytes[i % col_count]) - max_bytes[i % col_count] = bytes_required; + if (width > max_width[i]) + max_width[i] = width; + if (nl_lines > max_nl_lines[i]) + max_nl_lines[i] = nl_lines; + if (bytes_required > max_bytes[i]) + max_bytes[i] = bytes_required; + + width_average[i] += width; - width_average[i % col_count] += width; + /* i is the current column number: increment with wrap */ + if (++i >= col_count) + i = 0; } /* If we have rows, compute average */ @@ -889,43 +902,10 @@ print_aligned_text(const printTableContent *cont, FILE *fout, bool is_pager) is_pager = is_local_pager = true; } - /* Check if newlines or our wrapping now need the pager */ - if (!is_pager && fout == stdout) + /* Check if there are enough lines to require the pager */ + if (!is_pager) { - /* scan all cells, find maximum width, compute cell_count */ - for (i = 0, ptr = cont->cells; *ptr; ptr++, cell_count++) - { - int width, - nl_lines, - bytes_required; - - pg_wcssize((const unsigned char *) *ptr, strlen(*ptr), encoding, - &width, &nl_lines, &bytes_required); - - /* - * A row can have both wrapping and newlines that cause it to - * display across multiple lines. We check for both cases below. - */ - if (width > 0 && width_wrap[i]) - { - unsigned int extra_lines; - - /* don't count the first line of nl_lines - it's not "extra" */ - extra_lines = ((width - 1) / width_wrap[i]) + nl_lines - 1; - if (extra_lines > extra_row_output_lines) - extra_row_output_lines = extra_lines; - } - - /* i is the current column number: increment with wrap */ - if (++i >= col_count) - { - i = 0; - /* At last column of each row, add tallest column height */ - extra_output_lines += extra_row_output_lines; - extra_row_output_lines = 0; - } - } - IsPagerNeeded(cont, extra_output_lines, false, &fout, &is_pager); + IsPagerNeeded(cont, width_wrap, false, &fout, &is_pager); is_local_pager = is_pager; } @@ -1351,6 +1331,11 @@ print_aligned_vertical(const printTableContent *cont, if (opt_border > 2) opt_border = 2; + /* + * Kluge for totally empty table: use the default footer even though + * vertical modes normally don't. Otherwise we'd print nothing at all, + * which isn't terribly friendly. Assume pager will not be needed. + */ if (cont->cells[0] == NULL && cont->opt->start_table && cont->opt->stop_table) { @@ -1376,7 +1361,7 @@ print_aligned_vertical(const printTableContent *cont, */ if (!is_pager) { - IsPagerNeeded(cont, 0, true, &fout, &is_pager); + IsPagerNeeded(cont, NULL, true, &fout, &is_pager); is_local_pager = is_pager; } @@ -3081,28 +3066,62 @@ set_sigpipe_trap_state(bool ignore) /* * PageOutput * - * Tests if pager is needed and returns appropriate FILE pointer. + * Tests if pager is needed and returns appropriate FILE pointer + * (either a pipe, or stdout if we don't need the pager). + * + * lines: number of lines that will be printed + * topt: print formatting options * * If the topt argument is NULL no pager is used. */ FILE * PageOutput(int lines, const printTableOpt *topt) { + return PageOutputInternal(lines, topt, NULL, NULL, false); +} + +/* + * Private version that allows for line-counting to be avoided when + * not needed. If "cont" is not null then the input value of "lines" + * is ignored and we count lines based on cont + width_wrap + vertical + * (see count_table_lines). + */ +static FILE * +PageOutputInternal(int lines, const printTableOpt *topt, + const printTableContent *cont, + const unsigned int *width_wrap, + bool vertical) +{ /* check whether we need / can / are supposed to use pager */ if (topt && topt->pager && isatty(fileno(stdin)) && isatty(fileno(stdout))) { + /* without TIOCGWINSZ, pager == 1 acts the same as pager > 1 */ #ifdef TIOCGWINSZ unsigned short int pager = topt->pager; int min_lines = topt->pager_min_lines; - int result; - struct winsize screen_size; - result = ioctl(fileno(stdout), TIOCGWINSZ, &screen_size); + if (pager == 1) + { + int result; + struct winsize screen_size; - /* >= accounts for a one-line prompt */ - if (result == -1 - || (lines >= screen_size.ws_row && lines >= min_lines) - || pager > 1) + result = ioctl(fileno(stdout), TIOCGWINSZ, &screen_size); + if (result < 0) + pager = 2; /* force use of pager */ + else + { + int threshold = Max(screen_size.ws_row, min_lines); + + if (cont) /* caller wants us to calculate lines */ + lines = count_table_lines(cont, width_wrap, vertical, + threshold); + /* >= accounts for a one-line prompt */ + if (lines >= threshold) + pager = 2; + } + } + + if (pager > 1) #endif { const char *pagerprog; @@ -3398,38 +3417,212 @@ printTableCleanup(printTableContent *const content) * IsPagerNeeded * * Setup pager if required + * + * cont: table data to be printed + * width_wrap[]: per-column maximum width, or NULL if caller will not wrap + * vertical: vertical mode? + * fout: where to print to (in/out argument) + * is_pager: output argument + * + * If we decide pager is needed, *fout is modified and *is_pager is set true */ static void -IsPagerNeeded(const printTableContent *cont, int extra_lines, bool expanded, +IsPagerNeeded(const printTableContent *cont, const unsigned int *width_wrap, + bool vertical, FILE **fout, bool *is_pager) { if (*fout == stdout) { - int lines; + *fout = PageOutputInternal(0, cont->opt, cont, width_wrap, vertical); + *is_pager = (*fout != stdout); + } + else + *is_pager = false; +} - if (expanded) - lines = (cont->ncolumns + 1) * cont->nrows; - else - lines = cont->nrows + 1; +/* + * Count the number of lines needed to print the given table. + * + * cont: table data to be printed + * width_wrap[]: per-column maximum width, or NULL if caller will not wrap + * vertical: vertical mode? + * threshold: we can stop counting once we pass this many lines + * + * The result is currently only fully accurate for ALIGNED/WRAPPED and + * UNALIGNED formats; otherwise it's an approximation. + * + * Note: while cont->opt will tell us most formatting details, we need the + * separate "vertical" flag because of the possibility of a dynamic switch + * from aligned_text to aligned_vertical format. + * + * The point of the threshold parameter is that when the table is very long, + * we'll typically be able to stop scanning after not many rows. + */ +#ifdef NEED_COUNT_TABLE_LINES +static int +count_table_lines(const printTableContent *cont, + const unsigned int *width_wrap, + bool vertical, + int threshold) +{ + int *header_height; + int lines = 0, + max_lines = 0, + nl_lines, + i; + int encoding = cont->opt->encoding; + const char *const *cell; - if (!cont->opt->tuples_only) - { - printTableFooter *f; + /* + * Scan all column headers and determine their heights. Cache the values + * since vertical mode repeats the headers for every record. + */ + header_height = (int *) pg_malloc(cont->ncolumns * sizeof(int)); + for (i = 0; i < cont->ncolumns; i++) + { + pg_wcssize((const unsigned char *) cont->headers[i], + strlen(cont->headers[i]), encoding, + NULL, &header_height[i], NULL); + } + + /* + * Account for separator lines (if used), as well as the trailing blank + * line that most formats emit. + */ + switch (cont->opt->format) + { + case PRINT_ALIGNED: + case PRINT_WRAPPED: /* - * FIXME -- this is slightly bogus: it counts the number of - * footers, not the number of lines in them. + * Vertical mode writes one separator line per record. Normal + * mode writes a single separator line between header and rows. */ - for (f = cont->footers; f; f = f->next) - lines++; + lines = vertical ? cont->nrows : 1; + /* Both modes add a blank line at the end */ + lines++; + break; + case PRINT_UNALIGNED: + + /* + * Vertical mode writes a separator (here assumed to be a newline) + * between records. Normal mode writes nothing extra. + */ + if (vertical) + lines = Max(cont->nrows - 1, 0); + break; + case PRINT_CSV: + /* Nothing extra is added */ + break; + case PRINT_HTML: + case PRINT_ASCIIDOC: + case PRINT_LATEX: + case PRINT_LATEX_LONGTABLE: + case PRINT_TROFF_MS: + + /* + * These formats aren't really meant for interactive consumption, + * so for now we won't work hard on them. Treat them like aligned + * mode. + */ + lines = vertical ? cont->nrows : 1; + lines++; + break; + case PRINT_NOTHING: + /* Shouldn't get here... */ + break; + } + + /* Scan all cells to count their lines */ + for (i = 0, cell = cont->cells; *cell; cell++) + { + int width; + + /* Count the original line breaks */ + pg_wcssize((const unsigned char *) *cell, strlen(*cell), encoding, + &width, &nl_lines, NULL); + + /* Count extra lines due to wrapping */ + if (width > 0 && width_wrap && width_wrap[i]) + nl_lines += (width - 1) / width_wrap[i]; + + if (vertical) + { + /* Pick the height of the header or cell, whichever is taller */ + if (nl_lines > header_height[i]) + lines += nl_lines; + else + lines += header_height[i]; + } + else + { + /* Remember max height in the current row */ + if (nl_lines > max_lines) + max_lines = nl_lines; } - *fout = PageOutput(lines + extra_lines, cont->opt); - *is_pager = (*fout != stdout); + /* i is the current column number: increment with wrap */ + if (++i >= cont->ncolumns) + { + i = 0; + if (!vertical) + { + /* At last column of each row, add tallest column height */ + lines += max_lines; + max_lines = 0; + } + /* Stop scanning table body once we pass threshold */ + if (lines > threshold) + break; + } } - else - *is_pager = false; + + /* Account for header and footer decoration */ + if (!cont->opt->tuples_only && lines <= threshold) + { + printTableFooter *f; + + if (cont->title) + { + /* Add height of title */ + pg_wcssize((const unsigned char *) cont->title, strlen(cont->title), + encoding, NULL, &nl_lines, NULL); + lines += nl_lines; + } + + if (!vertical) + { + /* Add height of tallest header column */ + max_lines = 0; + for (i = 0; i < cont->ncolumns; i++) + { + if (header_height[i] > max_lines) + max_lines = header_height[i]; + } + lines += max_lines; + } + + /* + * Add all footer lines. Vertical mode does not use the default + * footer, but we must include that in normal mode. + */ + for (f = (vertical ? cont->footers : footers_with_default(cont)); + f != NULL; f = f->next) + { + pg_wcssize((const unsigned char *) f->data, strlen(f->data), + encoding, NULL, &nl_lines, NULL); + lines += nl_lines; + /* Stop scanning footers once we pass threshold */ + if (lines > threshold) + break; + } + } + + free(header_height); + + return lines; } +#endif /* NEED_COUNT_TABLE_LINES */ /* * Use this to print any table in the supported formats. @@ -3456,7 +3649,7 @@ printTable(const printTableContent *cont, cont->opt->format != PRINT_ALIGNED && cont->opt->format != PRINT_WRAPPED) { - IsPagerNeeded(cont, 0, (cont->opt->expanded == 1), &fout, &is_pager); + IsPagerNeeded(cont, NULL, (cont->opt->expanded == 1), &fout, &is_pager); is_local_pager = is_pager; } @@ -3464,10 +3657,6 @@ printTable(const printTableContent *cont, clearerr(fout); /* print the stuff */ - - if (flog) - print_aligned_text(cont, flog, false); - switch (cont->opt->format) { case PRINT_UNALIGNED: @@ -3534,6 +3723,10 @@ printTable(const printTableContent *cont, if (is_local_pager) ClosePager(fout); + + /* also produce log output if wanted */ + if (flog) + print_aligned_text(cont, flog, false); } /* diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h index b12a2508d8c..4e3230ba234 100644 --- a/src/include/nodes/pathnodes.h +++ b/src/include/nodes/pathnodes.h @@ -110,6 +110,9 @@ typedef struct PlannerGlobal /* PlannerInfos for SubPlan nodes */ List *subroots pg_node_attr(read_write_ignore); + /* names already used for subplans (list of C strings) */ + List *subplanNames pg_node_attr(read_write_ignore); + /* indices of subplans that require REWIND */ Bitmapset *rewindPlanIDs; @@ -182,6 +185,10 @@ typedef struct PlannerGlobal /* hash table for NOT NULL attnums of relations */ struct HTAB *rel_notnullatts_hash pg_node_attr(read_write_ignore); + + /* extension state */ + void **extension_state pg_node_attr(read_write_ignore); + int extension_state_allocated; } PlannerGlobal; /* macro for fetching the Plan associated with a SubPlan node */ @@ -228,6 +235,9 @@ struct PlannerInfo /* NULL at outermost Query */ PlannerInfo *parent_root pg_node_attr(read_write_ignore); + /* Subplan name for EXPLAIN and debugging purposes (NULL at top level) */ + char *plan_name; + /* * plan_params contains the expressions that this query level needs to * make available to a lower query level that is currently being planned. @@ -526,6 +536,8 @@ struct PlannerInfo bool placeholdersFrozen; /* true if planning a recursive WITH item */ bool hasRecursion; + /* true if a planner extension may replan this subquery */ + bool assumeReplanning; /* * The rangetable index for the RTE_GROUP RTE, or 0 if there is no @@ -572,14 +584,15 @@ struct PlannerInfo bool *isAltSubplan pg_node_attr(read_write_ignore); bool *isUsedSubplan pg_node_attr(read_write_ignore); - /* optional private data for join_search_hook, e.g., GEQO */ - void *join_search_private pg_node_attr(read_write_ignore); - /* Does this query modify any partition key columns? */ bool partColsUpdated; /* PartitionPruneInfos added in this query's plan. */ List *partPruneInfos; + + /* extension state */ + void **extension_state pg_node_attr(read_write_ignore); + int extension_state_allocated; }; @@ -1091,6 +1104,10 @@ typedef struct RelOptInfo List **partexprs pg_node_attr(read_write_ignore); /* Nullable partition key expressions */ List **nullable_partexprs pg_node_attr(read_write_ignore); + + /* extension state */ + void **extension_state pg_node_attr(read_write_ignore); + int extension_state_allocated; } RelOptInfo; /* diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h index e9d8bf74145..1b4436f2ff6 100644 --- a/src/include/nodes/primnodes.h +++ b/src/include/nodes/primnodes.h @@ -1108,6 +1108,7 @@ typedef struct SubPlan Oid firstColCollation; /* Collation of first column of subplan * result */ /* Information about execution strategy: */ + bool isInitPlan; /* true if it's an InitPlan */ bool useHashTable; /* true to store subselect output in a hash * table (implies we are doing "IN") */ bool unknownEqFalse; /* true if it's okay to return FALSE when the diff --git a/src/include/optimizer/extendplan.h b/src/include/optimizer/extendplan.h new file mode 100644 index 00000000000..de9618761dd --- /dev/null +++ b/src/include/optimizer/extendplan.h @@ -0,0 +1,72 @@ +/*------------------------------------------------------------------------- + * + * extendplan.h + * Extend core planner objects with additional private state + * + * + * Portions Copyright (c) 1996-2025, PostgreSQL Global Development Group + * Portions Copyright (c) 1994, Regents of the University of California + * + * src/include/optimizer/extendplan.h + * + *------------------------------------------------------------------------- + */ +#ifndef EXTENDPLAN_H +#define EXTENDPLAN_H + +#include "nodes/pathnodes.h" + +extern int GetPlannerExtensionId(const char *extension_name); + +/* + * Get extension-specific state from a PlannerGlobal. + */ +static inline void * +GetPlannerGlobalExtensionState(PlannerGlobal *glob, int extension_id) +{ + Assert(extension_id >= 0); + + if (extension_id >= glob->extension_state_allocated) + return NULL; + + return glob->extension_state[extension_id]; +} + +/* + * Get extension-specific state from a PlannerInfo. + */ +static inline void * +GetPlannerInfoExtensionState(PlannerInfo *root, int extension_id) +{ + Assert(extension_id >= 0); + + if (extension_id >= root->extension_state_allocated) + return NULL; + + return root->extension_state[extension_id]; +} + +/* + * Get extension-specific state from a PlannerInfo. + */ +static inline void * +GetRelOptInfoExtensionState(RelOptInfo *rel, int extension_id) +{ + Assert(extension_id >= 0); + + if (extension_id >= rel->extension_state_allocated) + return NULL; + + return rel->extension_state[extension_id]; +} + +/* Functions to store private state into various planner objects */ +extern void SetPlannerGlobalExtensionState(PlannerGlobal *glob, + int extension_id, + void *opaque); +extern void SetPlannerInfoExtensionState(PlannerInfo *root, int extension_id, + void *opaque); +extern void SetRelOptInfoExtensionState(RelOptInfo *rel, int extension_id, + void *opaque); + +#endif diff --git a/src/include/optimizer/geqo.h b/src/include/optimizer/geqo.h index 9f8e0f337aa..b3017dd8ec4 100644 --- a/src/include/optimizer/geqo.h +++ b/src/include/optimizer/geqo.h @@ -24,6 +24,7 @@ #include "common/pg_prng.h" #include "nodes/pathnodes.h" +#include "optimizer/extendplan.h" #include "optimizer/geqo_gene.h" @@ -62,6 +63,8 @@ extern PGDLLIMPORT int Geqo_generations; /* 1 .. inf, or 0 to use default */ extern PGDLLIMPORT double Geqo_selection_bias; +extern PGDLLIMPORT int Geqo_planner_extension_id; + #define DEFAULT_GEQO_SELECTION_BIAS 2.0 #define MIN_GEQO_SELECTION_BIAS 1.5 #define MAX_GEQO_SELECTION_BIAS 2.0 @@ -70,7 +73,7 @@ extern PGDLLIMPORT double Geqo_seed; /* 0 .. 1 */ /* - * Private state for a GEQO run --- accessible via root->join_search_private + * Private state for a GEQO run --- accessible via GetGeqoPrivateData */ typedef struct { @@ -78,6 +81,13 @@ typedef struct pg_prng_state random_state; /* PRNG state */ } GeqoPrivateData; +static inline GeqoPrivateData * +GetGeqoPrivateData(PlannerInfo *root) +{ + /* headers must be C++-compliant, so the cast is required here */ + return (GeqoPrivateData *) + GetPlannerInfoExtensionState(root, Geqo_planner_extension_id); +} /* routines in geqo_main.c */ extern RelOptInfo *geqo(PlannerInfo *root, diff --git a/src/include/optimizer/planner.h b/src/include/optimizer/planner.h index f220e9a270d..1bbef0018d5 100644 --- a/src/include/optimizer/planner.h +++ b/src/include/optimizer/planner.h @@ -43,6 +43,7 @@ extern PlannedStmt *standard_planner(Query *parse, const char *query_string, ParamListInfo boundParams); extern PlannerInfo *subquery_planner(PlannerGlobal *glob, Query *parse, + char *plan_name, PlannerInfo *parent_root, bool hasRecursion, double tuple_fraction, SetOperationStmt *setops); @@ -62,4 +63,7 @@ extern Expr *preprocess_phv_expression(PlannerInfo *root, Expr *expr); extern RelOptInfo *create_unique_paths(PlannerInfo *root, RelOptInfo *rel, SpecialJoinInfo *sjinfo); +extern char *choose_plan_name(PlannerGlobal *glob, const char *name, + bool always_number); + #endif /* PLANNER_H */ diff --git a/src/include/utils/float.h b/src/include/utils/float.h index 0e2e9ec5347..fa903b4bf59 100644 --- a/src/include/utils/float.h +++ b/src/include/utils/float.h @@ -51,62 +51,27 @@ extern char *float8out_internal(float8 num); extern int float4_cmp_internal(float4 a, float4 b); extern int float8_cmp_internal(float8 a, float8 b); -/* - * Routines to provide reasonably platform-independent handling of - * infinity and NaN - * - * We assume that isinf() and isnan() are available and work per spec. - * (On some platforms, we have to supply our own; see src/port.) However, - * generating an Infinity or NaN in the first place is less well standardized; - * pre-C99 systems tend not to have C99's INFINITY and NaN macros. We - * centralize our workarounds for this here. - */ - -/* - * The funny placements of the two #pragmas is necessary because of a - * long lived bug in the Microsoft compilers. - * See http://support.microsoft.com/kb/120968/en-us for details - */ -#ifdef _MSC_VER -#pragma warning(disable:4756) -#endif static inline float4 get_float4_infinity(void) { -#ifdef INFINITY /* C99 standard way */ return (float4) INFINITY; -#else -#ifdef _MSC_VER -#pragma warning(default:4756) -#endif - - /* - * On some platforms, HUGE_VAL is an infinity, elsewhere it's just the - * largest normal float8. We assume forcing an overflow will get us a - * true infinity. - */ - return (float4) (HUGE_VAL * HUGE_VAL); -#endif } static inline float8 get_float8_infinity(void) { -#ifdef INFINITY /* C99 standard way */ return (float8) INFINITY; -#else - - /* - * On some platforms, HUGE_VAL is an infinity, elsewhere it's just the - * largest normal float8. We assume forcing an overflow will get us a - * true infinity. - */ - return (float8) (HUGE_VAL * HUGE_VAL); -#endif } +/* + * Routines to provide reasonably platform-independent handling of NaN + * + * We assume that isnan() is available and work per spec. However, generating + * a NaN in the first place is less well standardized; pre-C99 systems tend + * not to have C99's NaN macro. We centralize our workaround for this here. + */ static inline float4 get_float4_nan(void) { diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out index 035f9a78206..bc83a6e188e 100644 --- a/src/test/regress/expected/aggregates.out +++ b/src/test/regress/expected/aggregates.out @@ -782,9 +782,9 @@ select array(select sum(x+y) s QUERY PLAN ------------------------------------------------------------------- Function Scan on pg_catalog.generate_series x - Output: ARRAY(SubPlan 1) + Output: ARRAY(SubPlan array_1) Function Call: generate_series(1, 3) - SubPlan 1 + SubPlan array_1 -> Sort Output: (sum((x.x + y.y))), y.y Sort Key: (sum((x.x + y.y))) @@ -960,7 +960,7 @@ explain (costs off) ------------------------------------------------------------ Result Replaces: MinMaxAggregate - InitPlan 1 + InitPlan minmax_1 -> Limit -> Index Only Scan using tenk1_unique1 on tenk1 Index Cond: (unique1 IS NOT NULL) @@ -978,7 +978,7 @@ explain (costs off) --------------------------------------------------------------------- Result Replaces: MinMaxAggregate - InitPlan 1 + InitPlan minmax_1 -> Limit -> Index Only Scan Backward using tenk1_unique1 on tenk1 Index Cond: (unique1 IS NOT NULL) @@ -996,7 +996,7 @@ explain (costs off) ------------------------------------------------------------------------ Result Replaces: MinMaxAggregate - InitPlan 1 + InitPlan minmax_1 -> Limit -> Index Only Scan Backward using tenk1_unique1 on tenk1 Index Cond: ((unique1 IS NOT NULL) AND (unique1 < 42)) @@ -1014,7 +1014,7 @@ explain (costs off) ------------------------------------------------------------------------ Result Replaces: MinMaxAggregate - InitPlan 1 + InitPlan minmax_1 -> Limit -> Index Only Scan Backward using tenk1_unique1 on tenk1 Index Cond: ((unique1 IS NOT NULL) AND (unique1 > 42)) @@ -1038,7 +1038,7 @@ explain (costs off) --------------------------------------------------------------------------- Result Replaces: MinMaxAggregate - InitPlan 1 + InitPlan minmax_1 -> Limit -> Index Only Scan Backward using tenk1_unique1 on tenk1 Index Cond: ((unique1 IS NOT NULL) AND (unique1 > 42000)) @@ -1058,7 +1058,7 @@ explain (costs off) ---------------------------------------------------------------------------- Result Replaces: MinMaxAggregate - InitPlan 1 + InitPlan minmax_1 -> Limit -> Index Only Scan Backward using tenk1_thous_tenthous on tenk1 Index Cond: ((thousand = 33) AND (tenthous IS NOT NULL)) @@ -1076,7 +1076,7 @@ explain (costs off) -------------------------------------------------------------------------- Result Replaces: MinMaxAggregate - InitPlan 1 + InitPlan minmax_1 -> Limit -> Index Only Scan using tenk1_thous_tenthous on tenk1 Index Cond: ((thousand = 33) AND (tenthous IS NOT NULL)) @@ -1095,10 +1095,10 @@ explain (costs off) QUERY PLAN ----------------------------------------------------------------------------------------- Seq Scan on int4_tbl - SubPlan 2 + SubPlan expr_1 -> Result Replaces: MinMaxAggregate - InitPlan 1 + InitPlan minmax_1 -> Limit -> Index Only Scan using tenk1_unique1 on tenk1 Index Cond: ((unique1 IS NOT NULL) AND (unique1 > int4_tbl.f1)) @@ -1121,8 +1121,8 @@ explain (costs off) QUERY PLAN --------------------------------------------------------------------- HashAggregate - Group Key: (InitPlan 1).col1 - InitPlan 1 + Group Key: (InitPlan minmax_1).col1 + InitPlan minmax_1 -> Limit -> Index Only Scan Backward using tenk1_unique2 on tenk1 Index Cond: (unique2 IS NOT NULL) @@ -1141,8 +1141,8 @@ explain (costs off) QUERY PLAN --------------------------------------------------------------------- Sort - Sort Key: ((InitPlan 1).col1) - InitPlan 1 + Sort Key: ((InitPlan minmax_1).col1) + InitPlan minmax_1 -> Limit -> Index Only Scan Backward using tenk1_unique2 on tenk1 Index Cond: (unique2 IS NOT NULL) @@ -1161,8 +1161,8 @@ explain (costs off) QUERY PLAN --------------------------------------------------------------------- Sort - Sort Key: ((InitPlan 1).col1) - InitPlan 1 + Sort Key: ((InitPlan minmax_1).col1) + InitPlan minmax_1 -> Limit -> Index Only Scan Backward using tenk1_unique2 on tenk1 Index Cond: (unique2 IS NOT NULL) @@ -1181,8 +1181,8 @@ explain (costs off) QUERY PLAN --------------------------------------------------------------------- Sort - Sort Key: (((InitPlan 1).col1 + 1)) - InitPlan 1 + Sort Key: (((InitPlan minmax_1).col1 + 1)) + InitPlan minmax_1 -> Limit -> Index Only Scan Backward using tenk1_unique2 on tenk1 Index Cond: (unique2 IS NOT NULL) @@ -1202,7 +1202,7 @@ explain (costs off) --------------------------------------------------------------------- Sort Sort Key: (generate_series(1, 3)) DESC - InitPlan 1 + InitPlan minmax_1 -> Limit -> Index Only Scan Backward using tenk1_unique2 on tenk1 Index Cond: (unique2 IS NOT NULL) @@ -1226,7 +1226,7 @@ explain (costs off) ---------------------------------------------------- Result Replaces: MinMaxAggregate - InitPlan 1 + InitPlan minmax_1 -> Limit -> Result One-Time Filter: (100 IS NOT NULL) @@ -1258,7 +1258,7 @@ explain (costs off) --------------------------------------------------------------------------------------------- Result Replaces: MinMaxAggregate - InitPlan 1 + InitPlan minmax_1 -> Limit -> Merge Append Sort Key: minmaxtest.f1 @@ -1269,7 +1269,7 @@ explain (costs off) -> Index Only Scan Backward using minmaxtest2i on minmaxtest2 minmaxtest_3 Index Cond: (f1 IS NOT NULL) -> Index Only Scan using minmaxtest3i on minmaxtest3 minmaxtest_4 - InitPlan 2 + InitPlan minmax_2 -> Limit -> Merge Append Sort Key: minmaxtest_5.f1 DESC @@ -1294,7 +1294,7 @@ explain (costs off) QUERY PLAN --------------------------------------------------------------------------------------------- Unique - InitPlan 1 + InitPlan minmax_1 -> Limit -> Merge Append Sort Key: minmaxtest.f1 @@ -1305,7 +1305,7 @@ explain (costs off) -> Index Only Scan Backward using minmaxtest2i on minmaxtest2 minmaxtest_3 Index Cond: (f1 IS NOT NULL) -> Index Only Scan using minmaxtest3i on minmaxtest3 minmaxtest_4 - InitPlan 2 + InitPlan minmax_2 -> Limit -> Merge Append Sort Key: minmaxtest_5.f1 DESC @@ -1317,7 +1317,7 @@ explain (costs off) Index Cond: (f1 IS NOT NULL) -> Index Only Scan Backward using minmaxtest3i on minmaxtest3 minmaxtest_9 -> Sort - Sort Key: ((InitPlan 1).col1), ((InitPlan 2).col1) + Sort Key: ((InitPlan minmax_1).col1), ((InitPlan minmax_2).col1) -> Result Replaces: MinMaxAggregate (27 rows) @@ -1342,10 +1342,10 @@ explain (costs off) QUERY PLAN --------------------------------------------------------------------- Seq Scan on int4_tbl t0 - SubPlan 2 + SubPlan expr_1 -> HashAggregate - Group Key: (InitPlan 1).col1 - InitPlan 1 + Group Key: (InitPlan minmax_1).col1 + InitPlan minmax_1 -> Limit -> Seq Scan on int4_tbl t1 Filter: ((f1 IS NOT NULL) AND (f1 = t0.f1)) diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out index 98e68e972be..c743fc769cb 100644 --- a/src/test/regress/expected/create_index.out +++ b/src/test/regress/expected/create_index.out @@ -593,7 +593,7 @@ SELECT point(x,x), (SELECT f1 FROM gpolygon_tbl ORDER BY f1 <-> point(x,x) LIMIT QUERY PLAN -------------------------------------------------------------------------------------------- Function Scan on generate_series x - SubPlan 1 + SubPlan expr_1 -> Limit -> Index Scan using ggpolygonind on gpolygon_tbl Order By: (f1 <-> point((x.x)::double precision, (x.x)::double precision)) @@ -1908,11 +1908,11 @@ SELECT * FROM tenk1 EXPLAIN (COSTS OFF) SELECT * FROM tenk1 WHERE thousand = 42 AND (tenthous = 1 OR tenthous = (SELECT 1 + 2) OR tenthous = 42); - QUERY PLAN ----------------------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------------------------- Index Scan using tenk1_thous_tenthous on tenk1 - Index Cond: ((thousand = 42) AND (tenthous = ANY (ARRAY[1, (InitPlan 1).col1, 42]))) - InitPlan 1 + Index Cond: ((thousand = 42) AND (tenthous = ANY (ARRAY[1, (InitPlan expr_1).col1, 42]))) + InitPlan expr_1 -> Result (4 rows) @@ -2043,8 +2043,8 @@ SELECT count(*) FROM tenk1 t1 ---------------------------------------------------------------------------- Aggregate -> Index Only Scan using tenk1_thous_tenthous on tenk1 t1 - Filter: ((thousand = 42) OR (thousand = (SubPlan 1))) - SubPlan 1 + Filter: ((thousand = 42) OR (thousand = (SubPlan expr_1))) + SubPlan expr_1 -> Limit -> Index Only Scan using tenk1_thous_tenthous on tenk1 t2 Index Cond: (thousand = (t1.tenthous + 1)) diff --git a/src/test/regress/expected/groupingsets.out b/src/test/regress/expected/groupingsets.out index 210bbe307a7..991121545c5 100644 --- a/src/test/regress/expected/groupingsets.out +++ b/src/test/regress/expected/groupingsets.out @@ -504,17 +504,17 @@ select grouping(ss.x) from int8_tbl i1 cross join lateral (select (select i1.q1) as x) ss group by ss.x; - QUERY PLAN ------------------------------------------------- + QUERY PLAN +---------------------------------------------------------- GroupAggregate - Output: GROUPING((SubPlan 1)), ((SubPlan 2)) - Group Key: ((SubPlan 2)) + Output: GROUPING((SubPlan expr_1)), ((SubPlan expr_2)) + Group Key: ((SubPlan expr_2)) -> Sort - Output: ((SubPlan 2)), i1.q1 - Sort Key: ((SubPlan 2)) + Output: ((SubPlan expr_2)), i1.q1 + Sort Key: ((SubPlan expr_2)) -> Seq Scan on public.int8_tbl i1 - Output: (SubPlan 2), i1.q1 - SubPlan 2 + Output: (SubPlan expr_2), i1.q1 + SubPlan expr_2 -> Result Output: i1.q1 (11 rows) @@ -534,22 +534,22 @@ select (select grouping(ss.x)) from int8_tbl i1 cross join lateral (select (select i1.q1) as x) ss group by ss.x; - QUERY PLAN --------------------------------------------- + QUERY PLAN +------------------------------------------------ GroupAggregate - Output: (SubPlan 2), ((SubPlan 3)) - Group Key: ((SubPlan 3)) + Output: (SubPlan expr_1), ((SubPlan expr_3)) + Group Key: ((SubPlan expr_3)) -> Sort - Output: ((SubPlan 3)), i1.q1 - Sort Key: ((SubPlan 3)) + Output: ((SubPlan expr_3)), i1.q1 + Sort Key: ((SubPlan expr_3)) -> Seq Scan on public.int8_tbl i1 - Output: (SubPlan 3), i1.q1 - SubPlan 3 + Output: (SubPlan expr_3), i1.q1 + SubPlan expr_3 -> Result Output: i1.q1 - SubPlan 2 + SubPlan expr_1 -> Result - Output: GROUPING((SubPlan 1)) + Output: GROUPING((SubPlan expr_2)) (14 rows) select (select grouping(ss.x)) @@ -592,7 +592,7 @@ explain (costs off) ------------------------------------------------------------ Result Replaces: MinMaxAggregate - InitPlan 1 + InitPlan minmax_1 -> Limit -> Index Only Scan using tenk1_unique1 on tenk1 Index Cond: (unique1 IS NOT NULL) @@ -881,7 +881,7 @@ explain (costs off) Sort Sort Key: "*VALUES*".column1 -> Values Scan on "*VALUES*" - SubPlan 1 + SubPlan expr_1 -> Aggregate Group Key: () Filter: "*VALUES*".column1 @@ -2169,17 +2169,17 @@ 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 cube(v1); - QUERY PLAN -------------------------------- + QUERY PLAN +------------------------------------ MixedAggregate - Hash Key: (InitPlan 3).col1 + Hash Key: (InitPlan expr_3).col1 Group Key: () - InitPlan 1 + InitPlan expr_2 -> Result - InitPlan 3 + InitPlan expr_3 -> Result -> Result - SubPlan 2 + SubPlan expr_1 -> Result (10 rows) @@ -2192,15 +2192,15 @@ 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 + InitPlan expr_2 -> Result - InitPlan 3 + InitPlan expr_3 -> Result -> Result - SubPlan 2 + SubPlan expr_1 -> Result (8 rows) @@ -2222,18 +2222,18 @@ order by case when grouping((select t1.v from gstest5 t2 where id = t1.id)) = 0 then (select t1.v from gstest5 t2 where id = t1.id) else null end nulls first; - QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort - Output: (GROUPING((SubPlan 1))), ((SubPlan 3)), (CASE WHEN (GROUPING((SubPlan 2)) = 0) THEN ((SubPlan 3)) ELSE NULL::integer END), t1.v - Sort Key: (CASE WHEN (GROUPING((SubPlan 2)) = 0) THEN ((SubPlan 3)) ELSE NULL::integer END) NULLS FIRST + Output: (GROUPING((SubPlan expr_1))), ((SubPlan expr_3)), (CASE WHEN (GROUPING((SubPlan expr_2)) = 0) THEN ((SubPlan expr_3)) ELSE NULL::integer END), t1.v + Sort Key: (CASE WHEN (GROUPING((SubPlan expr_2)) = 0) THEN ((SubPlan expr_3)) ELSE NULL::integer END) NULLS FIRST -> HashAggregate - Output: GROUPING((SubPlan 1)), ((SubPlan 3)), CASE WHEN (GROUPING((SubPlan 2)) = 0) THEN ((SubPlan 3)) ELSE NULL::integer END, t1.v + Output: GROUPING((SubPlan expr_1)), ((SubPlan expr_3)), CASE WHEN (GROUPING((SubPlan expr_2)) = 0) THEN ((SubPlan expr_3)) ELSE NULL::integer END, t1.v Hash Key: t1.v - Hash Key: (SubPlan 3) + Hash Key: (SubPlan expr_3) -> Seq Scan on pg_temp.gstest5 t1 - Output: (SubPlan 3), t1.v, t1.id - SubPlan 3 + Output: (SubPlan expr_3), t1.v, t1.id + SubPlan expr_3 -> Bitmap Heap Scan on pg_temp.gstest5 t2 Output: t1.v Recheck Cond: (t2.id = t1.id) @@ -2272,18 +2272,18 @@ select grouping((select t1.v from gstest5 t2 where id = t1.id)), from gstest5 t1 group by grouping sets(v, s) order by o nulls first; - QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort - Output: (GROUPING((SubPlan 1))), ((SubPlan 3)), (CASE WHEN (GROUPING((SubPlan 2)) = 0) THEN ((SubPlan 3)) ELSE NULL::integer END), t1.v - Sort Key: (CASE WHEN (GROUPING((SubPlan 2)) = 0) THEN ((SubPlan 3)) ELSE NULL::integer END) NULLS FIRST + Output: (GROUPING((SubPlan expr_1))), ((SubPlan expr_3)), (CASE WHEN (GROUPING((SubPlan expr_2)) = 0) THEN ((SubPlan expr_3)) ELSE NULL::integer END), t1.v + Sort Key: (CASE WHEN (GROUPING((SubPlan expr_2)) = 0) THEN ((SubPlan expr_3)) ELSE NULL::integer END) NULLS FIRST -> HashAggregate - Output: GROUPING((SubPlan 1)), ((SubPlan 3)), CASE WHEN (GROUPING((SubPlan 2)) = 0) THEN ((SubPlan 3)) ELSE NULL::integer END, t1.v + Output: GROUPING((SubPlan expr_1)), ((SubPlan expr_3)), CASE WHEN (GROUPING((SubPlan expr_2)) = 0) THEN ((SubPlan expr_3)) ELSE NULL::integer END, t1.v Hash Key: t1.v - Hash Key: (SubPlan 3) + Hash Key: (SubPlan expr_3) -> Seq Scan on pg_temp.gstest5 t1 - Output: (SubPlan 3), t1.v, t1.id - SubPlan 3 + Output: (SubPlan expr_3), t1.v, t1.id + SubPlan expr_3 -> Bitmap Heap Scan on pg_temp.gstest5 t2 Output: t1.v Recheck Cond: (t2.id = t1.id) diff --git a/src/test/regress/expected/incremental_sort.out b/src/test/regress/expected/incremental_sort.out index 5a1dd9fc022..fdec5b9ba52 100644 --- a/src/test/regress/expected/incremental_sort.out +++ b/src/test/regress/expected/incremental_sort.out @@ -1609,13 +1609,13 @@ from tenk1 t, generate_series(1, 1000); --------------------------------------------------------------------------------- Unique -> Sort - Sort Key: t.unique1, ((SubPlan 1)) + Sort Key: t.unique1, ((SubPlan expr_1)) -> Gather Workers Planned: 2 -> Nested Loop -> Parallel Index Only Scan using tenk1_unique1 on tenk1 t -> Function Scan on generate_series - SubPlan 1 + SubPlan expr_1 -> Index Only Scan using tenk1_unique1 on tenk1 Index Cond: (unique1 = t.unique1) (11 rows) @@ -1628,13 +1628,13 @@ order by 1, 2; QUERY PLAN --------------------------------------------------------------------------- Sort - Sort Key: t.unique1, ((SubPlan 1)) + Sort Key: t.unique1, ((SubPlan expr_1)) -> Gather Workers Planned: 2 -> Nested Loop -> Parallel Index Only Scan using tenk1_unique1 on tenk1 t -> Function Scan on generate_series - SubPlan 1 + SubPlan expr_1 -> Index Only Scan using tenk1_unique1 on tenk1 Index Cond: (unique1 = t.unique1) (10 rows) diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out index 031dd87424a..0490a746555 100644 --- a/src/test/regress/expected/inherit.out +++ b/src/test/regress/expected/inherit.out @@ -1758,9 +1758,9 @@ explain (verbose, costs off) select min(1-id) from matest0; QUERY PLAN --------------------------------------------------------------------------------- Result - Output: (InitPlan 1).col1 + Output: (InitPlan minmax_1).col1 Replaces: MinMaxAggregate - InitPlan 1 + InitPlan minmax_1 -> Limit Output: ((1 - matest0.id)) -> Result @@ -1948,7 +1948,7 @@ SELECT min(x) FROM -------------------------------------------------------------------- Result Replaces: MinMaxAggregate - InitPlan 1 + InitPlan minmax_1 -> Limit -> Merge Append Sort Key: a.unique1 @@ -1967,7 +1967,7 @@ SELECT min(y) FROM -------------------------------------------------------------------- Result Replaces: MinMaxAggregate - InitPlan 1 + InitPlan minmax_1 -> Limit -> Merge Append Sort Key: a.unique1 @@ -2008,7 +2008,7 @@ FROM generate_series(1, 3) g(i); QUERY PLAN ---------------------------------------------------------------- Function Scan on generate_series g - SubPlan 1 + SubPlan array_1 -> Limit -> Merge Append Sort Key: ((d.d + g.i)) @@ -2048,19 +2048,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) = (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: (SubPlan 1).col1, (SubPlan 1).col2, (rescan SubPlan 1), i.tableoid, i.ctid + Output: (SubPlan multiexpr_1).col1, (SubPlan multiexpr_1).col2, (rescan SubPlan multiexpr_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 + SubPlan multiexpr_1 -> Limit Output: (i.f1), (((i.f2)::text || '-'::text)) -> Seq Scan on public.int4_tbl @@ -2096,21 +2096,21 @@ alter table inhpar attach partition inhcld2 for values 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) = (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: (SubPlan 1).col1, (SubPlan 1).col2, (rescan SubPlan 1), i_1.tableoid, i_1.ctid - SubPlan 1 + Output: (SubPlan multiexpr_1).col1, (SubPlan multiexpr_1).col2, (rescan SubPlan multiexpr_1), i_1.tableoid, i_1.ctid + SubPlan multiexpr_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: (SubPlan 1).col1, (SubPlan 1).col2, (rescan SubPlan 1), i_2.tableoid, i_2.ctid + Output: (SubPlan multiexpr_1).col1, (SubPlan multiexpr_1).col2, (rescan SubPlan multiexpr_1), i_2.tableoid, i_2.ctid (13 rows) update inhpar i set (f1, f2) = (select i.f1, i.f2 || '-' from int4_tbl limit 1); @@ -3260,11 +3260,11 @@ explain (costs off) select min(a), max(a) from parted_minmax where b = '12345'; ------------------------------------------------------------------------------------------------ Result Replaces: MinMaxAggregate - InitPlan 1 + InitPlan minmax_1 -> Limit -> Index Only Scan using parted_minmax1i on parted_minmax1 parted_minmax Index Cond: ((a IS NOT NULL) AND (b = '12345'::text)) - InitPlan 2 + InitPlan minmax_2 -> Limit -> Index Only Scan Backward using parted_minmax1i on parted_minmax1 parted_minmax_1 Index Cond: ((a IS NOT NULL) AND (b = '12345'::text)) diff --git a/src/test/regress/expected/insert_conflict.out b/src/test/regress/expected/insert_conflict.out index fdd0f6c8f25..db668474684 100644 --- a/src/test/regress/expected/insert_conflict.out +++ b/src/test/regress/expected/insert_conflict.out @@ -61,9 +61,9 @@ explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on con Insert on insertconflicttest Conflict Resolution: UPDATE Conflict Arbiter Indexes: op_index_key, collation_index_key, both_index_key - Conflict Filter: EXISTS(SubPlan 1) + Conflict Filter: EXISTS(SubPlan exists_1) -> Result - SubPlan 1 + SubPlan exists_1 -> Index Only Scan using both_index_expr_key on insertconflicttest ii Index Cond: (key = excluded.key) (8 rows) diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index cd37f549b5a..14a6d7513aa 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -2375,7 +2375,7 @@ order by t1.unique1; Sort Sort Key: t1.unique1 -> Hash Join - Hash Cond: ((t1.two = t2.two) AND (t1.unique1 = (SubPlan 2))) + Hash Cond: ((t1.two = t2.two) AND (t1.unique1 = (SubPlan expr_1))) -> Bitmap Heap Scan on tenk1 t1 Recheck Cond: (unique1 < 10) -> Bitmap Index Scan on tenk1_unique1 @@ -2385,10 +2385,10 @@ order by t1.unique1; Recheck Cond: (unique1 < 10) -> Bitmap Index Scan on tenk1_unique1 Index Cond: (unique1 < 10) - SubPlan 2 + SubPlan expr_1 -> Result Replaces: MinMaxAggregate - InitPlan 1 + InitPlan minmax_1 -> Limit -> Index Only Scan using tenk1_unique1 on tenk1 Index Cond: ((unique1 IS NOT NULL) AND (unique1 = t2.unique1)) @@ -3181,11 +3181,11 @@ where unique1 in (select unique2 from tenk1 b); explain (costs off) select a.* from tenk1 a where unique1 not in (select unique2 from tenk1 b); - QUERY PLAN ------------------------------------------------------------ + QUERY PLAN +--------------------------------------------------------------- Seq Scan on tenk1 a - Filter: (NOT (ANY (unique1 = (hashed SubPlan 1).col1))) - SubPlan 1 + Filter: (NOT (ANY (unique1 = (hashed SubPlan any_1).col1))) + SubPlan any_1 -> Index Only Scan using tenk1_unique2 on tenk1 b (4 rows) @@ -3706,11 +3706,11 @@ order by 1,2; Sort Key: t1.q1, t1.q2 -> Hash Left Join Hash Cond: (t1.q2 = t2.q1) - Filter: (1 = (SubPlan 1)) + Filter: (1 = (SubPlan expr_1)) -> Seq Scan on int8_tbl t1 -> Hash -> Seq Scan on int8_tbl t2 - SubPlan 1 + SubPlan expr_1 -> Limit -> Result One-Time Filter: ((42) IS NOT NULL) @@ -4225,14 +4225,14 @@ from int8_tbl i8 right join (select false as z) ss3 on true, lateral (select i8.q2 as q2l where x limit 1) ss4 where i8.q2 = 123; - QUERY PLAN ----------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------- Nested Loop - Output: i8.q1, i8.q2, (InitPlan 1).col1, false, (i8.q2) - InitPlan 1 + Output: i8.q1, i8.q2, (InitPlan expr_1).col1, false, (i8.q2) + InitPlan expr_1 -> Result Output: true - InitPlan 2 + InitPlan expr_2 -> Result Output: true -> Seq Scan on public.int4_tbl i4 @@ -4241,7 +4241,7 @@ where i8.q2 = 123; -> Nested Loop Output: i8.q1, i8.q2, (i8.q2) -> Subquery Scan on ss1 - Output: ss1.y, (InitPlan 1).col1 + Output: ss1.y, (InitPlan expr_1).col1 -> Limit Output: NULL::integer -> Result @@ -4255,7 +4255,7 @@ where i8.q2 = 123; Output: (i8.q2) -> Result Output: i8.q2 - One-Time Filter: ((InitPlan 1).col1) + One-Time Filter: ((InitPlan expr_1).col1) (29 rows) explain (verbose, costs off) @@ -4268,14 +4268,14 @@ from int8_tbl i8 right join (select false as z) ss3 on true, lateral (select i8.q2 as q2l where x limit 1) ss4 where i8.q2 = 123; - QUERY PLAN ----------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------- Nested Loop - Output: i8.q1, i8.q2, (InitPlan 1).col1, false, (i8.q2) - InitPlan 1 + Output: i8.q1, i8.q2, (InitPlan expr_1).col1, false, (i8.q2) + InitPlan expr_1 -> Result Output: true - InitPlan 2 + InitPlan expr_2 -> Result Output: true -> Limit @@ -4285,7 +4285,7 @@ where i8.q2 = 123; -> Nested Loop Output: i8.q1, i8.q2, (i8.q2) -> Seq Scan on public.int4_tbl i4 - Output: i4.f1, (InitPlan 1).col1 + Output: i4.f1, (InitPlan expr_1).col1 Filter: (i4.f1 = 0) -> Nested Loop Output: i8.q1, i8.q2, (i8.q2) @@ -4296,7 +4296,7 @@ where i8.q2 = 123; Output: (i8.q2) -> Result Output: i8.q2 - One-Time Filter: ((InitPlan 1).col1) + One-Time Filter: ((InitPlan expr_1).col1) (27 rows) -- Test proper handling of appendrel PHVs during useless-RTE removal @@ -5757,13 +5757,13 @@ explain (costs off) select a.unique1, b.unique2 from onek a left join onek b on a.unique1 = b.unique2 where (b.unique2, random() > 0) = any (select q1, random() > 0 from int8_tbl c where c.q1 < b.unique1); - QUERY PLAN ------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------------------------------------------------------------------- Hash Join Hash Cond: (b.unique2 = a.unique1) -> Seq Scan on onek b - Filter: (ANY ((unique2 = (SubPlan 1).col1) AND ((random() > '0'::double precision) = (SubPlan 1).col2))) - SubPlan 1 + Filter: (ANY ((unique2 = (SubPlan any_1).col1) AND ((random() > '0'::double precision) = (SubPlan any_1).col2))) + SubPlan any_1 -> Seq Scan on int8_tbl c Filter: (q1 < b.unique1) -> Hash @@ -6105,7 +6105,7 @@ select exists( QUERY PLAN --------------------------------------------------------------------- Seq Scan on int4_tbl x0 - SubPlan 1 + SubPlan exists_1 -> Nested Loop Left Join Join Filter: (t2.q2 = t4.q2) -> Nested Loop Left Join @@ -6956,7 +6956,7 @@ where t1.a = t2.a; ------------------------------------------ Seq Scan on sj t2 Filter: (a IS NOT NULL) - SubPlan 1 + SubPlan expr_1 -> Result One-Time Filter: (t2.a = t2.a) -> Seq Scan on sj @@ -8983,8 +8983,8 @@ lateral (select * from int8_tbl t1, where q2 = (select greatest(t1.q1,t2.q2)) and (select v.id=0)) offset 0) ss2) ss where t1.q1 = ss.q2) ss0; - QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------ Nested Loop Output: "*VALUES*".column1, t1.q1, t1.q2, ss2.q1, ss2.q2 -> Seq Scan on public.int8_tbl t1 @@ -8998,20 +8998,20 @@ lateral (select * from int8_tbl t1, Filter: (t1.q1 = ss2.q2) -> Seq Scan on public.int8_tbl t2 Output: t2.q1, t2.q2 - Filter: (ANY ((t2.q1 = (SubPlan 3).col1) AND ((random() > '0'::double precision) = (SubPlan 3).col2))) - SubPlan 3 + Filter: (ANY ((t2.q1 = (SubPlan any_1).col1) AND ((random() > '0'::double precision) = (SubPlan any_1).col2))) + SubPlan any_1 -> Result Output: t3.q2, (random() > '0'::double precision) - One-Time Filter: (InitPlan 2).col1 - InitPlan 1 + One-Time Filter: (InitPlan expr_2).col1 + InitPlan expr_1 -> Result Output: GREATEST(t1.q1, t2.q2) - InitPlan 2 + InitPlan expr_2 -> Result Output: ("*VALUES*".column1 = 0) -> Seq Scan on public.int8_tbl t3 Output: t3.q1, t3.q2 - Filter: (t3.q2 = (InitPlan 1).col1) + Filter: (t3.q2 = (InitPlan expr_1).col1) (27 rows) select * from (values (0), (1)) v(id), @@ -9723,13 +9723,13 @@ SELECT * FROM rescan_bhs t1 LEFT JOIN rescan_bhs t2 ON t1.a IN QUERY PLAN ----------------------------------------------------------- Nested Loop Left Join - Join Filter: (ANY (t1.a = (SubPlan 1).col1)) + Join Filter: (ANY (t1.a = (SubPlan any_1).col1)) -> Bitmap Heap Scan on rescan_bhs t1 -> Bitmap Index Scan on rescan_bhs_a_idx -> Materialize -> Bitmap Heap Scan on rescan_bhs t2 -> Bitmap Index Scan on rescan_bhs_a_idx - SubPlan 1 + SubPlan any_1 -> Result One-Time Filter: (t2.a > 1) -> Bitmap Heap Scan on rescan_bhs t3 diff --git a/src/test/regress/expected/join_hash.out b/src/test/regress/expected/join_hash.out index 4fc34a0e72a..a45e1450040 100644 --- a/src/test/regress/expected/join_hash.out +++ b/src/test/regress/expected/join_hash.out @@ -1031,30 +1031,30 @@ WHERE ------------------------------------------------------------------------------------------------ Hash Join Output: hjtest_1.a, hjtest_2.a, (hjtest_1.tableoid)::regclass, (hjtest_2.tableoid)::regclass - Hash Cond: ((hjtest_1.id = (SubPlan 1)) AND ((SubPlan 2) = (SubPlan 3))) + Hash Cond: ((hjtest_1.id = (SubPlan expr_1)) AND ((SubPlan expr_2) = (SubPlan expr_3))) Join Filter: (hjtest_1.a <> hjtest_2.b) -> Seq Scan on public.hjtest_1 Output: hjtest_1.a, hjtest_1.tableoid, hjtest_1.id, hjtest_1.b - Filter: ((SubPlan 4) < 50) - SubPlan 4 + Filter: ((SubPlan expr_4) < 50) + SubPlan expr_4 -> Result Output: (hjtest_1.b * 5) -> Hash Output: hjtest_2.a, hjtest_2.tableoid, hjtest_2.id, hjtest_2.c, hjtest_2.b -> Seq Scan on public.hjtest_2 Output: hjtest_2.a, hjtest_2.tableoid, hjtest_2.id, hjtest_2.c, hjtest_2.b - Filter: ((SubPlan 5) < 55) - SubPlan 5 + Filter: ((SubPlan expr_5) < 55) + SubPlan expr_5 -> Result Output: (hjtest_2.c * 5) - SubPlan 1 + SubPlan expr_1 -> Result Output: 1 One-Time Filter: (hjtest_2.id = 1) - SubPlan 3 + SubPlan expr_3 -> Result Output: (hjtest_2.c * 5) - SubPlan 2 + SubPlan expr_2 -> Result Output: (hjtest_1.b * 5) (28 rows) @@ -1085,30 +1085,30 @@ WHERE ------------------------------------------------------------------------------------------------ Hash Join Output: hjtest_1.a, hjtest_2.a, (hjtest_1.tableoid)::regclass, (hjtest_2.tableoid)::regclass - Hash Cond: (((SubPlan 1) = hjtest_1.id) AND ((SubPlan 3) = (SubPlan 2))) + Hash Cond: (((SubPlan expr_1) = hjtest_1.id) AND ((SubPlan expr_3) = (SubPlan expr_2))) Join Filter: (hjtest_1.a <> hjtest_2.b) -> Seq Scan on public.hjtest_2 Output: hjtest_2.a, hjtest_2.tableoid, hjtest_2.id, hjtest_2.c, hjtest_2.b - Filter: ((SubPlan 5) < 55) - SubPlan 5 + Filter: ((SubPlan expr_5) < 55) + SubPlan expr_5 -> Result Output: (hjtest_2.c * 5) -> Hash Output: hjtest_1.a, hjtest_1.tableoid, hjtest_1.id, hjtest_1.b -> Seq Scan on public.hjtest_1 Output: hjtest_1.a, hjtest_1.tableoid, hjtest_1.id, hjtest_1.b - Filter: ((SubPlan 4) < 50) - SubPlan 4 + Filter: ((SubPlan expr_4) < 50) + SubPlan expr_4 -> Result Output: (hjtest_1.b * 5) - SubPlan 2 + SubPlan expr_2 -> Result Output: (hjtest_1.b * 5) - SubPlan 1 + SubPlan expr_1 -> Result Output: 1 One-Time Filter: (hjtest_2.id = 1) - SubPlan 3 + SubPlan expr_3 -> Result Output: (hjtest_2.c * 5) (28 rows) diff --git a/src/test/regress/expected/memoize.out b/src/test/regress/expected/memoize.out index fbcaf113266..00c30b91459 100644 --- a/src/test/regress/expected/memoize.out +++ b/src/test/regress/expected/memoize.out @@ -429,8 +429,8 @@ WHERE unique1 < 3 ---------------------------------------------------------------- Index Scan using tenk1_unique1 on tenk1 t0 Index Cond: (unique1 < 3) - Filter: EXISTS(SubPlan 1) - SubPlan 1 + Filter: EXISTS(SubPlan exists_1) + SubPlan exists_1 -> Nested Loop -> Index Scan using tenk1_hundred on tenk1 t2 Filter: (t0.two <> four) diff --git a/src/test/regress/expected/merge.out b/src/test/regress/expected/merge.out index 44df626c40c..9cb1d87066a 100644 --- a/src/test/regress/expected/merge.out +++ b/src/test/regress/expected/merge.out @@ -1828,29 +1828,29 @@ WHEN MATCHED AND t.c > s.cnt THEN -> Hash Join Output: t.ctid, s.a, s.b, s.c, s.d, s.ctid Hash Cond: (t.a = s.a) - Join Filter: (t.b < (SubPlan 1)) + Join Filter: (t.b < (SubPlan expr_1)) -> Seq Scan on public.tgt t Output: t.ctid, t.a, t.b -> Hash Output: s.a, s.b, s.c, s.d, s.ctid -> Seq Scan on public.src s Output: s.a, s.b, s.c, s.d, s.ctid - SubPlan 1 + SubPlan expr_1 -> Aggregate Output: count(*) -> Seq Scan on public.ref r Output: r.ab, r.cd Filter: ((r.ab = (s.a + s.b)) AND (r.cd = (s.c - s.d))) - SubPlan 4 + SubPlan expr_3 -> Aggregate Output: count(*) -> Seq Scan on public.ref r_2 Output: r_2.ab, r_2.cd Filter: ((r_2.ab = (s.a + s.b)) AND (r_2.cd = (s.c - s.d))) - SubPlan 3 + SubPlan multiexpr_1 -> Result - Output: s.b, (InitPlan 2).col1 - InitPlan 2 + Output: s.b, (InitPlan expr_2).col1 + InitPlan expr_2 -> Aggregate Output: count(*) -> Seq Scan on public.ref r_1 diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out index 7499cdb2cdf..deacdd75807 100644 --- a/src/test/regress/expected/partition_prune.out +++ b/src/test/regress/expected/partition_prune.out @@ -1915,21 +1915,21 @@ select * from from int4_tbl touter) ss, asptab where asptab.id > ss.b::int; - QUERY PLAN --------------------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------------------- Nested Loop -> Seq Scan on int4_tbl touter -> Append -> Index Only Scan using asptab0_pkey on asptab0 asptab_1 - Index Cond: (id > (EXISTS(SubPlan 3))::integer) - SubPlan 4 + Index Cond: (id > (EXISTS(SubPlan exists_3))::integer) + SubPlan exists_4 -> Seq Scan on int4_tbl tinner_2 -> Index Only Scan using asptab1_pkey on asptab1 asptab_2 - Index Cond: (id > (EXISTS(SubPlan 3))::integer) - SubPlan 3 + Index Cond: (id > (EXISTS(SubPlan exists_3))::integer) + SubPlan exists_3 -> Seq Scan on int4_tbl tinner_1 Filter: (f1 = touter.f1) - SubPlan 2 + SubPlan exists_2 -> Seq Scan on int4_tbl tinner (14 rows) @@ -2236,36 +2236,36 @@ explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q1 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, buffers off) execute ab_q2 (2, 2); - QUERY PLAN ------------------------------------------------------------------------ + QUERY PLAN +---------------------------------------------------------------------------- Append (actual rows=0.00 loops=1) Subplans Removed: 6 - InitPlan 1 + InitPlan expr_1 -> Result (actual rows=1.00 loops=1) -> Seq Scan on ab_a2_b1 ab_1 (actual rows=0.00 loops=1) - Filter: ((a >= $1) AND (a <= $2) AND (b < (InitPlan 1).col1)) + Filter: ((a >= $1) AND (a <= $2) AND (b < (InitPlan expr_1).col1)) -> Seq Scan on ab_a2_b2 ab_2 (actual rows=0.00 loops=1) - Filter: ((a >= $1) AND (a <= $2) AND (b < (InitPlan 1).col1)) + Filter: ((a >= $1) AND (a <= $2) AND (b < (InitPlan expr_1).col1)) -> Seq Scan on ab_a2_b3 ab_3 (never executed) - Filter: ((a >= $1) AND (a <= $2) AND (b < (InitPlan 1).col1)) + Filter: ((a >= $1) AND (a <= $2) AND (b < (InitPlan expr_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, buffers off) execute ab_q3 (2, 2); - QUERY PLAN ------------------------------------------------------------------------ + QUERY PLAN +---------------------------------------------------------------------------- Append (actual rows=0.00 loops=1) Subplans Removed: 6 - InitPlan 1 + InitPlan expr_1 -> Result (actual rows=1.00 loops=1) -> Seq Scan on ab_a1_b2 ab_1 (actual rows=0.00 loops=1) - Filter: ((b >= $1) AND (b <= $2) AND (a < (InitPlan 1).col1)) + Filter: ((b >= $1) AND (b <= $2) AND (a < (InitPlan expr_1).col1)) -> Seq Scan on ab_a2_b2 ab_2 (actual rows=0.00 loops=1) - Filter: ((b >= $1) AND (b <= $2) AND (a < (InitPlan 1).col1)) + Filter: ((b >= $1) AND (b <= $2) AND (a < (InitPlan expr_1).col1)) -> Seq Scan on ab_a3_b2 ab_3 (never executed) - Filter: ((b >= $1) AND (b <= $2) AND (a < (InitPlan 1).col1)) + Filter: ((b >= $1) AND (b <= $2) AND (a < (InitPlan expr_1).col1)) (10 rows) -- @@ -2475,23 +2475,23 @@ 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 = (select 1) or a = (select 3)) and b = 2'); - explain_parallel_append ------------------------------------------------------------------------------------------------- + explain_parallel_append +---------------------------------------------------------------------------------------------------------- Aggregate (actual rows=N loops=N) - InitPlan 1 + InitPlan expr_1 -> Result (actual rows=N loops=N) - InitPlan 2 + InitPlan expr_2 -> Result (actual rows=N loops=N) -> Gather (actual rows=N loops=N) Workers Planned: 2 Workers Launched: N -> Parallel Append (actual rows=N loops=N) -> Parallel Seq Scan on ab_a1_b2 ab_1 (actual rows=N loops=N) - Filter: ((b = 2) AND ((a = (InitPlan 1).col1) OR (a = (InitPlan 2).col1))) + Filter: ((b = 2) AND ((a = (InitPlan expr_1).col1) OR (a = (InitPlan expr_2).col1))) -> Parallel Seq Scan on ab_a2_b2 ab_2 (never executed) - Filter: ((b = 2) AND ((a = (InitPlan 1).col1) OR (a = (InitPlan 2).col1))) + Filter: ((b = 2) AND ((a = (InitPlan expr_1).col1) OR (a = (InitPlan expr_2).col1))) -> Parallel Seq Scan on ab_a3_b2 ab_3 (actual rows=N loops=N) - Filter: ((b = 2) AND ((a = (InitPlan 1).col1) OR (a = (InitPlan 2).col1))) + Filter: ((b = 2) AND ((a = (InitPlan expr_1).col1) OR (a = (InitPlan expr_2).col1))) (15 rows) -- Test pruning during parallel nested loop query @@ -2692,65 +2692,65 @@ select * from ab where a = (select max(a) from lprt_a) and b = (select max(a)-1 QUERY PLAN ---------------------------------------------------------------------------- Append (actual rows=0.00 loops=1) - InitPlan 1 + InitPlan expr_1 -> Aggregate (actual rows=1.00 loops=1) -> Seq Scan on lprt_a (actual rows=102.00 loops=1) - InitPlan 2 + InitPlan expr_2 -> Aggregate (actual rows=1.00 loops=1) -> Seq Scan on lprt_a lprt_a_1 (actual rows=102.00 loops=1) -> Bitmap Heap Scan on ab_a1_b1 ab_1 (never executed) - Recheck Cond: (a = (InitPlan 1).col1) - Filter: (b = (InitPlan 2).col1) + Recheck Cond: (a = (InitPlan expr_1).col1) + Filter: (b = (InitPlan expr_2).col1) -> Bitmap Index Scan on ab_a1_b1_a_idx (never executed) - Index Cond: (a = (InitPlan 1).col1) + Index Cond: (a = (InitPlan expr_1).col1) Index Searches: 0 -> Bitmap Heap Scan on ab_a1_b2 ab_2 (never executed) - Recheck Cond: (a = (InitPlan 1).col1) - Filter: (b = (InitPlan 2).col1) + Recheck Cond: (a = (InitPlan expr_1).col1) + Filter: (b = (InitPlan expr_2).col1) -> Bitmap Index Scan on ab_a1_b2_a_idx (never executed) - Index Cond: (a = (InitPlan 1).col1) + Index Cond: (a = (InitPlan expr_1).col1) Index Searches: 0 -> Bitmap Heap Scan on ab_a1_b3 ab_3 (never executed) - Recheck Cond: (a = (InitPlan 1).col1) - Filter: (b = (InitPlan 2).col1) + Recheck Cond: (a = (InitPlan expr_1).col1) + Filter: (b = (InitPlan expr_2).col1) -> Bitmap Index Scan on ab_a1_b3_a_idx (never executed) - Index Cond: (a = (InitPlan 1).col1) + Index Cond: (a = (InitPlan expr_1).col1) Index Searches: 0 -> Bitmap Heap Scan on ab_a2_b1 ab_4 (never executed) - Recheck Cond: (a = (InitPlan 1).col1) - Filter: (b = (InitPlan 2).col1) + Recheck Cond: (a = (InitPlan expr_1).col1) + Filter: (b = (InitPlan expr_2).col1) -> Bitmap Index Scan on ab_a2_b1_a_idx (never executed) - Index Cond: (a = (InitPlan 1).col1) + Index Cond: (a = (InitPlan expr_1).col1) Index Searches: 0 -> Bitmap Heap Scan on ab_a2_b2 ab_5 (never executed) - Recheck Cond: (a = (InitPlan 1).col1) - Filter: (b = (InitPlan 2).col1) + Recheck Cond: (a = (InitPlan expr_1).col1) + Filter: (b = (InitPlan expr_2).col1) -> Bitmap Index Scan on ab_a2_b2_a_idx (never executed) - Index Cond: (a = (InitPlan 1).col1) + Index Cond: (a = (InitPlan expr_1).col1) Index Searches: 0 -> Bitmap Heap Scan on ab_a2_b3 ab_6 (never executed) - Recheck Cond: (a = (InitPlan 1).col1) - Filter: (b = (InitPlan 2).col1) + Recheck Cond: (a = (InitPlan expr_1).col1) + Filter: (b = (InitPlan expr_2).col1) -> Bitmap Index Scan on ab_a2_b3_a_idx (never executed) - Index Cond: (a = (InitPlan 1).col1) + Index Cond: (a = (InitPlan expr_1).col1) Index Searches: 0 -> Bitmap Heap Scan on ab_a3_b1 ab_7 (never executed) - Recheck Cond: (a = (InitPlan 1).col1) - Filter: (b = (InitPlan 2).col1) + Recheck Cond: (a = (InitPlan expr_1).col1) + Filter: (b = (InitPlan expr_2).col1) -> Bitmap Index Scan on ab_a3_b1_a_idx (never executed) - Index Cond: (a = (InitPlan 1).col1) + Index Cond: (a = (InitPlan expr_1).col1) Index Searches: 0 -> Bitmap Heap Scan on ab_a3_b2 ab_8 (actual rows=0.00 loops=1) - Recheck Cond: (a = (InitPlan 1).col1) - Filter: (b = (InitPlan 2).col1) + Recheck Cond: (a = (InitPlan expr_1).col1) + Filter: (b = (InitPlan expr_2).col1) -> Bitmap Index Scan on ab_a3_b2_a_idx (actual rows=0.00 loops=1) - Index Cond: (a = (InitPlan 1).col1) + Index Cond: (a = (InitPlan expr_1).col1) Index Searches: 1 -> Bitmap Heap Scan on ab_a3_b3 ab_9 (never executed) - Recheck Cond: (a = (InitPlan 1).col1) - Filter: (b = (InitPlan 2).col1) + Recheck Cond: (a = (InitPlan expr_1).col1) + Filter: (b = (InitPlan expr_2).col1) -> Bitmap Index Scan on ab_a3_b3_a_idx (never executed) - Index Cond: (a = (InitPlan 1).col1) + Index Cond: (a = (InitPlan expr_1).col1) Index Searches: 0 (61 rows) @@ -2760,45 +2760,45 @@ select * from (select * from ab where a = 1 union all select * from ab) ab where QUERY PLAN ---------------------------------------------------------------------------------- Append (actual rows=0.00 loops=1) - InitPlan 1 + InitPlan expr_1 -> Result (actual rows=1.00 loops=1) -> Append (actual rows=0.00 loops=1) -> Bitmap Heap Scan on ab_a1_b1 ab_11 (actual rows=0.00 loops=1) Recheck Cond: (a = 1) - Filter: (b = (InitPlan 1).col1) + Filter: (b = (InitPlan expr_1).col1) -> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0.00 loops=1) Index Cond: (a = 1) Index Searches: 1 -> Bitmap Heap Scan on ab_a1_b2 ab_12 (never executed) Recheck Cond: (a = 1) - Filter: (b = (InitPlan 1).col1) + Filter: (b = (InitPlan expr_1).col1) -> Bitmap Index Scan on ab_a1_b2_a_idx (never executed) Index Cond: (a = 1) Index Searches: 0 -> Bitmap Heap Scan on ab_a1_b3 ab_13 (never executed) Recheck Cond: (a = 1) - Filter: (b = (InitPlan 1).col1) + Filter: (b = (InitPlan expr_1).col1) -> Bitmap Index Scan on ab_a1_b3_a_idx (never executed) Index Cond: (a = 1) Index Searches: 0 -> Seq Scan on ab_a1_b1 ab_1 (actual rows=0.00 loops=1) - Filter: (b = (InitPlan 1).col1) + Filter: (b = (InitPlan expr_1).col1) -> Seq Scan on ab_a1_b2 ab_2 (never executed) - Filter: (b = (InitPlan 1).col1) + Filter: (b = (InitPlan expr_1).col1) -> Seq Scan on ab_a1_b3 ab_3 (never executed) - Filter: (b = (InitPlan 1).col1) + Filter: (b = (InitPlan expr_1).col1) -> Seq Scan on ab_a2_b1 ab_4 (actual rows=0.00 loops=1) - Filter: (b = (InitPlan 1).col1) + Filter: (b = (InitPlan expr_1).col1) -> Seq Scan on ab_a2_b2 ab_5 (never executed) - Filter: (b = (InitPlan 1).col1) + Filter: (b = (InitPlan expr_1).col1) -> Seq Scan on ab_a2_b3 ab_6 (never executed) - Filter: (b = (InitPlan 1).col1) + Filter: (b = (InitPlan expr_1).col1) -> Seq Scan on ab_a3_b1 ab_7 (actual rows=0.00 loops=1) - Filter: (b = (InitPlan 1).col1) + Filter: (b = (InitPlan expr_1).col1) -> Seq Scan on ab_a3_b2 ab_8 (never executed) - Filter: (b = (InitPlan 1).col1) + Filter: (b = (InitPlan expr_1).col1) -> Seq Scan on ab_a3_b3 ab_9 (never executed) - Filter: (b = (InitPlan 1).col1) + Filter: (b = (InitPlan expr_1).col1) (40 rows) -- A case containing a UNION ALL with a non-partitioned child. @@ -2807,47 +2807,47 @@ select * from (select * from ab where a = 1 union all (values(10,5)) union all s QUERY PLAN ---------------------------------------------------------------------------------- Append (actual rows=0.00 loops=1) - InitPlan 1 + InitPlan expr_1 -> Result (actual rows=1.00 loops=1) -> Append (actual rows=0.00 loops=1) -> Bitmap Heap Scan on ab_a1_b1 ab_11 (actual rows=0.00 loops=1) Recheck Cond: (a = 1) - Filter: (b = (InitPlan 1).col1) + Filter: (b = (InitPlan expr_1).col1) -> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0.00 loops=1) Index Cond: (a = 1) Index Searches: 1 -> Bitmap Heap Scan on ab_a1_b2 ab_12 (never executed) Recheck Cond: (a = 1) - Filter: (b = (InitPlan 1).col1) + Filter: (b = (InitPlan expr_1).col1) -> Bitmap Index Scan on ab_a1_b2_a_idx (never executed) Index Cond: (a = 1) Index Searches: 0 -> Bitmap Heap Scan on ab_a1_b3 ab_13 (never executed) Recheck Cond: (a = 1) - Filter: (b = (InitPlan 1).col1) + Filter: (b = (InitPlan expr_1).col1) -> Bitmap Index Scan on ab_a1_b3_a_idx (never executed) Index Cond: (a = 1) Index Searches: 0 -> Result (actual rows=0.00 loops=1) - One-Time Filter: (5 = (InitPlan 1).col1) + One-Time Filter: (5 = (InitPlan expr_1).col1) -> Seq Scan on ab_a1_b1 ab_1 (actual rows=0.00 loops=1) - Filter: (b = (InitPlan 1).col1) + Filter: (b = (InitPlan expr_1).col1) -> Seq Scan on ab_a1_b2 ab_2 (never executed) - Filter: (b = (InitPlan 1).col1) + Filter: (b = (InitPlan expr_1).col1) -> Seq Scan on ab_a1_b3 ab_3 (never executed) - Filter: (b = (InitPlan 1).col1) + Filter: (b = (InitPlan expr_1).col1) -> Seq Scan on ab_a2_b1 ab_4 (actual rows=0.00 loops=1) - Filter: (b = (InitPlan 1).col1) + Filter: (b = (InitPlan expr_1).col1) -> Seq Scan on ab_a2_b2 ab_5 (never executed) - Filter: (b = (InitPlan 1).col1) + Filter: (b = (InitPlan expr_1).col1) -> Seq Scan on ab_a2_b3 ab_6 (never executed) - Filter: (b = (InitPlan 1).col1) + Filter: (b = (InitPlan expr_1).col1) -> Seq Scan on ab_a3_b1 ab_7 (actual rows=0.00 loops=1) - Filter: (b = (InitPlan 1).col1) + Filter: (b = (InitPlan expr_1).col1) -> Seq Scan on ab_a3_b2 ab_8 (never executed) - Filter: (b = (InitPlan 1).col1) + Filter: (b = (InitPlan expr_1).col1) -> Seq Scan on ab_a3_b3 ab_9 (never executed) - Filter: (b = (InitPlan 1).col1) + Filter: (b = (InitPlan expr_1).col1) (42 rows) -- Another UNION ALL test, but containing a mix of exec init and exec run-time pruning. @@ -2865,27 +2865,27 @@ union all ) ab where a = $1 and b = (select -10); -- Ensure the xy_1 subplan is not pruned. explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q6(1); - QUERY PLAN --------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------- Append (actual rows=0.00 loops=1) Subplans Removed: 12 - InitPlan 1 + InitPlan expr_1 -> Result (actual rows=1.00 loops=1) -> Seq Scan on ab_a1_b1 ab_1 (never executed) - Filter: ((a = $1) AND (b = (InitPlan 1).col1)) + Filter: ((a = $1) AND (b = (InitPlan expr_1).col1)) -> Seq Scan on ab_a1_b2 ab_2 (never executed) - Filter: ((a = $1) AND (b = (InitPlan 1).col1)) + Filter: ((a = $1) AND (b = (InitPlan expr_1).col1)) -> Seq Scan on ab_a1_b3 ab_3 (never executed) - Filter: ((a = $1) AND (b = (InitPlan 1).col1)) + Filter: ((a = $1) AND (b = (InitPlan expr_1).col1)) -> Seq Scan on xy_1 (actual rows=0.00 loops=1) - Filter: ((x = $1) AND (y = (InitPlan 1).col1)) + Filter: ((x = $1) AND (y = (InitPlan expr_1).col1)) Rows Removed by Filter: 1 -> Seq Scan on ab_a1_b1 ab_4 (never executed) - Filter: ((a = $1) AND (b = (InitPlan 1).col1)) + Filter: ((a = $1) AND (b = (InitPlan expr_1).col1)) -> Seq Scan on ab_a1_b2 ab_5 (never executed) - Filter: ((a = $1) AND (b = (InitPlan 1).col1)) + Filter: ((a = $1) AND (b = (InitPlan expr_1).col1)) -> Seq Scan on ab_a1_b3 ab_6 (never executed) - Filter: ((a = $1) AND (b = (InitPlan 1).col1)) + Filter: ((a = $1) AND (b = (InitPlan expr_1).col1)) (19 rows) -- Ensure we see just the xy_1 row. @@ -2971,7 +2971,7 @@ update ab_a1 set b = 3 from ab_a2 where ab_a2.b = (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 + InitPlan expr_1 -> Result (actual rows=1.00 loops=1) -> Nested Loop (actual rows=3.00 loops=1) -> Append (actual rows=3.00 loops=1) @@ -2982,11 +2982,11 @@ update ab_a1 set b = 3 from ab_a2 where ab_a2.b = (select 1);'); Storage: Memory Maximum Storage: NkB -> Append (actual rows=1.00 loops=1) -> Seq Scan on ab_a2_b1 ab_a2_1 (actual rows=1.00 loops=1) - Filter: (b = (InitPlan 1).col1) + Filter: (b = (InitPlan expr_1).col1) -> Seq Scan on ab_a2_b2 ab_a2_2 (never executed) - Filter: (b = (InitPlan 1).col1) + Filter: (b = (InitPlan expr_1).col1) -> Seq Scan on ab_a2_b3 ab_a2_3 (never executed) - Filter: (b = (InitPlan 1).col1) + Filter: (b = (InitPlan expr_1).col1) (20 rows) select tableoid::regclass, * from ab; @@ -3356,12 +3356,12 @@ select * from listp where a = (select null::int); QUERY PLAN ------------------------------------------------------ Append (actual rows=0.00 loops=1) - InitPlan 1 + InitPlan expr_1 -> Result (actual rows=1.00 loops=1) -> Seq Scan on listp_1_1 listp_1 (never executed) - Filter: (a = (InitPlan 1).col1) + Filter: (a = (InitPlan expr_1).col1) -> Seq Scan on listp_2_1 listp_2 (never executed) - Filter: (a = (InitPlan 1).col1) + Filter: (a = (InitPlan expr_1).col1) (7 rows) drop table listp; @@ -3500,14 +3500,14 @@ prepare ps1 as select * from mc3p where a = $1 and abs(b) < (select 3); explain (analyze, costs off, summary off, timing off, buffers off) execute ps1(1); - QUERY PLAN -------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------ Append (actual rows=1.00 loops=1) Subplans Removed: 2 - InitPlan 1 + InitPlan expr_1 -> Result (actual rows=1.00 loops=1) -> Seq Scan on mc3p1 mc3p_1 (actual rows=1.00 loops=1) - Filter: ((a = $1) AND (abs(b) < (InitPlan 1).col1)) + Filter: ((a = $1) AND (abs(b) < (InitPlan expr_1).col1)) (6 rows) deallocate ps1; @@ -3515,16 +3515,16 @@ prepare ps2 as select * from mc3p where a <= $1 and abs(b) < (select 3); explain (analyze, costs off, summary off, timing off, buffers off) execute ps2(1); - QUERY PLAN --------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------- Append (actual rows=2.00 loops=1) Subplans Removed: 1 - InitPlan 1 + InitPlan expr_1 -> Result (actual rows=1.00 loops=1) -> Seq Scan on mc3p0 mc3p_1 (actual rows=1.00 loops=1) - Filter: ((a <= $1) AND (abs(b) < (InitPlan 1).col1)) + Filter: ((a <= $1) AND (abs(b) < (InitPlan expr_1).col1)) -> Seq Scan on mc3p1 mc3p_2 (actual rows=1.00 loops=1) - Filter: ((a <= $1) AND (abs(b) < (InitPlan 1).col1)) + Filter: ((a <= $1) AND (abs(b) < (InitPlan expr_1).col1)) (8 rows) deallocate ps2; @@ -3540,14 +3540,14 @@ select * from boolp where a = (select value from boolvalues where value); QUERY PLAN -------------------------------------------------------------- Append (actual rows=0.00 loops=1) - InitPlan 1 + InitPlan expr_1 -> Seq Scan on boolvalues (actual rows=1.00 loops=1) Filter: value Rows Removed by Filter: 1 -> Seq Scan on boolp_f boolp_1 (never executed) - Filter: (a = (InitPlan 1).col1) + Filter: (a = (InitPlan expr_1).col1) -> Seq Scan on boolp_t boolp_2 (actual rows=0.00 loops=1) - Filter: (a = (InitPlan 1).col1) + Filter: (a = (InitPlan expr_1).col1) (9 rows) explain (analyze, costs off, summary off, timing off, buffers off) @@ -3555,14 +3555,14 @@ select * from boolp where a = (select value from boolvalues where not value); QUERY PLAN -------------------------------------------------------------- Append (actual rows=0.00 loops=1) - InitPlan 1 + InitPlan expr_1 -> Seq Scan on boolvalues (actual rows=1.00 loops=1) Filter: (NOT value) Rows Removed by Filter: 1 -> Seq Scan on boolp_f boolp_1 (actual rows=0.00 loops=1) - Filter: (a = (InitPlan 1).col1) + Filter: (a = (InitPlan expr_1).col1) -> Seq Scan on boolp_t boolp_2 (never executed) - Filter: (a = (InitPlan 1).col1) + Filter: (a = (InitPlan expr_1).col1) (9 rows) drop table boolp; @@ -3654,22 +3654,22 @@ explain (analyze, costs off, summary off, timing off, buffers off) select * from -------------------------------------------------------------------------------------------------- Merge Append (actual rows=20.00 loops=1) Sort Key: ma_test.b - InitPlan 2 + InitPlan expr_1 -> Result (actual rows=1.00 loops=1) Replaces: MinMaxAggregate - InitPlan 1 + InitPlan minmax_1 -> Limit (actual rows=1.00 loops=1) -> Index Scan using ma_test_p2_b_idx on ma_test_p2 (actual rows=1.00 loops=1) Index Cond: (b IS NOT NULL) Index Searches: 1 -> Index Scan using ma_test_p1_b_idx on ma_test_p1 ma_test_1 (never executed) - Filter: (a >= (InitPlan 2).col1) + Filter: (a >= (InitPlan expr_1).col1) Index Searches: 0 -> Index Scan using ma_test_p2_b_idx on ma_test_p2 ma_test_2 (actual rows=10.00 loops=1) - Filter: (a >= (InitPlan 2).col1) + Filter: (a >= (InitPlan expr_1).col1) Index Searches: 1 -> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_3 (actual rows=10.00 loops=1) - Filter: (a >= (InitPlan 2).col1) + Filter: (a >= (InitPlan expr_1).col1) Index Searches: 1 (19 rows) @@ -4043,17 +4043,17 @@ from ( select 1, 1, 1 ) s(a, b, c) where s.a = 1 and s.b = 1 and s.c = (select 1); - QUERY PLAN -------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------ Append - InitPlan 1 + InitPlan expr_1 -> Result -> Seq Scan on p1 p - Filter: ((a = 1) AND (b = 1) AND (c = (InitPlan 1).col1)) + Filter: ((a = 1) AND (b = 1) AND (c = (InitPlan expr_1).col1)) -> Seq Scan on q111 q1 - Filter: ((a = 1) AND (b = 1) AND (c = (InitPlan 1).col1)) + Filter: ((a = 1) AND (b = 1) AND (c = (InitPlan expr_1).col1)) -> Result - One-Time Filter: (1 = (InitPlan 1).col1) + One-Time Filter: (1 = (InitPlan expr_1).col1) (9 rows) select * @@ -4081,18 +4081,18 @@ from ( ) s(a, b, c) where s.a = $1 and s.b = $2 and s.c = (select 1); explain (costs off) execute q (1, 1); - QUERY PLAN ------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------- Append Subplans Removed: 1 - InitPlan 1 + InitPlan expr_1 -> Result -> Seq Scan on p1 p - Filter: ((a = $1) AND (b = $2) AND (c = (InitPlan 1).col1)) + Filter: ((a = $1) AND (b = $2) AND (c = (InitPlan expr_1).col1)) -> Seq Scan on q111 q1 - Filter: ((a = $1) AND (b = $2) AND (c = (InitPlan 1).col1)) + Filter: ((a = $1) AND (b = $2) AND (c = (InitPlan expr_1).col1)) -> Result - One-Time Filter: ((1 = $1) AND (1 = $2) AND (1 = (InitPlan 1).col1)) + One-Time Filter: ((1 = $1) AND (1 = $2) AND (1 = (InitPlan expr_1).col1)) (10 rows) execute q (1, 1); @@ -4110,11 +4110,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, buffers off) select * from listp where a = (select 2) and b <> 10; - QUERY PLAN ------------------------------------------------------ + QUERY PLAN +-------------------------------------------------------- Seq Scan on listp1 listp (actual rows=0.00 loops=1) - Filter: ((b <> 10) AND (a = (InitPlan 1).col1)) - InitPlan 1 + Filter: ((b <> 10) AND (a = (InitPlan expr_1).col1)) + InitPlan expr_1 -> Result (never executed) (4 rows) @@ -4182,13 +4182,13 @@ select explain_parallel_append('select * from listp where a = (select 1);'); Gather (actual rows=N loops=N) Workers Planned: 2 Workers Launched: N - InitPlan 1 + InitPlan expr_1 -> Result (actual rows=N loops=N) -> Parallel Append (actual rows=N loops=N) -> Seq Scan on listp_12_1 listp_1 (actual rows=N loops=N) - Filter: (a = (InitPlan 1).col1) + Filter: (a = (InitPlan expr_1).col1) -> Parallel Seq Scan on listp_12_2 listp_2 (never executed) - Filter: (a = (InitPlan 1).col1) + Filter: (a = (InitPlan expr_1).col1) (10 rows) -- Like the above but throw some more complexity at the planner by adding @@ -4205,19 +4205,19 @@ select * from listp where a = (select 2);'); Workers Launched: N -> Parallel Append (actual rows=N loops=N) -> Parallel Append (actual rows=N loops=N) - InitPlan 2 + InitPlan expr_2 -> Result (actual rows=N loops=N) -> Seq Scan on listp_12_1 listp_1 (never executed) - Filter: (a = (InitPlan 2).col1) + Filter: (a = (InitPlan expr_2).col1) -> Parallel Seq Scan on listp_12_2 listp_2 (actual rows=N loops=N) - Filter: (a = (InitPlan 2).col1) + Filter: (a = (InitPlan expr_2).col1) -> Parallel Append (actual rows=N loops=N) - InitPlan 1 + InitPlan expr_1 -> Result (actual rows=N loops=N) -> Seq Scan on listp_12_1 listp_4 (actual rows=N loops=N) - Filter: (a = (InitPlan 1).col1) + Filter: (a = (InitPlan expr_1).col1) -> Parallel Seq Scan on listp_12_2 listp_5 (never executed) - Filter: (a = (InitPlan 1).col1) + Filter: (a = (InitPlan expr_1).col1) (18 rows) drop table listp; @@ -4240,23 +4240,23 @@ select * from rangep where b IN((select 1),(select 2)) order by a; QUERY PLAN --------------------------------------------------------------------------------------------------------------- Append (actual rows=0.00 loops=1) - InitPlan 1 + InitPlan expr_1 -> Result (actual rows=1.00 loops=1) - InitPlan 2 + InitPlan expr_2 -> Result (actual rows=1.00 loops=1) -> Merge Append (actual rows=0.00 loops=1) 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=0.00 loops=1) - Filter: (b = ANY (ARRAY[(InitPlan 1).col1, (InitPlan 2).col1])) + Filter: (b = ANY (ARRAY[(InitPlan expr_1).col1, (InitPlan expr_2).col1])) Index Searches: 1 -> Index Scan using rangep_0_to_100_2_a_idx on rangep_0_to_100_2 rangep_3 (actual rows=0.00 loops=1) - Filter: (b = ANY (ARRAY[(InitPlan 1).col1, (InitPlan 2).col1])) + Filter: (b = ANY (ARRAY[(InitPlan expr_1).col1, (InitPlan expr_2).col1])) Index Searches: 1 -> Index Scan using rangep_0_to_100_3_a_idx on rangep_0_to_100_3 rangep_4 (never executed) - Filter: (b = ANY (ARRAY[(InitPlan 1).col1, (InitPlan 2).col1])) + Filter: (b = ANY (ARRAY[(InitPlan expr_1).col1, (InitPlan expr_2).col1])) Index Searches: 0 -> Index Scan using rangep_100_to_200_a_idx on rangep_100_to_200 rangep_5 (actual rows=0.00 loops=1) - Filter: (b = ANY (ARRAY[(InitPlan 1).col1, (InitPlan 2).col1])) + Filter: (b = ANY (ARRAY[(InitPlan expr_1).col1, (InitPlan expr_2).col1])) Index Searches: 1 (19 rows) diff --git a/src/test/regress/expected/portals.out b/src/test/regress/expected/portals.out index 06726ed4ab7..31f77abc446 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 + InitPlan expr_1 -> Result (3 rows) explain (costs off) declare c1 scroll cursor for select (select 42) as x; - QUERY PLAN ----------------- + QUERY PLAN +------------------- Materialize - InitPlan 1 + InitPlan expr_1 -> Result -> Result (4 rows) diff --git a/src/test/regress/expected/predicate.out b/src/test/regress/expected/predicate.out index 304b6868b90..66fb0854b88 100644 --- a/src/test/regress/expected/predicate.out +++ b/src/test/regress/expected/predicate.out @@ -247,11 +247,11 @@ SELECT * FROM pred_tab t1 QUERY PLAN --------------------------------------------------------- Nested Loop Left Join - Join Filter: EXISTS(SubPlan 1) + Join Filter: EXISTS(SubPlan exists_1) -> Seq Scan on pred_tab t1 -> Materialize -> Seq Scan on pred_tab t2 - SubPlan 1 + SubPlan exists_1 -> Nested Loop -> Nested Loop -> Nested Loop @@ -274,8 +274,8 @@ SELECT * FROM pred_tab t1 QUERY PLAN -------------------------------------------- Nested Loop Left Join - Join Filter: (InitPlan 1).col1 - InitPlan 1 + Join Filter: (InitPlan exists_1).col1 + InitPlan exists_1 -> Result Replaces: Join on t3, t4, t5, t6 One-Time Filter: false diff --git a/src/test/regress/expected/returning.out b/src/test/regress/expected/returning.out index 341b689f766..d02c2ceab53 100644 --- a/src/test/regress/expected/returning.out +++ b/src/test/regress/expected/returning.out @@ -548,16 +548,16 @@ INSERT INTO foo VALUES (5, 'subquery test') QUERY PLAN --------------------------------------------------------------- Insert on pg_temp.foo - Output: (SubPlan 1), (SubPlan 2) + Output: (SubPlan expr_1), (SubPlan expr_2) -> Result Output: 5, 'subquery test'::text, 42, '99'::bigint - SubPlan 1 + SubPlan expr_1 -> Aggregate Output: max((old.f4 + x.x)) -> Function Scan on pg_catalog.generate_series x Output: x.x Function Call: generate_series(1, 10) - SubPlan 2 + SubPlan expr_2 -> Aggregate Output: max((new.f4 + x_1.x)) -> Function Scan on pg_catalog.generate_series x_1 @@ -578,26 +578,26 @@ UPDATE foo SET f4 = 100 WHERE f1 = 5 RETURNING (SELECT old.f4 = new.f4), (SELECT max(old.f4 + x) FROM generate_series(1, 10) x) old_max, (SELECT max(new.f4 + x) FROM generate_series(1, 10) x) new_max; - QUERY PLAN ---------------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------------- Update on pg_temp.foo - Output: (SubPlan 1), (SubPlan 2), (SubPlan 3) + Output: (SubPlan expr_1), (SubPlan expr_2), (SubPlan expr_3) Update on pg_temp.foo foo_1 -> Result Output: '100'::bigint, foo_1.tableoid, foo_1.ctid -> Seq Scan on pg_temp.foo foo_1 Output: foo_1.tableoid, foo_1.ctid Filter: (foo_1.f1 = 5) - SubPlan 1 + SubPlan expr_1 -> Result Output: (old.f4 = new.f4) - SubPlan 2 + SubPlan expr_2 -> Aggregate Output: max((old.f4 + x.x)) -> Function Scan on pg_catalog.generate_series x Output: x.x Function Call: generate_series(1, 10) - SubPlan 3 + SubPlan expr_3 -> Aggregate Output: max((new.f4 + x_1.x)) -> Function Scan on pg_catalog.generate_series x_1 @@ -621,18 +621,18 @@ DELETE FROM foo WHERE f1 = 5 QUERY PLAN --------------------------------------------------------------- Delete on pg_temp.foo - Output: (SubPlan 1), (SubPlan 2) + Output: (SubPlan expr_1), (SubPlan expr_2) Delete on pg_temp.foo foo_1 -> Seq Scan on pg_temp.foo foo_1 Output: foo_1.tableoid, foo_1.ctid Filter: (foo_1.f1 = 5) - SubPlan 1 + SubPlan expr_1 -> Aggregate Output: max((old.f4 + x.x)) -> Function Scan on pg_catalog.generate_series x Output: x.x Function Call: generate_series(1, 10) - SubPlan 2 + SubPlan expr_2 -> Aggregate Output: max((new.f4 + x_1.x)) -> Function Scan on pg_catalog.generate_series x_1 diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out index 7153ebba521..5a172c5d91c 100644 --- a/src/test/regress/expected/rowsecurity.out +++ b/src/test/regress/expected/rowsecurity.out @@ -265,27 +265,27 @@ NOTICE: f_leak => 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 <= (InitPlan 1).col1) AND f_leak(dtitle)) - InitPlan 1 + Filter: ((dlevel <= (InitPlan expr_1).col1) AND f_leak(dtitle)) + InitPlan expr_1 -> Index Scan using uaccount_pkey on uaccount Index Cond: (pguser = 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 = document.cid) - InitPlan 1 + InitPlan expr_1 -> Index Scan using uaccount_pkey on uaccount Index Cond: (pguser = CURRENT_USER) -> Seq Scan on category -> Hash -> Seq Scan on document - Filter: ((dlevel <= (InitPlan 1).col1) AND f_leak(dtitle)) + Filter: ((dlevel <= (InitPlan expr_1).col1) AND f_leak(dtitle)) (9 rows) -- viewpoint from regress_rls_dave @@ -329,27 +329,27 @@ NOTICE: f_leak => 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 <= (InitPlan 1).col1) AND f_leak(dtitle)) - InitPlan 1 + Filter: ((cid <> 44) AND (cid <> 44) AND (cid < 50) AND (dlevel <= (InitPlan expr_1).col1) AND f_leak(dtitle)) + InitPlan expr_1 -> Index Scan using uaccount_pkey on uaccount Index Cond: (pguser = 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 = document.cid) - InitPlan 1 + InitPlan expr_1 -> Index Scan using uaccount_pkey on uaccount Index Cond: (pguser = CURRENT_USER) -> Seq Scan on category -> Hash -> Seq Scan on document - Filter: ((cid <> 44) AND (cid <> 44) AND (cid < 50) AND (dlevel <= (InitPlan 1).col1) AND f_leak(dtitle)) + Filter: ((cid <> 44) AND (cid <> 44) AND (cid < 50) AND (dlevel <= (InitPlan expr_1).col1) AND f_leak(dtitle)) (9 rows) -- 44 would technically fail for both p2r and p1r, but we should get an error @@ -987,18 +987,18 @@ NOTICE: f_leak => my first satire (4 rows) EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle); - QUERY PLAN --------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------- Append - InitPlan 1 + InitPlan expr_1 -> Index Scan using uaccount_pkey on uaccount Index Cond: (pguser = CURRENT_USER) -> Seq Scan on part_document_fiction part_document_1 - Filter: ((dlevel <= (InitPlan 1).col1) AND f_leak(dtitle)) + Filter: ((dlevel <= (InitPlan expr_1).col1) AND f_leak(dtitle)) -> Seq Scan on part_document_satire part_document_2 - Filter: ((dlevel <= (InitPlan 1).col1) AND f_leak(dtitle)) + Filter: ((dlevel <= (InitPlan expr_1).col1) AND f_leak(dtitle)) -> Seq Scan on part_document_nonfiction part_document_3 - Filter: ((dlevel <= (InitPlan 1).col1) AND f_leak(dtitle)) + Filter: ((dlevel <= (InitPlan expr_1).col1) AND f_leak(dtitle)) (10 rows) -- viewpoint from regress_rls_carol @@ -1029,18 +1029,18 @@ NOTICE: f_leak => awesome technology book (10 rows) EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle); - QUERY PLAN --------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------- Append - InitPlan 1 + InitPlan expr_1 -> Index Scan using uaccount_pkey on uaccount Index Cond: (pguser = CURRENT_USER) -> Seq Scan on part_document_fiction part_document_1 - Filter: ((dlevel <= (InitPlan 1).col1) AND f_leak(dtitle)) + Filter: ((dlevel <= (InitPlan expr_1).col1) AND f_leak(dtitle)) -> Seq Scan on part_document_satire part_document_2 - Filter: ((dlevel <= (InitPlan 1).col1) AND f_leak(dtitle)) + Filter: ((dlevel <= (InitPlan expr_1).col1) AND f_leak(dtitle)) -> Seq Scan on part_document_nonfiction part_document_3 - Filter: ((dlevel <= (InitPlan 1).col1) AND f_leak(dtitle)) + Filter: ((dlevel <= (InitPlan expr_1).col1) AND f_leak(dtitle)) (10 rows) -- viewpoint from regress_rls_dave @@ -1059,11 +1059,11 @@ NOTICE: f_leak => 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 <= (InitPlan 1).col1) AND f_leak(dtitle)) - InitPlan 1 + Filter: ((cid < 55) AND (dlevel <= (InitPlan expr_1).col1) AND f_leak(dtitle)) + InitPlan expr_1 -> Index Scan using uaccount_pkey on uaccount Index Cond: (pguser = CURRENT_USER) (5 rows) @@ -1137,11 +1137,11 @@ NOTICE: f_leak => 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 <= (InitPlan 1).col1) AND f_leak(dtitle)) - InitPlan 1 + Filter: ((cid < 55) AND (dlevel <= (InitPlan expr_1).col1) AND f_leak(dtitle)) + InitPlan expr_1 -> Index Scan using uaccount_pkey on uaccount Index Cond: (pguser = CURRENT_USER) (5 rows) @@ -1176,18 +1176,18 @@ NOTICE: f_leak => awesome technology book (11 rows) EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle); - QUERY PLAN --------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------- Append - InitPlan 1 + InitPlan expr_1 -> Index Scan using uaccount_pkey on uaccount Index Cond: (pguser = CURRENT_USER) -> Seq Scan on part_document_fiction part_document_1 - Filter: ((dlevel <= (InitPlan 1).col1) AND f_leak(dtitle)) + Filter: ((dlevel <= (InitPlan expr_1).col1) AND f_leak(dtitle)) -> Seq Scan on part_document_satire part_document_2 - Filter: ((dlevel <= (InitPlan 1).col1) AND f_leak(dtitle)) + Filter: ((dlevel <= (InitPlan expr_1).col1) AND f_leak(dtitle)) -> Seq Scan on part_document_nonfiction part_document_3 - Filter: ((dlevel <= (InitPlan 1).col1) AND f_leak(dtitle)) + Filter: ((dlevel <= (InitPlan expr_1).col1) AND f_leak(dtitle)) (10 rows) -- only owner can change policies @@ -1437,11 +1437,11 @@ NOTICE: f_leak => 03b26944890929ff751653acb2f2af79 (1 row) EXPLAIN (COSTS OFF) SELECT * FROM only s1 WHERE f_leak(b); - QUERY PLAN ---------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------- Seq Scan on s1 - Filter: ((ANY (a = (hashed SubPlan 1).col1)) AND f_leak(b)) - SubPlan 1 + Filter: ((ANY (a = (hashed SubPlan any_1).col1)) AND f_leak(b)) + SubPlan any_1 -> Seq Scan on s2 Filter: (((x % 2) = 0) AND (y ~~ '%2f%'::text)) (5 rows) @@ -1457,11 +1457,11 @@ NOTICE: f_leak => 03b26944890929ff751653acb2f2af79 (1 row) EXPLAIN (COSTS OFF) SELECT * FROM s1 WHERE f_leak(b); - QUERY PLAN ---------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------- Seq Scan on s1 - Filter: ((ANY (a = (hashed SubPlan 1).col1)) AND f_leak(b)) - SubPlan 1 + Filter: ((ANY (a = (hashed SubPlan any_1).col1)) AND f_leak(b)) + SubPlan any_1 -> Seq Scan on s2 Filter: (((x % 2) = 0) AND (y ~~ '%af%'::text)) (5 rows) @@ -1477,11 +1477,11 @@ EXPLAIN (COSTS OFF) SELECT (SELECT x FROM s1 LIMIT 1) xx, * FROM s2 WHERE y like ------------------------------------------------------------------------- Seq Scan on s2 Filter: (((x % 2) = 0) AND (y ~~ '%28%'::text)) - SubPlan 2 + SubPlan expr_1 -> Limit -> Seq Scan on s1 - Filter: (ANY (a = (hashed SubPlan 1).col1)) - SubPlan 1 + Filter: (ANY (a = (hashed SubPlan any_1).col1)) + SubPlan any_1 -> Seq Scan on s2 s2_1 Filter: (((x % 2) = 0) AND (y ~~ '%af%'::text)) (9 rows) @@ -2717,11 +2717,11 @@ NOTICE: f_leak => bbb (1 row) EXPLAIN (COSTS OFF) SELECT * FROM rls_view; - QUERY PLAN ---------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------- Seq Scan on z1 - Filter: ((NOT (ANY (a = (hashed SubPlan 1).col1))) AND ((a % 2) = 0) AND f_leak(b)) - SubPlan 1 + Filter: ((NOT (ANY (a = (hashed SubPlan any_1).col1))) AND ((a % 2) = 0) AND f_leak(b)) + SubPlan any_1 -> Seq Scan on z1_blacklist (4 rows) @@ -2735,11 +2735,11 @@ NOTICE: f_leak => bbb (1 row) EXPLAIN (COSTS OFF) SELECT * FROM rls_view; - QUERY PLAN ---------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------- Seq Scan on z1 - Filter: ((NOT (ANY (a = (hashed SubPlan 1).col1))) AND ((a % 2) = 0) AND f_leak(b)) - SubPlan 1 + Filter: ((NOT (ANY (a = (hashed SubPlan any_1).col1))) AND ((a % 2) = 0) AND f_leak(b)) + SubPlan any_1 -> Seq Scan on z1_blacklist (4 rows) @@ -2907,11 +2907,11 @@ NOTICE: f_leak => bbb (1 row) EXPLAIN (COSTS OFF) SELECT * FROM rls_view; - QUERY PLAN ---------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------- Seq Scan on z1 - Filter: ((NOT (ANY (a = (hashed SubPlan 1).col1))) AND ((a % 2) = 0) AND f_leak(b)) - SubPlan 1 + Filter: ((NOT (ANY (a = (hashed SubPlan any_1).col1))) AND ((a % 2) = 0) AND f_leak(b)) + SubPlan any_1 -> Seq Scan on z1_blacklist (4 rows) @@ -2933,11 +2933,11 @@ NOTICE: f_leak => aba (1 row) EXPLAIN (COSTS OFF) SELECT * FROM rls_view; - QUERY PLAN ---------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------- Seq Scan on z1 - Filter: ((NOT (ANY (a = (hashed SubPlan 1).col1))) AND ((a % 2) = 1) AND f_leak(b)) - SubPlan 1 + Filter: ((NOT (ANY (a = (hashed SubPlan any_1).col1))) AND ((a % 2) = 1) AND f_leak(b)) + SubPlan any_1 -> Seq Scan on z1_blacklist (4 rows) diff --git a/src/test/regress/expected/rowtypes.out b/src/test/regress/expected/rowtypes.out index dd52d96d50f..677ad2ab9ad 100644 --- a/src/test/regress/expected/rowtypes.out +++ b/src/test/regress/expected/rowtypes.out @@ -1251,19 +1251,19 @@ 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) + Filter: ((SubPlan expr_1) IS NOT NULL) CTE cte -> Result Output: '(1,2)'::record - SubPlan 3 + SubPlan expr_1 -> Result Output: cte.c - One-Time Filter: (InitPlan 2).col1 - InitPlan 2 + One-Time Filter: (InitPlan expr_2).col1 + InitPlan expr_2 -> Result Output: ((cte.c).f1 > 0) (13 rows) diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out index 0185ef661b1..933921d1860 100644 --- a/src/test/regress/expected/select_parallel.out +++ b/src/test/regress/expected/select_parallel.out @@ -156,9 +156,9 @@ explain (costs off) -> Parallel Append -> Parallel Seq Scan on part_pa_test_p1 pa2_1 -> Parallel Seq Scan on part_pa_test_p2 pa2_2 - SubPlan 2 + SubPlan expr_1 -> Result - SubPlan 1 + SubPlan expr_2 -> Append -> Seq Scan on part_pa_test_p1 pa1_1 Filter: (a = pa2.a) @@ -302,15 +302,15 @@ alter table tenk2 set (parallel_workers = 0); explain (costs off) select count(*) from tenk1 where (two, four) not in (select hundred, thousand from tenk2 where thousand > 100); - QUERY PLAN ----------------------------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------ Finalize Aggregate -> Gather Workers Planned: 4 -> Partial Aggregate -> Parallel Seq Scan on tenk1 - Filter: (NOT (ANY ((two = (hashed SubPlan 1).col1) AND (four = (hashed SubPlan 1).col2)))) - SubPlan 1 + Filter: (NOT (ANY ((two = (hashed SubPlan any_1).col1) AND (four = (hashed SubPlan any_1).col2)))) + SubPlan any_1 -> Seq Scan on tenk2 Filter: (thousand > 100) (9 rows) @@ -326,11 +326,11 @@ select count(*) from tenk1 where (two, four) not in explain (costs off) select * from tenk1 where (unique1 + random())::integer not in (select ten from tenk2); - QUERY PLAN -------------------------------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------------------- Seq Scan on tenk1 - Filter: (NOT (ANY ((((unique1)::double precision + random()))::integer = (hashed SubPlan 1).col1))) - SubPlan 1 + Filter: (NOT (ANY ((((unique1)::double precision + random()))::integer = (hashed SubPlan any_1).col1))) + SubPlan any_1 -> Seq Scan on tenk2 (4 rows) @@ -343,10 +343,10 @@ alter table tenk2 set (parallel_workers = 2); explain (costs off) select count(*) from tenk1 where tenk1.unique1 = (Select max(tenk2.unique1) from tenk2); - QUERY PLAN ------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------- Aggregate - InitPlan 1 + InitPlan expr_1 -> Finalize Aggregate -> Gather Workers Planned: 2 @@ -355,7 +355,7 @@ explain (costs off) -> Gather Workers Planned: 4 -> Parallel Seq Scan on tenk1 - Filter: (unique1 = (InitPlan 1).col1) + Filter: (unique1 = (InitPlan expr_1).col1) (11 rows) select count(*) from tenk1 @@ -395,17 +395,17 @@ select count((unique1)) from tenk1 where hundred > 1; explain (costs off) select count((unique1)) from tenk1 where hundred = any ((select array_agg(i) from generate_series(1, 100, 15) i)::int[]); - QUERY PLAN ---------------------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------------------- Finalize Aggregate - InitPlan 1 + InitPlan expr_1 -> Aggregate -> Function Scan on generate_series i -> Gather Workers Planned: 4 -> Partial Aggregate -> Parallel Index Scan using tenk1_hundred on tenk1 - Index Cond: (hundred = ANY ((InitPlan 1).col1)) + Index Cond: (hundred = ANY ((InitPlan expr_1).col1)) (9 rows) select count((unique1)) from tenk1 @@ -1224,24 +1224,24 @@ ORDER BY 1; -> Append -> Gather Workers Planned: 4 - InitPlan 1 + InitPlan expr_1 -> Limit -> Gather Workers Planned: 4 -> Parallel Seq Scan on tenk1 tenk1_2 Filter: (fivethous = 1) -> Parallel Seq Scan on tenk1 - Filter: (fivethous = (InitPlan 1).col1) + Filter: (fivethous = (InitPlan expr_1).col1) -> Gather Workers Planned: 4 - InitPlan 2 + InitPlan expr_2 -> Limit -> Gather Workers Planned: 4 -> Parallel Seq Scan on tenk1 tenk1_3 Filter: (fivethous = 1) -> Parallel Seq Scan on tenk1 tenk1_1 - Filter: (fivethous = (InitPlan 2).col1) + Filter: (fivethous = (InitPlan expr_2).col1) (23 rows) -- test interaction with SRFs @@ -1254,10 +1254,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), ARRAY(SubPlan 1), tenk1.tenthous + Output: generate_series(1, tenk1.two), ARRAY(SubPlan array_1), tenk1.tenthous -> Gather Merge Output: tenk1.two, tenk1.tenthous Workers Planned: 4 @@ -1268,7 +1268,7 @@ SELECT generate_series(1, two), array(select generate_series(1, two)) Sort Key: tenk1.tenthous -> Parallel Seq Scan on public.tenk1 Output: tenk1.tenthous, tenk1.two - SubPlan 1 + SubPlan array_1 -> ProjectSet Output: generate_series(1, tenk1.two) -> Result @@ -1333,11 +1333,11 @@ SELECT 1 FROM tenk1_vw_sec QUERY PLAN ------------------------------------------------------------------- Subquery Scan on tenk1_vw_sec - Filter: ((SubPlan 1) < 100) + Filter: ((SubPlan expr_1) < 100) -> Gather Workers Planned: 4 -> Parallel Index Only Scan using tenk1_unique1 on tenk1 - SubPlan 1 + SubPlan expr_1 -> Aggregate -> Seq Scan on int4_tbl Filter: (f1 < tenk1_vw_sec.unique1) diff --git a/src/test/regress/expected/sqljson.out b/src/test/regress/expected/sqljson.out index 625acf3019a..c7b9e575445 100644 --- a/src/test/regress/expected/sqljson.out +++ b/src/test/regress/expected/sqljson.out @@ -1093,8 +1093,8 @@ SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) RETURNING QUERY PLAN --------------------------------------------------------------------- Result - Output: (InitPlan 1).col1 - InitPlan 1 + Output: (InitPlan expr_1).col1 + InitPlan expr_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/expected/subselect.out index 47b2af7b2e1..cf6b32d1173 100644 --- a/src/test/regress/expected/subselect.out +++ b/src/test/regress/expected/subselect.out @@ -205,11 +205,11 @@ SELECT f1 AS "Correlated Field" -- Check ROWCOMPARE cases, both correlated and not EXPLAIN (VERBOSE, COSTS OFF) SELECT ROW(1, 2) = (SELECT f1, f2) AS eq FROM SUBSELECT_TBL; - QUERY PLAN ------------------------------------------------------------------ + QUERY PLAN +--------------------------------------------------------------------------------------- Seq Scan on public.subselect_tbl - Output: (((1 = (SubPlan 1).col1) AND (2 = (SubPlan 1).col2))) - SubPlan 1 + Output: (((1 = (SubPlan rowcompare_1).col1) AND (2 = (SubPlan rowcompare_1).col2))) + SubPlan rowcompare_1 -> Result Output: subselect_tbl.f1, subselect_tbl.f2 (5 rows) @@ -229,11 +229,11 @@ SELECT ROW(1, 2) = (SELECT f1, f2) AS eq FROM SUBSELECT_TBL; EXPLAIN (VERBOSE, COSTS OFF) SELECT ROW(1, 2) = (SELECT 3, 4) AS eq FROM SUBSELECT_TBL; - QUERY PLAN ------------------------------------------------------------------ + QUERY PLAN +--------------------------------------------------------------------------------------- Seq Scan on public.subselect_tbl - Output: ((1 = (InitPlan 1).col1) AND (2 = (InitPlan 1).col2)) - InitPlan 1 + Output: ((1 = (InitPlan rowcompare_1).col1) AND (2 = (InitPlan rowcompare_1).col2)) + InitPlan rowcompare_1 -> Result Output: 3, 4 (5 rows) @@ -375,18 +375,18 @@ explain (verbose, costs off) select '42' union all select 43; -- check materialization of an initplan reference (bug #14524) explain (verbose, costs off) select 1 = all (select (select 1)); - QUERY PLAN -------------------------------------------- + QUERY PLAN +------------------------------------------------ Result - Output: (ALL (1 = (SubPlan 2).col1)) - SubPlan 2 + Output: (ALL (1 = (SubPlan all_1).col1)) + SubPlan all_1 -> Materialize - Output: ((InitPlan 1).col1) - InitPlan 1 + Output: ((InitPlan expr_1).col1) + InitPlan expr_1 -> Result Output: 1 -> Result - Output: (InitPlan 1).col1 + Output: (InitPlan expr_1).col1 (10 rows) select 1 = all (select (select 1)); @@ -428,8 +428,8 @@ select * from int4_tbl o where exists QUERY PLAN -------------------------------------- Seq Scan on int4_tbl o - Filter: EXISTS(SubPlan 1) - SubPlan 1 + Filter: EXISTS(SubPlan exists_1) + SubPlan exists_1 -> Limit -> Seq Scan on int4_tbl i Filter: (f1 = o.f1) @@ -988,7 +988,7 @@ select (1 = any(array_agg(f1))) = any (select false) from int4_tbl; ---------------------------- Aggregate -> Seq Scan on int4_tbl - SubPlan 1 + SubPlan any_1 -> Result (4 rows) @@ -1116,11 +1116,11 @@ select * from outer_text where (f1, f2) not in (select * from inner_text); -- explain (verbose, costs off) select 'foo'::text in (select 'bar'::name union all select 'bar'::name); - QUERY PLAN ---------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------- Result - Output: (ANY ('foo'::text = (hashed SubPlan 1).col1)) - SubPlan 1 + Output: (ANY ('foo'::text = (hashed SubPlan any_1).col1)) + SubPlan any_1 -> Append -> Result Output: 'bar'::name @@ -1140,11 +1140,11 @@ select 'foo'::text in (select 'bar'::name union all select 'bar'::name); -- explain (verbose, costs off) select row(row(row(1))) = any (select row(row(1))); - QUERY PLAN --------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------ Result - Output: (ANY ('("(1)")'::record = (SubPlan 1).col1)) - SubPlan 1 + Output: (ANY ('("(1)")'::record = (SubPlan any_1).col1)) + SubPlan any_1 -> Materialize Output: '("(1)")'::record -> Result @@ -1184,11 +1184,11 @@ language sql as 'select $1::text = $2'; create operator = (procedure=bogus_int8_text_eq, leftarg=int8, rightarg=text); explain (costs off) select * from int8_tbl where q1 in (select c1 from inner_text); - QUERY PLAN --------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------ Seq Scan on int8_tbl - Filter: (ANY ((q1)::text = (hashed SubPlan 1).col1)) - SubPlan 1 + Filter: (ANY ((q1)::text = (hashed SubPlan any_1).col1)) + SubPlan any_1 -> Seq Scan on inner_text (4 rows) @@ -1205,11 +1205,11 @@ create or replace function bogus_int8_text_eq(int8, text) returns boolean language sql as 'select $1::text = $2 and $1::text = $2'; explain (costs off) select * from int8_tbl where q1 in (select c1 from inner_text); - QUERY PLAN ------------------------------------------------------------------------------------------------------ + QUERY PLAN +------------------------------------------------------------------------------------------------------------- Seq Scan on int8_tbl - Filter: (ANY (((q1)::text = (hashed SubPlan 1).col1) AND ((q1)::text = (hashed SubPlan 1).col1))) - SubPlan 1 + Filter: (ANY (((q1)::text = (hashed SubPlan any_1).col1) AND ((q1)::text = (hashed SubPlan any_1).col1))) + SubPlan any_1 -> Seq Scan on inner_text (4 rows) @@ -1226,11 +1226,11 @@ create or replace function bogus_int8_text_eq(int8, text) returns boolean language sql as 'select $2 = $1::text'; explain (costs off) select * from int8_tbl where q1 in (select c1 from inner_text); - QUERY PLAN -------------------------------------------------- + QUERY PLAN +----------------------------------------------------- Seq Scan on int8_tbl - Filter: (ANY ((SubPlan 1).col1 = (q1)::text)) - SubPlan 1 + Filter: (ANY ((SubPlan any_1).col1 = (q1)::text)) + SubPlan any_1 -> Materialize -> Seq Scan on inner_text (5 rows) @@ -1249,12 +1249,12 @@ rollback; -- to get rid of the bogus operator explain (costs off) select count(*) from tenk1 t where (exists(select 1 from tenk1 k where k.unique1 = t.unique2) or ten < 0); - QUERY PLAN --------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------------- Aggregate -> Seq Scan on tenk1 t - Filter: ((ANY (unique2 = (hashed SubPlan 2).col1)) OR (ten < 0)) - SubPlan 2 + Filter: ((ANY (unique2 = (hashed SubPlan exists_2).col1)) OR (ten < 0)) + SubPlan exists_2 -> Index Only Scan using tenk1_unique1 on tenk1 k (5 rows) @@ -1274,10 +1274,10 @@ where (exists(select 1 from tenk1 k where k.unique1 = t.unique2) or ten < 0) Aggregate -> Bitmap Heap Scan on tenk1 t Recheck Cond: (thousand = 1) - Filter: (EXISTS(SubPlan 1) OR (ten < 0)) + Filter: (EXISTS(SubPlan exists_1) OR (ten < 0)) -> Bitmap Index Scan on tenk1_thous_tenthous Index Cond: (thousand = 1) - SubPlan 1 + SubPlan exists_1 -> Index Only Scan using tenk1_unique1 on tenk1 k Index Cond: (unique1 = t.unique2) (9 rows) @@ -1299,20 +1299,20 @@ analyze exists_tbl; explain (costs off) select * from exists_tbl t1 where (exists(select 1 from exists_tbl t2 where t1.c1 = t2.c2) or c3 < 0); - QUERY PLAN --------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------- Append -> Seq Scan on exists_tbl_null t1_1 - Filter: (EXISTS(SubPlan 1) OR (c3 < 0)) - SubPlan 1 + Filter: (EXISTS(SubPlan exists_1) OR (c3 < 0)) + SubPlan exists_1 -> Append -> Seq Scan on exists_tbl_null t2_1 Filter: (t1_1.c1 = c2) -> Seq Scan on exists_tbl_def t2_2 Filter: (t1_1.c1 = c2) -> Seq Scan on exists_tbl_def t1_2 - Filter: ((ANY (c1 = (hashed SubPlan 2).col1)) OR (c3 < 0)) - SubPlan 2 + Filter: ((ANY (c1 = (hashed SubPlan exists_2).col1)) OR (c3 < 0)) + SubPlan exists_2 -> Append -> Seq Scan on exists_tbl_null t2_4 -> Seq Scan on exists_tbl_def t2_5 @@ -1348,14 +1348,14 @@ where a.thousand = b.thousand explain (verbose, costs off) select x, x from (select (select now()) as x from (values(1),(2)) v(y)) ss; - QUERY PLAN ------------------------------------------------- + QUERY PLAN +---------------------------------------------------------- Values Scan on "*VALUES*" - Output: (InitPlan 1).col1, (InitPlan 2).col1 - InitPlan 1 + Output: (InitPlan expr_1).col1, (InitPlan expr_2).col1 + InitPlan expr_1 -> Result Output: now() - InitPlan 2 + InitPlan expr_2 -> Result Output: now() (8 rows) @@ -1363,13 +1363,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: (InitPlan 1).col1 - InitPlan 1 + Output: (InitPlan expr_1).col1 + InitPlan expr_1 -> Result Output: random() (7 rows) @@ -1380,12 +1380,12 @@ explain (verbose, costs off) QUERY PLAN ---------------------------------------------------------------------- Values Scan on "*VALUES*" - Output: (SubPlan 1), (SubPlan 2) - SubPlan 1 + Output: (SubPlan expr_1), (SubPlan expr_2) + SubPlan expr_1 -> Result Output: now() One-Time Filter: ("*VALUES*".column1 = "*VALUES*".column1) - SubPlan 2 + SubPlan expr_2 -> Result Output: now() One-Time Filter: ("*VALUES*".column1 = "*VALUES*".column1) @@ -1399,8 +1399,8 @@ explain (verbose, costs off) Subquery Scan on ss Output: ss.x, ss.x -> Values Scan on "*VALUES*" - Output: (SubPlan 1) - SubPlan 1 + Output: (SubPlan expr_1) + SubPlan expr_1 -> Result Output: random() One-Time Filter: ("*VALUES*".column1 = "*VALUES*".column1) @@ -1420,16 +1420,16 @@ where o.ten = 0; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate - Output: sum((((ANY (i.ten = (hashed SubPlan 1).col1))))::integer) + Output: sum((((ANY (i.ten = (hashed SubPlan any_1).col1))))::integer) -> Nested Loop - Output: ((ANY (i.ten = (hashed SubPlan 1).col1))) + Output: ((ANY (i.ten = (hashed SubPlan any_1).col1))) -> Seq Scan on public.onek o Output: o.unique1, o.unique2, o.two, o.four, o.ten, o.twenty, o.hundred, o.thousand, o.twothousand, o.fivethous, o.tenthous, o.odd, o.even, o.stringu1, o.stringu2, o.string4 Filter: (o.ten = 0) -> Index Scan using onek_unique1 on public.onek i - Output: (ANY (i.ten = (hashed SubPlan 1).col1)), random() + Output: (ANY (i.ten = (hashed SubPlan any_1).col1)), random() Index Cond: (i.unique1 = o.unique1) - SubPlan 1 + SubPlan any_1 -> Seq Scan on public.int4_tbl Output: int4_tbl.f1 Filter: (int4_tbl.f1 <= o.hundred) @@ -1638,7 +1638,7 @@ select * from ---------------------------------------- Values Scan on "*VALUES*" Output: "*VALUES*".column1 - SubPlan 1 + SubPlan any_1 -> Values Scan on "*VALUES*_1" Output: "*VALUES*_1".column1 (5 rows) @@ -1665,12 +1665,12 @@ select * from int4_tbl where --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Nested Loop Semi Join Output: int4_tbl.f1 - Join Filter: (CASE WHEN (ANY (int4_tbl.f1 = (hashed SubPlan 1).col1)) THEN int4_tbl.f1 ELSE NULL::integer END = b.ten) + Join Filter: (CASE WHEN (ANY (int4_tbl.f1 = (hashed SubPlan any_1).col1)) THEN int4_tbl.f1 ELSE NULL::integer END = b.ten) -> Seq Scan on public.int4_tbl Output: int4_tbl.f1 -> Seq Scan on public.tenk1 b Output: b.unique1, b.unique2, b.two, b.four, b.ten, b.twenty, b.hundred, b.thousand, b.twothousand, b.fivethous, b.tenthous, b.odd, b.even, b.stringu1, b.stringu2, b.string4 - SubPlan 1 + SubPlan any_1 -> Index Only Scan using tenk1_unique1 on public.tenk1 a Output: a.unique1 (10 rows) @@ -2798,14 +2798,14 @@ select * from tenk1 A where exists (select 1 from tenk2 B where A.hundred in (select C.hundred FROM tenk2 C WHERE c.odd = b.odd)); - QUERY PLAN ------------------------------------------------------ + QUERY PLAN +--------------------------------------------------------- Nested Loop Semi Join - Join Filter: (ANY (a.hundred = (SubPlan 1).col1)) + Join Filter: (ANY (a.hundred = (SubPlan any_1).col1)) -> Seq Scan on tenk1 a -> Materialize -> Seq Scan on tenk2 b - SubPlan 1 + SubPlan any_1 -> Seq Scan on tenk2 c Filter: (odd = b.odd) (8 rows) @@ -2815,14 +2815,14 @@ WHERE c.odd = b.odd)); explain (costs off) SELECT * FROM tenk1 A LEFT JOIN tenk2 B ON A.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = b.odd); - QUERY PLAN ------------------------------------------------------ + QUERY PLAN +--------------------------------------------------------- Nested Loop Left Join - Join Filter: (ANY (a.hundred = (SubPlan 1).col1)) + Join Filter: (ANY (a.hundred = (SubPlan any_1).col1)) -> Seq Scan on tenk1 a -> Materialize -> Seq Scan on tenk2 b - SubPlan 1 + SubPlan any_1 -> Seq Scan on tenk2 c Filter: (odd = b.odd) (8 rows) @@ -2832,14 +2832,14 @@ ON A.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = b.odd); explain (costs off) SELECT * FROM tenk1 A LEFT JOIN tenk2 B ON B.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = a.odd); - QUERY PLAN ------------------------------------------------------ + QUERY PLAN +--------------------------------------------------------- Nested Loop Left Join - Join Filter: (ANY (b.hundred = (SubPlan 1).col1)) + Join Filter: (ANY (b.hundred = (SubPlan any_1).col1)) -> Seq Scan on tenk1 a -> Materialize -> Seq Scan on tenk2 b - SubPlan 1 + SubPlan any_1 -> Seq Scan on tenk2 c Filter: (odd = a.odd) (8 rows) @@ -2901,7 +2901,7 @@ ON B.hundred in (SELECT min(c.hundred) FROM tenk2 C WHERE c.odd = b.odd); Filter: (b.hundred = unnamed_subquery.min) -> Result Replaces: MinMaxAggregate - InitPlan 1 + InitPlan minmax_1 -> Limit -> Index Scan using tenk2_hundred on tenk2 c Index Cond: (hundred IS NOT NULL) @@ -3142,7 +3142,7 @@ WHERE unique1 IN (VALUES (0), ((2 IN (SELECT unique2 FROM onek c -> Seq Scan on onek t -> Values Scan on "*VALUES*" Filter: (t.unique1 = column1) - SubPlan 1 + SubPlan any_1 -> Index Only Scan using onek_unique2 on onek c Index Cond: (unique2 = t.unique1) (7 rows) @@ -3158,7 +3158,7 @@ WHERE unique1 IN (VALUES (0), ((2 IN (SELECT unique2 FROM onek c -> Sort Sort Key: "*VALUES*".column1 -> Values Scan on "*VALUES*" - SubPlan 1 + SubPlan any_1 -> Index Only Scan using onek_unique2 on onek c Filter: ((unique2)::double precision = ANY ('{0.479425538604203,2}'::double precision[])) -> Index Scan using onek_unique1 on onek t @@ -3177,7 +3177,7 @@ SELECT ten FROM onek t WHERE unique1 IN (VALUES (0), ((2 IN -> Sort Sort Key: "*VALUES*".column1 -> Values Scan on "*VALUES*" - SubPlan 1 + SubPlan any_1 -> Result -> Index Scan using onek_unique1 on onek t Index Cond: (unique1 = "*VALUES*".column1) diff --git a/src/test/regress/expected/union.out b/src/test/regress/expected/union.out index 7c089e0d598..fb77d108337 100644 --- a/src/test/regress/expected/union.out +++ b/src/test/regress/expected/union.out @@ -1217,7 +1217,7 @@ select event_id drop table events_child, events, other_events; reset enable_indexonlyscan; -- --- Test handling of UNION with provably empty inputs +-- Test handling of UNION / EXCEPT / INTERSECT with provably empty inputs -- -- Ensure the empty UNION input is pruned and de-duplication is done for the -- remaining relation. @@ -1258,14 +1258,102 @@ SELECT two FROM tenk1 WHERE 1=2 UNION SELECT four FROM tenk1 WHERE 1=2 UNION -SELECT ten FROM tenk1 WHERE 1=2; +SELECT ten FROM tenk1 WHERE 1=2 +ORDER BY 1; + QUERY PLAN +-------------------------------------- + Sort + Output: unnamed_subquery.two + Sort Key: unnamed_subquery.two + -> Result + Output: unnamed_subquery.two + Replaces: Aggregate + One-Time Filter: false +(7 rows) + +-- Ensure the planner provides a const-false Result node +EXPLAIN (COSTS OFF, VERBOSE) +SELECT two FROM tenk1 WHERE 1=2 +INTERSECT +SELECT four FROM tenk1 +ORDER BY 1; + QUERY PLAN +--------------------------------------------------------------------- + Sort + Output: unnamed_subquery.two + Sort Key: unnamed_subquery.two + -> Result + Output: unnamed_subquery.two + Replaces: Aggregate on unnamed_subquery, unnamed_subquery_1 + One-Time Filter: false +(7 rows) + +-- As above, with the inputs swapped +EXPLAIN (COSTS OFF, VERBOSE) +SELECT four FROM tenk1 +INTERSECT +SELECT two FROM tenk1 WHERE 1=2 +ORDER BY 1; + QUERY PLAN +--------------------------------------------------------------------- + Sort + Output: unnamed_subquery.four + Sort Key: unnamed_subquery.four + -> Result + Output: unnamed_subquery.four + Replaces: Aggregate on unnamed_subquery, unnamed_subquery_1 + One-Time Filter: false +(7 rows) + +-- Try with both inputs dummy +EXPLAIN (COSTS OFF, VERBOSE) +SELECT four FROM tenk1 WHERE 1=2 +INTERSECT +SELECT two FROM tenk1 WHERE 1=2 +ORDER BY 1; + QUERY PLAN +--------------------------------------------------------------------- + Sort + Output: unnamed_subquery.four + Sort Key: unnamed_subquery.four + -> Result + Output: unnamed_subquery.four + Replaces: Aggregate on unnamed_subquery, unnamed_subquery_1 + One-Time Filter: false +(7 rows) + +-- Ensure the planner provides a const-false Result node when the left input +-- is empty +EXPLAIN (COSTS OFF, VERBOSE) +SELECT two FROM tenk1 WHERE 1=2 +EXCEPT +SELECT four FROM tenk1 +ORDER BY 1; + QUERY PLAN +--------------------------------------------------------------------- + Sort + Output: unnamed_subquery.two + Sort Key: unnamed_subquery.two + -> Result + Output: unnamed_subquery.two + Replaces: Aggregate on unnamed_subquery, unnamed_subquery_1 + One-Time Filter: false +(7 rows) + +-- Ensure the planner only scans the left input when right input is empty +EXPLAIN (COSTS OFF, VERBOSE) +SELECT two FROM tenk1 +EXCEPT ALL +SELECT four FROM tenk1 WHERE 1=2 +ORDER BY 1; QUERY PLAN -------------------------------- - Result - Output: unnamed_subquery.two - Replaces: Aggregate - One-Time Filter: false -(4 rows) + Sort + Output: tenk1.two + Sort Key: tenk1.two + -> Seq Scan on public.tenk1 + Output: tenk1.two +(5 rows) -- Test constraint exclusion of UNION ALL subqueries explain (costs off) diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out index 095df0a670c..03df7e75b7b 100644 --- a/src/test/regress/expected/updatable_views.out +++ b/src/test/regress/expected/updatable_views.out @@ -2750,7 +2750,7 @@ EXPLAIN (costs off) INSERT INTO rw_view1 VALUES (5); --------------------------------------------------------- Insert on base_tbl b -> Result - SubPlan 1 + SubPlan exists_1 -> Index Only Scan using ref_tbl_pkey on ref_tbl r Index Cond: (a = b.a) (5 rows) @@ -2764,7 +2764,7 @@ EXPLAIN (costs off) UPDATE rw_view1 SET a = a + 5; -> Seq Scan on base_tbl b -> Hash -> Seq Scan on ref_tbl r - SubPlan 1 + SubPlan exists_1 -> Index Only Scan using ref_tbl_pkey on ref_tbl r_1 Index Cond: (a = b.a) (9 rows) @@ -3167,21 +3167,21 @@ EXPLAIN (costs off) DELETE FROM rw_view1 WHERE id = 1 AND snoop(data); DELETE FROM rw_view1 WHERE id = 1 AND snoop(data); NOTICE: snooped value: Row 1 EXPLAIN (costs off) INSERT INTO rw_view1 VALUES (2, 'New row 2'); - QUERY PLAN ------------------------------------------------------------ + QUERY PLAN +----------------------------------------------------------------- Insert on base_tbl - InitPlan 1 + InitPlan exists_1 -> Index Only Scan using base_tbl_pkey on base_tbl t Index Cond: (id = 2) -> Result - One-Time Filter: ((InitPlan 1).col1 IS NOT TRUE) + One-Time Filter: ((InitPlan exists_1).col1 IS NOT TRUE) Update on base_tbl - InitPlan 1 + InitPlan exists_1 -> Index Only Scan using base_tbl_pkey on base_tbl t Index Cond: (id = 2) -> Result - One-Time Filter: (InitPlan 1).col1 + One-Time Filter: (InitPlan exists_1).col1 -> Index Scan using base_tbl_pkey on base_tbl Index Cond: (id = 2) (15 rows) @@ -3240,8 +3240,8 @@ SELECT * FROM v1 WHERE a=8; EXPLAIN (VERBOSE, COSTS OFF) UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a < 7 AND a != 6; - QUERY PLAN ------------------------------------------------------------------------------------------------------------ + QUERY PLAN +------------------------------------------------------------------------------------------------------------------ Update on public.t1 Update on public.t1 t1_1 Update on public.t11 t1_2 @@ -3253,8 +3253,8 @@ UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a < 7 AND a != 6; -> Index Scan using t1_a_idx on public.t1 t1_1 Output: t1_1.tableoid, t1_1.ctid Index Cond: ((t1_1.a > 5) AND (t1_1.a < 7)) - Filter: ((t1_1.a <> 6) AND EXISTS(SubPlan 1) AND snoop(t1_1.a) AND leakproof(t1_1.a)) - SubPlan 1 + Filter: ((t1_1.a <> 6) AND EXISTS(SubPlan exists_1) AND snoop(t1_1.a) AND leakproof(t1_1.a)) + SubPlan exists_1 -> Append -> Seq Scan on public.t12 t12_1 Filter: (t12_1.a = t1_1.a) @@ -3263,15 +3263,15 @@ UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a < 7 AND a != 6; -> Index Scan using t11_a_idx on public.t11 t1_2 Output: t1_2.tableoid, t1_2.ctid Index Cond: ((t1_2.a > 5) AND (t1_2.a < 7)) - Filter: ((t1_2.a <> 6) AND EXISTS(SubPlan 1) AND snoop(t1_2.a) AND leakproof(t1_2.a)) + Filter: ((t1_2.a <> 6) AND EXISTS(SubPlan exists_1) AND snoop(t1_2.a) AND leakproof(t1_2.a)) -> Index Scan using t12_a_idx on public.t12 t1_3 Output: t1_3.tableoid, t1_3.ctid Index Cond: ((t1_3.a > 5) AND (t1_3.a < 7)) - Filter: ((t1_3.a <> 6) AND EXISTS(SubPlan 1) AND snoop(t1_3.a) AND leakproof(t1_3.a)) + Filter: ((t1_3.a <> 6) AND EXISTS(SubPlan exists_1) AND snoop(t1_3.a) AND leakproof(t1_3.a)) -> Index Scan using t111_a_idx on public.t111 t1_4 Output: t1_4.tableoid, t1_4.ctid Index Cond: ((t1_4.a > 5) AND (t1_4.a < 7)) - Filter: ((t1_4.a <> 6) AND EXISTS(SubPlan 1) AND snoop(t1_4.a) AND leakproof(t1_4.a)) + Filter: ((t1_4.a <> 6) AND EXISTS(SubPlan exists_1) AND snoop(t1_4.a) AND leakproof(t1_4.a)) (30 rows) UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a < 7 AND a != 6; @@ -3287,8 +3287,8 @@ SELECT * FROM t1 WHERE a=100; -- Nothing should have been changed to 100 EXPLAIN (VERBOSE, COSTS OFF) UPDATE v1 SET a=a+1 WHERE snoop(a) AND leakproof(a) AND a = 8; - QUERY PLAN ------------------------------------------------------------------------------------------ + QUERY PLAN +------------------------------------------------------------------------------------------------ Update on public.t1 Update on public.t1 t1_1 Update on public.t11 t1_2 @@ -3300,8 +3300,8 @@ UPDATE v1 SET a=a+1 WHERE snoop(a) AND leakproof(a) AND a = 8; -> Index Scan using t1_a_idx on public.t1 t1_1 Output: t1_1.a, t1_1.tableoid, t1_1.ctid Index Cond: ((t1_1.a > 5) AND (t1_1.a = 8)) - Filter: (EXISTS(SubPlan 1) AND snoop(t1_1.a) AND leakproof(t1_1.a)) - SubPlan 1 + Filter: (EXISTS(SubPlan exists_1) AND snoop(t1_1.a) AND leakproof(t1_1.a)) + SubPlan exists_1 -> Append -> Seq Scan on public.t12 t12_1 Filter: (t12_1.a = t1_1.a) @@ -3310,15 +3310,15 @@ UPDATE v1 SET a=a+1 WHERE snoop(a) AND leakproof(a) AND a = 8; -> Index Scan using t11_a_idx on public.t11 t1_2 Output: t1_2.a, t1_2.tableoid, t1_2.ctid Index Cond: ((t1_2.a > 5) AND (t1_2.a = 8)) - Filter: (EXISTS(SubPlan 1) AND snoop(t1_2.a) AND leakproof(t1_2.a)) + Filter: (EXISTS(SubPlan exists_1) AND snoop(t1_2.a) AND leakproof(t1_2.a)) -> Index Scan using t12_a_idx on public.t12 t1_3 Output: t1_3.a, t1_3.tableoid, t1_3.ctid Index Cond: ((t1_3.a > 5) AND (t1_3.a = 8)) - Filter: (EXISTS(SubPlan 1) AND snoop(t1_3.a) AND leakproof(t1_3.a)) + Filter: (EXISTS(SubPlan exists_1) AND snoop(t1_3.a) AND leakproof(t1_3.a)) -> Index Scan using t111_a_idx on public.t111 t1_4 Output: t1_4.a, t1_4.tableoid, t1_4.ctid Index Cond: ((t1_4.a > 5) AND (t1_4.a = 8)) - Filter: (EXISTS(SubPlan 1) AND snoop(t1_4.a) AND leakproof(t1_4.a)) + Filter: (EXISTS(SubPlan exists_1) AND snoop(t1_4.a) AND leakproof(t1_4.a)) (30 rows) UPDATE v1 SET a=a+1 WHERE snoop(a) AND leakproof(a) AND a = 8; @@ -3502,10 +3502,10 @@ CREATE RULE v1_upd_rule AS ON UPDATE TO v1 DO INSTEAD CREATE VIEW v2 WITH (security_barrier = true) AS SELECT * FROM v1 WHERE EXISTS (SELECT 1); EXPLAIN (COSTS OFF) UPDATE v2 SET a = 1; - QUERY PLAN --------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------- Update on t1 - InitPlan 1 + InitPlan exists_1 -> Result -> Merge Join Merge Cond: (t1.a = v1.a) @@ -3516,7 +3516,7 @@ EXPLAIN (COSTS OFF) UPDATE v2 SET a = 1; Sort Key: v1.a -> Subquery Scan on v1 -> Result - One-Time Filter: (InitPlan 1).col1 + One-Time Filter: (InitPlan exists_1).col1 -> Seq Scan on t1 t1_1 (14 rows) diff --git a/src/test/regress/expected/update.out b/src/test/regress/expected/update.out index 1b27d132d7b..eef2bac1cbf 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) = (SELECT b, a FROM update_test s WHERE s.a = t.a) WHERE CURRENT_USER = SESSION_USER; - QUERY PLAN --------------------------------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------------------------------------------------------- Update on public.update_test t -> Result - Output: (SubPlan 1).col1, (SubPlan 1).col2, (rescan SubPlan 1), t.ctid + Output: (SubPlan multiexpr_1).col1, (SubPlan multiexpr_1).col2, (rescan SubPlan multiexpr_1), t.ctid One-Time Filter: (CURRENT_USER = SESSION_USER) -> Seq Scan on public.update_test t Output: t.a, t.ctid - SubPlan 1 + SubPlan multiexpr_1 -> Seq Scan on public.update_test s Output: s.b, s.a Filter: (s.a = t.a) diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out index a595fa28ce1..a2867f477f0 100644 --- a/src/test/regress/expected/window.out +++ b/src/test/regress/expected/window.out @@ -4250,14 +4250,14 @@ SELECT 1 FROM (SELECT ntile(s1.x) OVER () AS c FROM (SELECT (SELECT 1) AS x) AS s1) s WHERE s.c = 1; - QUERY PLAN ----------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------- Subquery Scan on s Filter: (s.c = 1) -> WindowAgg Window: w1 AS (ROWS UNBOUNDED PRECEDING) - Run Condition: (ntile((InitPlan 1).col1) OVER w1 <= 1) - InitPlan 1 + Run Condition: (ntile((InitPlan expr_1).col1) OVER w1 <= 1) + InitPlan expr_1 -> Result -> Result (8 rows) @@ -4338,7 +4338,7 @@ WHERE c = 1; Filter: (emp.c = 1) -> WindowAgg Window: w1 AS (ORDER BY empsalary.empno) - InitPlan 1 + InitPlan expr_1 -> Result -> Sort Sort Key: empsalary.empno DESC diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.out index c3932c7b94c..86fdb85c6c5 100644 --- a/src/test/regress/expected/with.out +++ b/src/test/regress/expected/with.out @@ -2306,14 +2306,14 @@ explain (verbose, costs off) select f1, (with cte1(x,y) as (select 1,2) select count((select i4.f1 from cte1))) as ss from int4_tbl i4; - QUERY PLAN --------------------------------------------- + QUERY PLAN +------------------------------------------------- Seq Scan on public.int4_tbl i4 - Output: i4.f1, (SubPlan 2) - SubPlan 2 + Output: i4.f1, (SubPlan expr_1) + SubPlan expr_1 -> Aggregate - Output: count((InitPlan 1).col1) - InitPlan 1 + Output: count((InitPlan expr_2).col1) + InitPlan expr_2 -> Result Output: i4.f1 -> Result @@ -3203,7 +3203,7 @@ WHEN NOT MATCHED THEN INSERT VALUES(o.k, o.v); Output: o.k, o.v, o.* -> Result Output: 0, 'merge source SubPlan'::text - SubPlan 2 + SubPlan expr_1 -> Limit Output: ((cte_basic.b || ' merge update'::text)) -> CTE Scan on cte_basic @@ -3235,7 +3235,7 @@ WHEN NOT MATCHED THEN INSERT VALUES(o.k, o.v); CTE cte_init -> Result Output: 1, 'cte_init val'::text - InitPlan 2 + InitPlan expr_1 -> Limit Output: ((cte_init.b || ' merge update'::text)) -> CTE Scan on cte_init @@ -3278,11 +3278,11 @@ WHEN NOT MATCHED THEN INSERT VALUES(o.a, o.b || (SELECT merge_source_cte.*::text CTE merge_source_cte -> Result Output: 15, 'merge_source_cte val'::text - InitPlan 2 + InitPlan expr_1 -> 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 = 15) - InitPlan 3 + InitPlan expr_2 -> 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/union.sql b/src/test/regress/sql/union.sql index 56bd20e741c..782cca23701 100644 --- a/src/test/regress/sql/union.sql +++ b/src/test/regress/sql/union.sql @@ -460,7 +460,7 @@ drop table events_child, events, other_events; reset enable_indexonlyscan; -- --- Test handling of UNION with provably empty inputs +-- Test handling of UNION / EXCEPT / INTERSECT with provably empty inputs -- -- Ensure the empty UNION input is pruned and de-duplication is done for the @@ -484,7 +484,44 @@ SELECT two FROM tenk1 WHERE 1=2 UNION SELECT four FROM tenk1 WHERE 1=2 UNION -SELECT ten FROM tenk1 WHERE 1=2; +SELECT ten FROM tenk1 WHERE 1=2 +ORDER BY 1; + +-- Ensure the planner provides a const-false Result node +EXPLAIN (COSTS OFF, VERBOSE) +SELECT two FROM tenk1 WHERE 1=2 +INTERSECT +SELECT four FROM tenk1 +ORDER BY 1; + +-- As above, with the inputs swapped +EXPLAIN (COSTS OFF, VERBOSE) +SELECT four FROM tenk1 +INTERSECT +SELECT two FROM tenk1 WHERE 1=2 +ORDER BY 1; + +-- Try with both inputs dummy +EXPLAIN (COSTS OFF, VERBOSE) +SELECT four FROM tenk1 WHERE 1=2 +INTERSECT +SELECT two FROM tenk1 WHERE 1=2 +ORDER BY 1; + +-- Ensure the planner provides a const-false Result node when the left input +-- is empty +EXPLAIN (COSTS OFF, VERBOSE) +SELECT two FROM tenk1 WHERE 1=2 +EXCEPT +SELECT four FROM tenk1 +ORDER BY 1; + +-- Ensure the planner only scans the left input when right input is empty +EXPLAIN (COSTS OFF, VERBOSE) +SELECT two FROM tenk1 +EXCEPT ALL +SELECT four FROM tenk1 WHERE 1=2 +ORDER BY 1; -- Test constraint exclusion of UNION ALL subqueries explain (costs off) diff --git a/src/tutorial/funcs.c b/src/tutorial/funcs.c index 4a61177567c..3cc94534187 100644 --- a/src/tutorial/funcs.c +++ b/src/tutorial/funcs.c @@ -13,6 +13,7 @@ #include "executor/executor.h" /* for GetAttributeByName() */ #include "utils/fmgrprotos.h" /* for text_starts_with() */ #include "utils/geo_decls.h" /* for point type */ +#include "varatt.h" /* for VARDATA/VARSIZE macros */ PG_MODULE_MAGIC; |