From 9733f433fe23372b0b7522f384a88825cf59050e Mon Sep 17 00:00:00 2001 From: Robert Haas Date: Wed, 9 Apr 2025 10:14:31 -0400 Subject: [PATCH] Keep track of what RTIs a Result node is scanning. Result nodes now include an RTI set, which is only non-NULL when they have no subplan, and is taken from the relid set of the RelOptInfo that the Result is generating. Using that information, EXPLAIN now emits, where relevant, a "Replaces" line that says whether it replaced a scan, a join, or an aggregate; and in the former two cases, which relations were involved. Likewise, pg_overexplain's EXPLAIN (RANGE_TABLE) now displays the RTIs stored in a Result node just as it already does for other RTI-bearing node types. --- contrib/file_fdw/expected/file_fdw.out | 1 + .../expected/pg_overexplain.out | 6 +- contrib/pg_overexplain/pg_overexplain.c | 12 ++ .../postgres_fdw/expected/postgres_fdw.out | 9 +- src/backend/commands/explain.c | 95 ++++++++++++ src/backend/optimizer/plan/createplan.c | 93 +++++++++--- src/backend/optimizer/plan/setrefs.c | 2 + src/include/nodes/plannodes.h | 9 ++ src/test/regress/expected/aggregates.out | 51 ++++--- src/test/regress/expected/case.out | 21 +-- .../regress/expected/generated_virtual.out | 3 +- src/test/regress/expected/groupingsets.out | 3 +- src/test/regress/expected/inherit.out | 28 ++-- src/test/regress/expected/join.out | 110 ++++++++------ src/test/regress/expected/merge.out | 3 +- .../regress/expected/partition_aggregate.out | 21 +-- src/test/regress/expected/partition_join.out | 23 +-- src/test/regress/expected/partition_prune.out | 141 ++++++++++-------- src/test/regress/expected/predicate.out | 12 +- src/test/regress/expected/rowsecurity.out | 13 +- src/test/regress/expected/rowtypes.out | 6 +- src/test/regress/expected/select.out | 7 +- src/test/regress/expected/subselect.out | 6 +- src/test/regress/expected/tsrf.out | 6 +- 24 files changed, 477 insertions(+), 204 deletions(-) diff --git a/contrib/file_fdw/expected/file_fdw.out b/contrib/file_fdw/expected/file_fdw.out index df8d43b374..2b8bee8f50 100644 --- a/contrib/file_fdw/expected/file_fdw.out +++ b/contrib/file_fdw/expected/file_fdw.out @@ -319,6 +319,7 @@ SELECT explain_filter('EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_csv WHER Result Output: a, b One-Time Filter: false + Replaces: Scan on agg_csv \t off SELECT * FROM agg_csv WHERE a < 0; diff --git a/contrib/pg_overexplain/expected/pg_overexplain.out b/contrib/pg_overexplain/expected/pg_overexplain.out index 44120c388a..dd8adddb4a 100644 --- a/contrib/pg_overexplain/expected/pg_overexplain.out +++ b/contrib/pg_overexplain/expected/pg_overexplain.out @@ -44,9 +44,10 @@ EXPLAIN (RANGE_TABLE) SELECT 1; QUERY PLAN ------------------------------------------ Result (cost=0.00..0.01 rows=1 width=4) + RTIs: 1 RTI 1 (result): Eref: "*RESULT*" () -(3 rows) +(4 rows) -- Create a partitioned table. CREATE TABLE vegetables (id serial, name text, genus text) @@ -475,6 +476,7 @@ INSERT INTO vegetables (name, genus) VALUES ('broccoflower', 'brassica'); Nominal RTI: 1 Exclude Relation RTI: 0 -> Result + RTIs: 2 RTI 1 (relation): Eref: vegetables (id, name, genus) Relation: vegetables @@ -485,5 +487,5 @@ INSERT INTO vegetables (name, genus) VALUES ('broccoflower', 'brassica'); Eref: "*RESULT*" () Unprunable RTIs: 1 Result RTIs: 1 -(14 rows) +(15 rows) diff --git a/contrib/pg_overexplain/pg_overexplain.c b/contrib/pg_overexplain/pg_overexplain.c index de824566f8..bd70b6d9d5 100644 --- a/contrib/pg_overexplain/pg_overexplain.c +++ b/contrib/pg_overexplain/pg_overexplain.c @@ -236,6 +236,18 @@ overexplain_per_node_hook(PlanState *planstate, List *ancestors, ((MergeAppend *) plan)->apprelids, es); break; + case T_Result: + + /* + * 'relids' is only meaningful when plan->lefttree is NULL, + * but if somehow it ends up set when plan->lefttree is not + * NULL, print it anyway. + */ + if (plan->lefttree == NULL || + ((Result *) plan)->relids != NULL) + overexplain_bitmapset("RTIs", + ((Result *) plan)->relids, + es); default: break; } diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index 2185b42bb4..536b1baa10 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -7149,7 +7149,8 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT count(*) FROM ft1 WHERE c2 < 0; Output: count(*) -> Result One-Time Filter: false -(4 rows) + Replaces: Scan on ft1 +(5 rows) SELECT count(*) FROM ft1 WHERE c2 < 0; count @@ -7193,7 +7194,8 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT count(*) FROM ft1 WHERE c2 >= 0; Output: count(*) -> Result One-Time Filter: false -(4 rows) + Replaces: Scan on ft1 +(5 rows) SELECT count(*) FROM ft1 WHERE c2 >= 0; count @@ -8022,7 +8024,8 @@ DELETE FROM rem1 WHERE false; -- currently can't be pushed down -> Result Output: ctid One-Time Filter: false -(5 rows) + Replaces: Scan on rem1 +(6 rows) -- Test with statement-level triggers CREATE TRIGGER trig_stmt_before diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index 786ee865f1..bf0185e1ca 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -147,6 +147,7 @@ static void show_buffer_usage(ExplainState *es, const BufferUsage *usage); static void show_wal_usage(ExplainState *es, const WalUsage *usage); static void show_memory_counters(ExplainState *es, const MemoryContextCounters *mem_counters); +static void show_result_replacement_info(Result *result, ExplainState *es); static void ExplainIndexScanDetails(Oid indexid, ScanDirection indexorderdir, ExplainState *es); static void ExplainScanTarget(Scan *plan, ExplainState *es); @@ -2250,6 +2251,8 @@ ExplainNode(PlanState *planstate, List *ancestors, if (plan->qual) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); + if (plan->lefttree == NULL) + show_result_replacement_info(castNode(Result, plan), es); break; case T_ModifyTable: show_modifytable_info(castNode(ModifyTableState, planstate), ancestors, @@ -4743,6 +4746,98 @@ show_modifytable_info(ModifyTableState *mtstate, List *ancestors, ExplainCloseGroup("Target Tables", "Target Tables", false, es); } +/* + * Explain what a "Result" node replaced. + */ +static void +show_result_replacement_info(Result *result, ExplainState *es) +{ + StringInfoData buf; + int nrels = 0; + int rti = -1; + bool found_non_result = false; + char *s; + + /* + * If the Result node has a subplan, it didn't replace a scan, join, or + * aggregate; + */ + Assert(result->plan.lefttree == NULL); + + /* + * If the relids set is empty, it didn't replace a scan or a join, so it + * must have come from an upper rel. Currently, an aggregate seems to be + * the only possibility. (We might want to add more details to the Result + * node in the future to disambiguate; or to show the rels being + * aggregated in the case of partitionwise aggregate.) + */ + if (bms_is_empty(result->relids)) + { + ExplainPropertyText("Replaces", "Aggregate", es); + return; + } + + /* + * Build up a comma-separated list of user-facing names for the range + * table entries in the relids set. + */ + initStringInfo(&buf); + while ((rti = bms_next_member(result->relids, rti)) >= 0) + { + RangeTblEntry *rte = rt_fetch(rti, es->rtable); + char *refname; + + /* + * add_outer_joins_to_relids will add join RTIs to the relids set of a + * join; if that join is then replaced with a Result node, we may see + * such RTIs here. But we want to completely ignore those here, + * because "a LEFT JOIN b ON whatever" is a join between a and b, not + * a join between a, b, and an unnamed join. + */ + if (rte->rtekind == RTE_JOIN) + continue; + + /* Count the number of rels that aren't ignored completely. */ + ++nrels; + + /* Work out what reference name to use and added it the string. */ + refname = (char *) list_nth(es->rtable_names, rti - 1); + if (refname == NULL) + refname = rte->eref->aliasname; + if (buf.len > 0) + appendStringInfoString(&buf, ", "); + appendStringInfoString(&buf, refname); + + /* Keep track of whether we see anything other than RTE_RESULT. */ + if (rte->rtekind != RTE_RESULT) + found_non_result = true; + } + + /* + * If this Result node is because of a single RTE that is RTE_RESULT, it + * is not really replacing anything at all, because there's no other + * method for implementing a scan of such an RTE, so we don't display the + * Replaces line in such cases. + * + * (Arguably, we should instead display the RTE name in some other way in + * such cases, but in typical cases the RTE name is *RESULT* and printing + * "Result on *RESULT*" or similar doesn't seem especially useful, so for + * now we don't print anything at all.) + */ + if (nrels <= 1 && !found_non_result) + return; + + /* + * We're replacing either a scan or a join, according to the number of + * rels in the relids set. + */ + if (nrels > 1) + s = psprintf("Join on %s", buf.data); + else + s = psprintf("Scan on %s", buf.data); + ExplainPropertyText("Replaces", s, es); +} + /* * Explain the constituent plans of an Append, MergeAppend, * BitmapAnd, or BitmapOr node. diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c index 4ad30b7627..ce0e7ee6b9 100644 --- a/src/backend/optimizer/plan/createplan.c +++ b/src/backend/optimizer/plan/createplan.c @@ -101,7 +101,8 @@ static Gather *create_gather_plan(PlannerInfo *root, GatherPath *best_path); static Plan *create_projection_plan(PlannerInfo *root, ProjectionPath *best_path, int flags); -static Plan *inject_projection_plan(Plan *subplan, List *tlist, bool parallel_safe); +static Plan *inject_projection_plan(Plan *subplan, List *tlist, + bool parallel_safe); static Sort *create_sort_plan(PlannerInfo *root, SortPath *best_path, int flags); static IncrementalSort *create_incrementalsort_plan(PlannerInfo *root, IncrementalSortPath *best_path, int flags); @@ -302,7 +303,10 @@ static SetOp *make_setop(SetOpCmd cmd, SetOpStrategy strategy, List *tlist, Plan *lefttree, Plan *righttree, List *groupList, long numGroups); static LockRows *make_lockrows(Plan *lefttree, List *rowMarks, int epqParam); -static Result *make_result(List *tlist, Node *resconstantqual, Plan *subplan); +static Result *make_gating_result(List *tlist, Node *resconstantqual, + Plan *subplan); +static Result *make_simple_result(List *tlist, Node *resconstantqual, + Relids relids); static ProjectSet *make_project_set(List *tlist, Plan *subplan); static ModifyTable *make_modifytable(PlannerInfo *root, Plan *subplan, CmdType operation, bool canSetTag, @@ -1024,6 +1028,7 @@ create_gating_plan(PlannerInfo *root, Path *path, Plan *plan, { Plan *gplan; Plan *splan; + Relids relids = NULL; Assert(gating_quals); @@ -1031,7 +1036,9 @@ create_gating_plan(PlannerInfo *root, Path *path, Plan *plan, * We might have a trivial Result plan already. Stacking one Result atop * another is silly, so if that applies, just discard the input plan. * (We're assuming its targetlist is uninteresting; it should be either - * the same as the result of build_path_tlist, or a simplified version.) + * the same as the result of build_path_tlist, or a simplified version. + * However, we preserve the set of relids that it purports to scan and + * attribute that to our replacement Result instead.) */ splan = plan; if (IsA(plan, Result)) @@ -1040,7 +1047,10 @@ create_gating_plan(PlannerInfo *root, Path *path, Plan *plan, if (rplan->plan.lefttree == NULL && rplan->resconstantqual == NULL) + { splan = NULL; + relids = rplan->relids; + } } /* @@ -1048,9 +1058,12 @@ create_gating_plan(PlannerInfo *root, Path *path, Plan *plan, * tlist; that's never a wrong choice, even if the parent node didn't ask * for CP_EXACT_TLIST. */ - gplan = (Plan *) make_result(build_path_tlist(root, path), - (Node *) gating_quals, - splan); + if (splan == NULL) + gplan = (Plan *) make_simple_result(build_path_tlist(root, path), + (Node *) gating_quals, relids); + else + gplan = (Plan *) make_gating_result(build_path_tlist(root, path), + (Node *) gating_quals, splan); /* * Notice that we don't change cost or size estimates when doing gating. @@ -1245,10 +1258,10 @@ create_append_plan(PlannerInfo *root, AppendPath *best_path, int flags) /* Generate a Result plan with constant-FALSE gating qual */ Plan *plan; - plan = (Plan *) make_result(tlist, - (Node *) list_make1(makeBoolConst(false, - false)), - NULL); + plan = (Plan *) make_simple_result(tlist, + (Node *) list_make1(makeBoolConst(false, + false)), + best_path->path.parent->relids); copy_generic_path_info(plan, (Path *) best_path); @@ -1596,7 +1609,8 @@ create_group_result_plan(PlannerInfo *root, GroupResultPath *best_path) /* best_path->quals is just bare clauses */ quals = order_qual_clauses(root, best_path->quals); - plan = make_result(tlist, (Node *) quals, NULL); + plan = make_simple_result(tlist, (Node *) quals, + best_path->path.parent->relids); copy_generic_path_info(&plan->plan, (Path *) best_path); @@ -2093,8 +2107,7 @@ create_projection_plan(PlannerInfo *root, ProjectionPath *best_path, int flags) } else { - /* We need a Result node */ - plan = (Plan *) make_result(tlist, NULL, subplan); + plan = (Plan *) make_gating_result(tlist, NULL, subplan); copy_generic_path_info(plan, (Path *) best_path); } @@ -2118,7 +2131,7 @@ inject_projection_plan(Plan *subplan, List *tlist, bool parallel_safe) { Plan *plan; - plan = (Plan *) make_result(tlist, NULL, subplan); + plan = (Plan *) make_gating_result(tlist, NULL, subplan); /* * In principle, we should charge tlist eval cost plus cpu_per_tuple per @@ -2588,7 +2601,8 @@ create_minmaxagg_plan(PlannerInfo *root, MinMaxAggPath *best_path) /* Generate the output plan --- basically just a Result */ tlist = build_path_tlist(root, &best_path->path); - plan = make_result(tlist, (Node *) best_path->quals, NULL); + plan = make_simple_result(tlist, (Node *) best_path->quals, + best_path->path.parent->relids); copy_generic_path_info(&plan->plan, (Path *) best_path); @@ -4039,7 +4053,8 @@ create_resultscan_plan(PlannerInfo *root, Path *best_path, replace_nestloop_params(root, (Node *) scan_clauses); } - scan_plan = make_result(tlist, (Node *) scan_clauses, NULL); + scan_plan = make_simple_result(tlist, (Node *) scan_clauses, + best_path->parent->relids); copy_generic_path_info(&scan_plan->plan, best_path); @@ -7056,22 +7071,58 @@ make_limit(Plan *lefttree, Node *limitOffset, Node *limitCount, } /* - * make_result - * Build a Result plan node + * make_gating_result + * Build a Result plan node that performs projection of a subplan, and/or + * applies a one time filter (resconstantqual) */ static Result * -make_result(List *tlist, - Node *resconstantqual, - Plan *subplan) +make_gating_result(List *tlist, + Node *resconstantqual, + Plan *subplan) { Result *node = makeNode(Result); Plan *plan = &node->plan; + Assert(subplan != NULL); + plan->targetlist = tlist; plan->qual = NIL; plan->lefttree = subplan; plan->righttree = NULL; node->resconstantqual = resconstantqual; + node->relids = NULL; + + return node; +} + +/* + * make_simple_result + * Build a Result plan node that returns a single row (or possibly no rows, + * if the one-time filtered defined by resconstantqual returns false) + * + * 'relids' should be the relids set for this path's RelOptInfo. In essence, + * we're saying that this Result node generates all the tuples for that + * RelOptInfo. Note that the same consideration can never arise in + * make_gating_result(), because in that case the tuples are always coming + * from some subordinate node. + * + * NB: It would be nice to assert that the relids set is non-empty here, + * but it might be, because this could be a path for an upper rel. + */ +static Result * +make_simple_result(List *tlist, + Node *resconstantqual, + Relids relids) +{ + Result *node = makeNode(Result); + Plan *plan = &node->plan; + + plan->targetlist = tlist; + plan->qual = NIL; + plan->lefttree = NULL; + plan->righttree = NULL; + node->resconstantqual = resconstantqual; + node->relids = relids; return node; } diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c index 150e9f060e..7f241cddb4 100644 --- a/src/backend/optimizer/plan/setrefs.c +++ b/src/backend/optimizer/plan/setrefs.c @@ -1052,6 +1052,8 @@ set_plan_refs(PlannerInfo *root, Plan *plan, int rtoffset) /* resconstantqual can't contain any subplan variable refs */ splan->resconstantqual = fix_scan_expr(root, splan->resconstantqual, rtoffset, 1); + /* adjust the relids set */ + splan->relids = offset_relid_set(splan->relids, rtoffset); } break; case T_ProjectSet: diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h index 658d76225e..782fb471b6 100644 --- a/src/include/nodes/plannodes.h +++ b/src/include/nodes/plannodes.h @@ -250,12 +250,21 @@ typedef struct Plan * If resconstantqual isn't NULL, it represents a one-time qualification * test (i.e., one that doesn't depend on any variables from the outer plan, * so needs to be evaluated only once). + * + * relids identifies the relation for which this Result node is generating the + * tuples. When subplan is not NULL, it should be empty: this node is not + * generating anything in that case, just acting on tuples generated by the + * subplan. Otherwise, it may contain a single RTI (as when this Result node + * is substituted for a scan); multiple RTIs (as when this Result node is + * substituted for a join); or no RTIs at all (as when this Result node is + * substituted for an upper rel). * ---------------- */ typedef struct Result { Plan plan; Node *resconstantqual; + Bitmapset *relids; } Result; /* ---------------- diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out index 1f1ce2380a..712a42e667 100644 --- a/src/test/regress/expected/aggregates.out +++ b/src/test/regress/expected/aggregates.out @@ -940,11 +940,12 @@ explain (costs off) QUERY PLAN ------------------------------------------------------------ Result + Replaces: Aggregate InitPlan 1 -> Limit -> Index Only Scan using tenk1_unique1 on tenk1 Index Cond: (unique1 IS NOT NULL) -(5 rows) +(6 rows) select min(unique1) from tenk1; min @@ -957,11 +958,12 @@ explain (costs off) QUERY PLAN --------------------------------------------------------------------- Result + Replaces: Aggregate InitPlan 1 -> Limit -> Index Only Scan Backward using tenk1_unique1 on tenk1 Index Cond: (unique1 IS NOT NULL) -(5 rows) +(6 rows) select max(unique1) from tenk1; max @@ -974,11 +976,12 @@ explain (costs off) QUERY PLAN ------------------------------------------------------------------------ Result + Replaces: Aggregate InitPlan 1 -> Limit -> Index Only Scan Backward using tenk1_unique1 on tenk1 Index Cond: ((unique1 IS NOT NULL) AND (unique1 < 42)) -(5 rows) +(6 rows) select max(unique1) from tenk1 where unique1 < 42; max @@ -991,11 +994,12 @@ explain (costs off) QUERY PLAN ------------------------------------------------------------------------ Result + Replaces: Aggregate InitPlan 1 -> Limit -> Index Only Scan Backward using tenk1_unique1 on tenk1 Index Cond: ((unique1 IS NOT NULL) AND (unique1 > 42)) -(5 rows) +(6 rows) select max(unique1) from tenk1 where unique1 > 42; max @@ -1014,11 +1018,12 @@ explain (costs off) QUERY PLAN --------------------------------------------------------------------------- Result + Replaces: Aggregate InitPlan 1 -> Limit -> Index Only Scan Backward using tenk1_unique1 on tenk1 Index Cond: ((unique1 IS NOT NULL) AND (unique1 > 42000)) -(5 rows) +(6 rows) select max(unique1) from tenk1 where unique1 > 42000; max @@ -1033,11 +1038,12 @@ explain (costs off) QUERY PLAN ---------------------------------------------------------------------------- Result + Replaces: Aggregate InitPlan 1 -> Limit -> Index Only Scan Backward using tenk1_thous_tenthous on tenk1 Index Cond: ((thousand = 33) AND (tenthous IS NOT NULL)) -(5 rows) +(6 rows) select max(tenthous) from tenk1 where thousand = 33; max @@ -1050,11 +1056,12 @@ explain (costs off) QUERY PLAN -------------------------------------------------------------------------- Result + Replaces: Aggregate InitPlan 1 -> Limit -> Index Only Scan using tenk1_thous_tenthous on tenk1 Index Cond: ((thousand = 33) AND (tenthous IS NOT NULL)) -(5 rows) +(6 rows) select min(tenthous) from tenk1 where thousand = 33; min @@ -1071,11 +1078,12 @@ explain (costs off) Seq Scan on int4_tbl SubPlan 2 -> Result + Replaces: Aggregate InitPlan 1 -> Limit -> Index Only Scan using tenk1_unique1 on tenk1 Index Cond: ((unique1 IS NOT NULL) AND (unique1 > int4_tbl.f1)) -(7 rows) +(8 rows) select f1, (select min(unique1) from tenk1 where unique1 > f1) AS gt from int4_tbl; @@ -1100,7 +1108,8 @@ explain (costs off) -> Index Only Scan Backward using tenk1_unique2 on tenk1 Index Cond: (unique2 IS NOT NULL) -> Result -(7 rows) + Replaces: Aggregate +(8 rows) select distinct max(unique2) from tenk1; max @@ -1119,7 +1128,8 @@ explain (costs off) -> Index Only Scan Backward using tenk1_unique2 on tenk1 Index Cond: (unique2 IS NOT NULL) -> Result -(7 rows) + Replaces: Aggregate +(8 rows) select max(unique2) from tenk1 order by 1; max @@ -1138,7 +1148,8 @@ explain (costs off) -> Index Only Scan Backward using tenk1_unique2 on tenk1 Index Cond: (unique2 IS NOT NULL) -> Result -(7 rows) + Replaces: Aggregate +(8 rows) select max(unique2) from tenk1 order by max(unique2); max @@ -1157,7 +1168,8 @@ explain (costs off) -> Index Only Scan Backward using tenk1_unique2 on tenk1 Index Cond: (unique2 IS NOT NULL) -> Result -(7 rows) + Replaces: Aggregate +(8 rows) select max(unique2) from tenk1 order by max(unique2)+1; max @@ -1177,7 +1189,8 @@ explain (costs off) Index Cond: (unique2 IS NOT NULL) -> ProjectSet -> Result -(8 rows) + Replaces: Aggregate +(9 rows) select max(unique2), generate_series(1,3) as g from tenk1 order by g desc; max | g @@ -1193,12 +1206,13 @@ explain (costs off) QUERY PLAN ---------------------------------------------------- Result + Replaces: Aggregate InitPlan 1 -> Limit -> Result One-Time Filter: (100 IS NOT NULL) -> Seq Scan on tenk1 -(6 rows) +(7 rows) select max(100) from tenk1; max @@ -1224,6 +1238,7 @@ explain (costs off) QUERY PLAN --------------------------------------------------------------------------------------------- Result + Replaces: Aggregate InitPlan 1 -> Limit -> Merge Append @@ -1246,7 +1261,7 @@ explain (costs off) -> Index Only Scan using minmaxtest2i on minmaxtest2 minmaxtest_8 Index Cond: (f1 IS NOT NULL) -> Index Only Scan Backward using minmaxtest3i on minmaxtest3 minmaxtest_9 -(23 rows) +(24 rows) select min(f1), max(f1) from minmaxtest; min | max @@ -1285,7 +1300,8 @@ explain (costs off) -> Sort Sort Key: ((InitPlan 1).col1), ((InitPlan 2).col1) -> Result -(26 rows) + Replaces: Aggregate +(27 rows) select distinct min(f1), max(f1) from minmaxtest; min | max @@ -1315,7 +1331,8 @@ explain (costs off) -> Seq Scan on int4_tbl t1 Filter: ((f1 IS NOT NULL) AND (f1 = t0.f1)) -> Result -(9 rows) + Replaces: Aggregate +(10 rows) select f1, (select distinct min(t1.f1) from int4_tbl t1 where t1.f1 = t0.f1) from int4_tbl t0; diff --git a/src/test/regress/expected/case.out b/src/test/regress/expected/case.out index efee7fc431..d74b0dd68c 100644 --- a/src/test/regress/expected/case.out +++ b/src/test/regress/expected/case.out @@ -266,27 +266,30 @@ SELECT * -- Tests for constant subexpression simplification explain (costs off) SELECT * FROM CASE_TBL WHERE NULLIF(1, 2) = 2; - QUERY PLAN --------------------------- + QUERY PLAN +------------------------------ Result One-Time Filter: false -(2 rows) + Replaces: Scan on case_tbl +(3 rows) explain (costs off) SELECT * FROM CASE_TBL WHERE NULLIF(1, 1) IS NOT NULL; - QUERY PLAN --------------------------- + QUERY PLAN +------------------------------ Result One-Time Filter: false -(2 rows) + Replaces: Scan on case_tbl +(3 rows) explain (costs off) SELECT * FROM CASE_TBL WHERE NULLIF(1, null) = 2; - QUERY PLAN --------------------------- + QUERY PLAN +------------------------------ Result One-Time Filter: false -(2 rows) + Replaces: Scan on case_tbl +(3 rows) -- -- Examples of updates involving tables diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out index 6300e7c1d9..1793c3f357 100644 --- a/src/test/regress/expected/generated_virtual.out +++ b/src/test/regress/expected/generated_virtual.out @@ -1561,7 +1561,8 @@ select t2.* from gtest32 t1 left join gtest32 t2 on false; -> Result Output: a, 20, COALESCE(a, 100) One-Time Filter: false -(8 rows) + Replaces: Scan on t2 +(9 rows) select t2.* from gtest32 t1 left join gtest32 t2 on false; a | b | c | d diff --git a/src/test/regress/expected/groupingsets.out b/src/test/regress/expected/groupingsets.out index 35e4cb47eb..07a37da79d 100644 --- a/src/test/regress/expected/groupingsets.out +++ b/src/test/regress/expected/groupingsets.out @@ -591,11 +591,12 @@ explain (costs off) QUERY PLAN ------------------------------------------------------------ Result + Replaces: Aggregate InitPlan 1 -> Limit -> Index Only Scan using tenk1_unique1 on tenk1 Index Cond: (unique1 IS NOT NULL) -(5 rows) +(6 rows) -- Views with GROUPING SET queries CREATE VIEW gstest_view AS select a, b, grouping(a,b), sum(c), count(*), max(c) diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out index f9b0c415cf..820e914cab 100644 --- a/src/test/regress/expected/inherit.out +++ b/src/test/regress/expected/inherit.out @@ -581,7 +581,8 @@ update some_tab set a = a + 1 where false; -> Result Output: (some_tab.a + 1), NULL::oid, NULL::tid One-Time Filter: false -(4 rows) + Replaces: Scan on some_tab +(5 rows) update some_tab set a = a + 1 where false; explain (verbose, costs off) @@ -593,7 +594,8 @@ update some_tab set a = a + 1 where false returning b, a; -> Result Output: (some_tab.a + 1), NULL::oid, NULL::tid One-Time Filter: false -(5 rows) + Replaces: Scan on some_tab +(6 rows) update some_tab set a = a + 1 where false returning b, a; b | a @@ -700,7 +702,8 @@ explain update parted_tab set a = 2 where false; Update on parted_tab (cost=0.00..0.00 rows=0 width=0) -> Result (cost=0.00..0.00 rows=0 width=10) One-Time Filter: false -(3 rows) + Replaces: Scan on parted_tab +(4 rows) drop table parted_tab; -- Check UPDATE with multi-level partitioned inherited target @@ -1756,6 +1759,7 @@ explain (verbose, costs off) select min(1-id) from matest0; --------------------------------------------------------------------------------- Result Output: (InitPlan 1).col1 + Replaces: Aggregate InitPlan 1 -> Limit Output: ((1 - matest0.id)) @@ -1779,7 +1783,7 @@ explain (verbose, costs off) select min(1-id) from matest0; -> Index Scan using matest3i on public.matest3 matest0_4 Output: matest0_4.id, (1 - matest0_4.id) Index Cond: ((1 - matest0_4.id) IS NOT NULL) -(25 rows) +(26 rows) select min(1-id) from matest0; min @@ -1942,6 +1946,7 @@ SELECT min(x) FROM QUERY PLAN -------------------------------------------------------------------- Result + Replaces: Aggregate InitPlan 1 -> Limit -> Merge Append @@ -1950,7 +1955,7 @@ SELECT min(x) FROM Index Cond: (unique1 IS NOT NULL) -> Index Only Scan using tenk1_unique2 on tenk1 b Index Cond: (unique2 IS NOT NULL) -(9 rows) +(10 rows) explain (costs off) SELECT min(y) FROM @@ -1960,6 +1965,7 @@ SELECT min(y) FROM QUERY PLAN -------------------------------------------------------------------- Result + Replaces: Aggregate InitPlan 1 -> Limit -> Merge Append @@ -1968,7 +1974,7 @@ SELECT min(y) FROM Index Cond: (unique1 IS NOT NULL) -> Index Only Scan using tenk1_unique2 on tenk1 b Index Cond: (unique2 IS NOT NULL) -(9 rows) +(10 rows) -- XXX planner doesn't recognize that index on unique2 is sufficiently sorted explain (costs off) @@ -3085,11 +3091,12 @@ explain (costs off) select * from range_list_parted where a between 3 and 23 and /* Should select no rows because range partition key cannot be null */ explain (costs off) select * from range_list_parted where a is null; - QUERY PLAN --------------------------- + QUERY PLAN +--------------------------------------- Result One-Time Filter: false -(2 rows) + Replaces: Scan on range_list_parted +(3 rows) /* Should only select rows from the null-accepting partition */ explain (costs off) select * from range_list_parted where b is null; @@ -3250,6 +3257,7 @@ explain (costs off) select min(a), max(a) from parted_minmax where b = '12345'; QUERY PLAN ------------------------------------------------------------------------------------------------ Result + Replaces: Aggregate InitPlan 1 -> Limit -> Index Only Scan using parted_minmax1i on parted_minmax1 parted_minmax @@ -3258,7 +3266,7 @@ explain (costs off) select min(a), max(a) from parted_minmax where b = '12345'; -> Limit -> Index Only Scan Backward using parted_minmax1i on parted_minmax1 parted_minmax_1 Index Cond: ((a IS NOT NULL) AND (b = '12345'::text)) -(9 rows) +(10 rows) select min(a), max(a) from parted_minmax where b = '12345'; min | max diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index f35a0b18c3..f298aa7c1d 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -2264,11 +2264,12 @@ explain (costs off) select aa, bb, unique1, unique1 from tenk1 right join b_star on aa = unique1 where bb < bb and bb is null; - QUERY PLAN --------------------------- + QUERY PLAN +----------------------------------- Result One-Time Filter: false -(2 rows) + Replaces: Join on tenk1, b_star +(3 rows) select aa, bb, unique1, unique1 from tenk1 right join b_star on aa = unique1 @@ -2386,11 +2387,12 @@ order by t1.unique1; Index Cond: (unique1 < 10) SubPlan 2 -> Result + Replaces: Aggregate InitPlan 1 -> Limit -> Index Only Scan using tenk1_unique1 on tenk1 Index Cond: ((unique1 IS NOT NULL) AND (unique1 = t2.unique1)) -(19 rows) +(20 rows) -- Ensure we get the expected result select t1.unique1,t2.unique1 from tenk1 t1 @@ -2655,8 +2657,8 @@ select * from int8_tbl t1 left join (int8_tbl t2 left join int8_tbl t3 full join int8_tbl t4 on false on false) left join int8_tbl t5 on t2.q1 = t5.q1 on t2.q2 = 123; - QUERY PLAN --------------------------------------------------- + QUERY PLAN +---------------------------------------------------- Nested Loop Left Join -> Seq Scan on int8_tbl t1 -> Materialize @@ -2668,8 +2670,9 @@ on t2.q2 = 123; Filter: (q2 = 123) -> Result One-Time Filter: false + Replaces: Join on t3, t4 -> Seq Scan on int8_tbl t5 -(12 rows) +(13 rows) explain (costs off) select * from int8_tbl t1 @@ -4103,7 +4106,8 @@ from int4_tbl t1 Result Output: (current_database())::information_schema.sql_identifier, (c.relname)::information_schema.sql_identifier One-Time Filter: false -(3 rows) + Replaces: Join on t1, t2, a, c, nc, t, nt, bt, nbt +(4 rows) -- Test handling of qual pushdown to appendrel members with non-Var outputs explain (verbose, costs off) @@ -4175,11 +4179,12 @@ select unique1 from tenk1, lateral f_immutable_int4(1) x where x = unique1; explain (costs off) select unique1 from tenk1, lateral f_immutable_int4(1) x where x in (select 17); - QUERY PLAN --------------------------- + QUERY PLAN +--------------------------- Result One-Time Filter: false -(2 rows) + Replaces: Scan on tenk1 +(3 rows) explain (costs off) select unique1, x from tenk1 join f_immutable_int4(1) x on unique1 = x; @@ -4225,11 +4230,12 @@ select unique1, x from tenk1 full join f_immutable_int4(1) x on unique1 = x; -- check that pullup of a const function allows further const-folding explain (costs off) select unique1 from tenk1, f_immutable_int4(1) x where x = 42; - QUERY PLAN --------------------------- + QUERY PLAN +--------------------------- Result One-Time Filter: false -(2 rows) + Replaces: Scan on tenk1 +(3 rows) -- test inlining of immutable functions with PlaceHolderVars explain (costs off) @@ -5086,7 +5092,8 @@ left join -> Result -> Result One-Time Filter: false -(5 rows) + Replaces: Join on c, n +(6 rows) -- check handling of apparently-commutable outer joins with non-commutable -- joins between them @@ -5280,12 +5287,13 @@ select 1 from right join (select 1 as z) as ss2 on true) on false, lateral (select i4.f1, ss1.n from int8_tbl as i8 limit 1) as ss3; - QUERY PLAN --------------------------- + QUERY PLAN +----------------------------------------------- Result Output: 1 One-Time Filter: false -(3 rows) + Replaces: Join on i4, ss3, x1, x2, *RESULT* +(4 rows) select 1 from int4_tbl as i4 @@ -5314,11 +5322,12 @@ select 1 from t t1 on false where t3.a = coalesce(t5.a,1)) as s2 on true; - QUERY PLAN --------------------------- + QUERY PLAN +-------------------------------------------- Result One-Time Filter: false -(2 rows) + Replaces: Join on t1, s1, t2, t3, t4, t5 +(3 rows) rollback; -- @@ -5715,14 +5724,15 @@ from int4_tbl as t1 inner join int8_tbl as t7 on null) on t5.q1 = t7.q2) on false; - QUERY PLAN --------------------------------- + QUERY PLAN +-------------------------------------------------- Nested Loop Left Join Join Filter: false -> Seq Scan on int4_tbl t1 -> Result One-Time Filter: false -(5 rows) + Replaces: Join on t2, t3, t4, t5, t7, t6 +(6 rows) -- variant with Var rather than PHV coming from t6 explain (costs off) @@ -5737,14 +5747,15 @@ from int4_tbl as t1 inner join int8_tbl as t7 on null) on t5.q1 = t7.q2) on false; - QUERY PLAN --------------------------------- + QUERY PLAN +-------------------------------------------------- Nested Loop Left Join Join Filter: false -> Seq Scan on int4_tbl t1 -> Result One-Time Filter: false -(5 rows) + Replaces: Join on t2, t3, t4, t5, t7, t6 +(6 rows) -- per further discussion of bug #17781 explain (costs off) @@ -5794,15 +5805,16 @@ select * from int8_tbl t1 left join (int8_tbl t2 inner join int8_tbl t3 on false left join int8_tbl t4 on t2.q2 = t4.q2) on t1.q1 = t2.q1; - QUERY PLAN --------------------------------------- + QUERY PLAN +-------------------------------------------- Hash Left Join Hash Cond: (t1.q1 = q1) -> Seq Scan on int8_tbl t1 -> Hash -> Result One-Time Filter: false -(6 rows) + Replaces: Join on t2, t3, t4 +(7 rows) -- deduce constant-false from an EquivalenceClass explain (costs off) @@ -5810,15 +5822,16 @@ select * from int8_tbl t1 left join (int8_tbl t2 inner join int8_tbl t3 on (t2.q1-t3.q2) = 0 and (t2.q1-t3.q2) = 1 left join int8_tbl t4 on t2.q2 = t4.q2) on t1.q1 = t2.q1; - QUERY PLAN --------------------------------------- + QUERY PLAN +-------------------------------------------- Hash Left Join Hash Cond: (t1.q1 = q1) -> Seq Scan on int8_tbl t1 -> Hash -> Result One-Time Filter: false -(6 rows) + Replaces: Join on t2, t3, t4 +(7 rows) -- pseudoconstant based on an outer-level Param explain (costs off) @@ -6058,7 +6071,8 @@ select p.* from -------------------------- Result One-Time Filter: false -(2 rows) + Replaces: Scan on p +(3 rows) select p.* from (parent p left join child c on (p.k = c.k)) join parent x on p.k = x.k @@ -6075,7 +6089,8 @@ select p.* from -------------------------- Result One-Time Filter: false -(2 rows) + Replaces: Join on p, x +(3 rows) -- bug 5255: this is not optimizable by join removal begin; @@ -6148,7 +6163,8 @@ SELECT q2 FROM -> Result Output: q2, 'constant'::text One-Time Filter: false -(9 rows) + Replaces: Scan on int8_tbl +(10 rows) -- join removal bug #17786: check that OR conditions are cleaned up EXPLAIN (COSTS OFF) @@ -6168,7 +6184,8 @@ FROM int4_tbl -> Seq Scan on tenk1 -> Result One-Time Filter: false -(9 rows) + Replaces: Scan on int8_tbl +(10 rows) rollback; -- another join removal bug: we must clean up correctly when removing a PHV @@ -6497,7 +6514,8 @@ where q1.x = q2.y; -------------------------- Result One-Time Filter: false -(2 rows) + Replaces: Scan on sj_1 +(3 rows) -- We can't use a cross-EC generated self join qual because of current logic of -- the generate_join_implied_equalities routine. @@ -7321,11 +7339,12 @@ select 1 from emp1 full join on true where false) s on true where false; - QUERY PLAN --------------------------- + QUERY PLAN +---------------------------------- Result One-Time Filter: false -(2 rows) + Replaces: Join on emp1, t1, t3 +(3 rows) select 1 from emp1 full join (select * from emp1 t1 join @@ -7555,7 +7574,8 @@ where false; Result Output: 1 One-Time Filter: false -(3 rows) + Replaces: Scan on ss +(4 rows) -- -- Test LATERAL @@ -8494,7 +8514,8 @@ select * from int8_tbl i8 left join lateral -> Result Output: f1, i8.q2 One-Time Filter: false -(8 rows) + Replaces: Scan on int4_tbl +(9 rows) explain (verbose, costs off) select * from int8_tbl i8 left join lateral @@ -8508,7 +8529,8 @@ select * from int8_tbl i8 left join lateral -> Result Output: f1, f1, i8.q2 One-Time Filter: false -(7 rows) + Replaces: Join on i1, i2 +(8 rows) -- check handling of nested appendrels inside LATERAL select * from diff --git a/src/test/regress/expected/merge.out b/src/test/regress/expected/merge.out index bcd2966829..1025bdf86d 100644 --- a/src/test/regress/expected/merge.out +++ b/src/test/regress/expected/merge.out @@ -2427,7 +2427,8 @@ MERGE INTO pa_target t USING pa_source s ON t.tid = s.sid -> Result Output: t.tid, t.ctid One-Time Filter: false -(12 rows) + Replaces: Scan on t +(13 rows) MERGE INTO pa_target t USING pa_source s ON t.tid = s.sid WHEN NOT MATCHED THEN INSERT VALUES (s.sid); diff --git a/src/test/regress/expected/partition_aggregate.out b/src/test/regress/expected/partition_aggregate.out index 5f2c0cf578..80b002fbdc 100644 --- a/src/test/regress/expected/partition_aggregate.out +++ b/src/test/regress/expected/partition_aggregate.out @@ -146,13 +146,14 @@ SELECT c, a, count(*) FROM pagg_tab GROUP BY a, c; -- Test when input relation for grouping is dummy EXPLAIN (COSTS OFF) SELECT c, sum(a) FROM pagg_tab WHERE 1 = 2 GROUP BY c; - QUERY PLAN --------------------------------- + QUERY PLAN +------------------------------------ HashAggregate Group Key: c -> Result One-Time Filter: false -(4 rows) + Replaces: Scan on pagg_tab +(5 rows) SELECT c, sum(a) FROM pagg_tab WHERE 1 = 2 GROUP BY c; c | sum @@ -161,12 +162,13 @@ SELECT c, sum(a) FROM pagg_tab WHERE 1 = 2 GROUP BY c; EXPLAIN (COSTS OFF) SELECT c, sum(a) FROM pagg_tab WHERE c = 'x' GROUP BY c; - QUERY PLAN --------------------------------- + QUERY PLAN +------------------------------------ GroupAggregate -> Result One-Time Filter: false -(3 rows) + Replaces: Scan on pagg_tab +(4 rows) SELECT c, sum(a) FROM pagg_tab WHERE c = 'x' GROUP BY c; c | sum @@ -804,15 +806,16 @@ SELECT a.x, b.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x < 20) a FULL JOI -- Empty join relation because of empty outer side, no partitionwise agg plan EXPLAIN (COSTS OFF) SELECT a.x, a.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x = 1 AND x = 2) a LEFT JOIN pagg_tab2 b ON a.x = b.y GROUP BY a.x, a.y ORDER BY 1, 2; - QUERY PLAN --------------------------------------- + QUERY PLAN +---------------------------------------------- GroupAggregate Group Key: pagg_tab1.y -> Sort Sort Key: pagg_tab1.y -> Result One-Time Filter: false -(6 rows) + Replaces: Join on b, pagg_tab1 +(7 rows) SELECT a.x, a.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x = 1 AND x = 2) a LEFT JOIN pagg_tab2 b ON a.x = b.y GROUP BY a.x, a.y ORDER BY 1, 2; x | y | count diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out index d5368186ca..52c4ffffd8 100644 --- a/src/test/regress/expected/partition_join.out +++ b/src/test/regress/expected/partition_join.out @@ -1605,19 +1605,21 @@ SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM plt1 t1, pl -- joins where one of the relations is proven empty EXPLAIN (COSTS OFF) SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.a = 1 AND t1.a = 2; - QUERY PLAN --------------------------- + QUERY PLAN +---------------------------- Result One-Time Filter: false -(2 rows) + Replaces: Join on t1, t2 +(3 rows) EXPLAIN (COSTS OFF) SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a = 1 AND a = 2) t1 LEFT JOIN prt2 t2 ON t1.a = t2.b; - QUERY PLAN --------------------------- + QUERY PLAN +------------------------------ Result One-Time Filter: false -(2 rows) + Replaces: Join on t2, prt1 +(3 rows) EXPLAIN (COSTS OFF) SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a = 1 AND a = 2) t1 RIGHT JOIN prt2 t2 ON t1.a = t2.b, prt1 t3 WHERE t2.b = t3.a; @@ -1644,7 +1646,8 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a = 1 AND a = 2) t1 -> Hash -> Result One-Time Filter: false -(21 rows) + Replaces: Scan on prt1 +(22 rows) EXPLAIN (COSTS OFF) SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a = 1 AND a = 2) t1 FULL JOIN prt2 t2 ON t1.a = t2.b WHERE t2.a = 0 ORDER BY t1.a, t2.b; @@ -1664,7 +1667,8 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a = 1 AND a = 2) t1 -> Hash -> Result One-Time Filter: false -(14 rows) + Replaces: Scan on prt1 +(15 rows) -- -- tests for hash partitioned tables. @@ -2251,7 +2255,8 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1_l WHERE a = 1 AND a = 2) -> Hash -> Result One-Time Filter: false -(11 rows) + Replaces: Scan on prt1_l +(12 rows) -- Test case to verify proper handling of subqueries in a partitioned delete. -- The weird-looking lateral join is just there to force creation of a diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out index 0bf35260b4..6b51f39f88 100644 --- a/src/test/regress/expected/partition_prune.out +++ b/src/test/regress/expected/partition_prune.out @@ -628,7 +628,8 @@ explain (costs off) select * from rlp3 where a = 20; /* empty */ -------------------------- Result One-Time Filter: false -(2 rows) + Replaces: Scan on rlp3 +(3 rows) -- redundant clauses are eliminated explain (costs off) select * from rlp where a > 1 and a = 10; /* only default */ @@ -671,7 +672,8 @@ explain (costs off) select * from rlp where a = 1 and a = 3; /* empty */ -------------------------- Result One-Time Filter: false -(2 rows) + Replaces: Scan on rlp +(3 rows) explain (costs off) select * from rlp where (a = 1 and a = 3) or (a > 1 and a = 15); QUERY PLAN @@ -1254,25 +1256,28 @@ select * from boolpart where a is not unknown; -- check that all partitions are pruned when faced with conflicting clauses explain (costs off) select * from boolpart where a is not unknown and a is unknown; - QUERY PLAN --------------------------- + QUERY PLAN +------------------------------ Result One-Time Filter: false -(2 rows) + Replaces: Scan on boolpart +(3 rows) explain (costs off) select * from boolpart where a is false and a is unknown; - QUERY PLAN --------------------------- + QUERY PLAN +------------------------------ Result One-Time Filter: false -(2 rows) + Replaces: Scan on boolpart +(3 rows) explain (costs off) select * from boolpart where a is true and a is unknown; - QUERY PLAN --------------------------- + QUERY PLAN +------------------------------ Result One-Time Filter: false -(2 rows) + Replaces: Scan on boolpart +(3 rows) -- inverse boolean partitioning - a seemingly unlikely design, but we've got -- code for it, so we'd better test it. @@ -1568,11 +1573,12 @@ explain (costs off) select * from coercepart where a = any ('{ab,null}'); (2 rows) explain (costs off) select * from coercepart where a = any (null::text[]); - QUERY PLAN --------------------------- + QUERY PLAN +-------------------------------- Result One-Time Filter: false -(2 rows) + Replaces: Scan on coercepart +(3 rows) explain (costs off) select * from coercepart where a = all ('{ab}'); QUERY PLAN @@ -1582,25 +1588,28 @@ explain (costs off) select * from coercepart where a = all ('{ab}'); (2 rows) explain (costs off) select * from coercepart where a = all ('{ab,bc}'); - QUERY PLAN --------------------------- + QUERY PLAN +-------------------------------- Result One-Time Filter: false -(2 rows) + Replaces: Scan on coercepart +(3 rows) explain (costs off) select * from coercepart where a = all ('{ab,null}'); - QUERY PLAN --------------------------- + QUERY PLAN +-------------------------------- Result One-Time Filter: false -(2 rows) + Replaces: Scan on coercepart +(3 rows) explain (costs off) select * from coercepart where a = all (null::text[]); - QUERY PLAN --------------------------- + QUERY PLAN +-------------------------------- Result One-Time Filter: false -(2 rows) + Replaces: Scan on coercepart +(3 rows) drop table coercepart; CREATE TABLE part (a INT, b INT) PARTITION BY LIST (a); @@ -1773,7 +1782,8 @@ explain (costs off) select * from lp where a <> 'a' and a is null; -------------------------- Result One-Time Filter: false -(2 rows) + Replaces: Scan on lp +(3 rows) explain (costs off) select * from lp where (a <> 'a' and a <> 'd') or a is null; QUERY PLAN @@ -1866,22 +1876,24 @@ create table lparted_by_int2 (a smallint) partition by list (a); create table lparted_by_int2_1 partition of lparted_by_int2 for values in (1); create table lparted_by_int2_16384 partition of lparted_by_int2 for values in (16384); explain (costs off) select * from lparted_by_int2 where a = 100_000_000_000_000; - QUERY PLAN --------------------------- + QUERY PLAN +------------------------------------- Result One-Time Filter: false -(2 rows) + Replaces: Scan on lparted_by_int2 +(3 rows) create table rparted_by_int2 (a smallint) partition by range (a); create table rparted_by_int2_1 partition of rparted_by_int2 for values from (1) to (10); create table rparted_by_int2_16384 partition of rparted_by_int2 for values from (10) to (16384); -- all partitions pruned explain (costs off) select * from rparted_by_int2 where a > 100_000_000_000_000; - QUERY PLAN --------------------------- + QUERY PLAN +------------------------------------- Result One-Time Filter: false -(2 rows) + Replaces: Scan on rparted_by_int2 +(3 rows) create table rparted_by_int2_maxvalue partition of rparted_by_int2 for values from (16384) to (maxvalue); -- all partitions but rparted_by_int2_maxvalue pruned @@ -2132,7 +2144,8 @@ explain (costs off) select * from hp where a = 1 and b = 'abcde' and -------------------------- Result One-Time Filter: false -(2 rows) + Replaces: Scan on hp +(3 rows) -- -- Test runtime partition pruning @@ -3390,11 +3403,12 @@ select * from stable_qual_pruning where a < '2000-02-01'::timestamptz; explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a = any(array['2010-02-01', '2020-01-01']::timestamp[]); - QUERY PLAN ------------------------------------ + QUERY PLAN +----------------------------------------- Result (actual rows=0.00 loops=1) One-Time Filter: false -(2 rows) + Replaces: Scan on stable_qual_pruning +(3 rows) explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning @@ -3642,6 +3656,7 @@ explain (analyze, costs off, summary off, timing off, buffers off) select * from Sort Key: ma_test.b InitPlan 2 -> Result (actual rows=1.00 loops=1) + Replaces: Aggregate InitPlan 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) @@ -3656,7 +3671,7 @@ explain (analyze, costs off, summary off, timing off, buffers off) select * from -> 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) Index Searches: 1 -(18 rows) +(19 rows) reset enable_seqscan; reset enable_sort; @@ -3678,11 +3693,12 @@ explain (costs off) select * from pp_arrpart where a = '{1}'; (2 rows) explain (costs off) select * from pp_arrpart where a = '{1, 2}'; - QUERY PLAN --------------------------- + QUERY PLAN +-------------------------------- Result One-Time Filter: false -(2 rows) + Replaces: Scan on pp_arrpart +(3 rows) explain (costs off) select * from pp_arrpart where a in ('{4, 5}', '{1}'); QUERY PLAN @@ -3764,11 +3780,12 @@ explain (costs off) select * from pp_enumpart where a = 'blue'; (2 rows) explain (costs off) select * from pp_enumpart where a = 'black'; - QUERY PLAN --------------------------- + QUERY PLAN +--------------------------------- Result One-Time Filter: false -(2 rows) + Replaces: Scan on pp_enumpart +(3 rows) drop table pp_enumpart; drop type pp_colors; @@ -3785,11 +3802,12 @@ explain (costs off) select * from pp_recpart where a = '(1,1)'::pp_rectype; (2 rows) explain (costs off) select * from pp_recpart where a = '(1,2)'::pp_rectype; - QUERY PLAN --------------------------- + QUERY PLAN +-------------------------------- Result One-Time Filter: false -(2 rows) + Replaces: Scan on pp_recpart +(3 rows) drop table pp_recpart; drop type pp_rectype; @@ -3805,11 +3823,12 @@ explain (costs off) select * from pp_intrangepart where a = '[1,2]'::int4range; (2 rows) explain (costs off) select * from pp_intrangepart where a = '(1,2)'::int4range; - QUERY PLAN --------------------------- + QUERY PLAN +------------------------------------- Result One-Time Filter: false -(2 rows) + Replaces: Scan on pp_intrangepart +(3 rows) drop table pp_intrangepart; -- @@ -4125,19 +4144,21 @@ explain (costs off) update listp1 set a = 1 where a = 2; -- constraint exclusion enabled set constraint_exclusion to 'on'; explain (costs off) select * from listp1 where a = 2; - QUERY PLAN --------------------------- + QUERY PLAN +---------------------------- Result One-Time Filter: false -(2 rows) + Replaces: Scan on listp1 +(3 rows) explain (costs off) update listp1 set a = 1 where a = 2; - QUERY PLAN --------------------------------- + QUERY PLAN +---------------------------------- Update on listp1 -> Result One-Time Filter: false -(3 rows) + Replaces: Scan on listp1 +(4 rows) reset constraint_exclusion; reset enable_partition_pruning; @@ -4524,18 +4545,20 @@ create table hp_contradict_test (a int, b int) partition by hash (a part_test_in create table hp_contradict_test_p1 partition of hp_contradict_test for values with (modulus 2, remainder 0); create table hp_contradict_test_p2 partition of hp_contradict_test for values with (modulus 2, remainder 1); explain (costs off) select * from hp_contradict_test where a is null and a === 1 and b === 1; - QUERY PLAN --------------------------- + QUERY PLAN +---------------------------------------- Result One-Time Filter: false -(2 rows) + Replaces: Scan on hp_contradict_test +(3 rows) explain (costs off) select * from hp_contradict_test where a === 1 and b === 1 and a is null; - QUERY PLAN --------------------------- + QUERY PLAN +---------------------------------------- Result One-Time Filter: false -(2 rows) + Replaces: Scan on hp_contradict_test +(3 rows) drop table hp_contradict_test; drop operator class part_test_int4_ops2 using hash; diff --git a/src/test/regress/expected/predicate.out b/src/test/regress/expected/predicate.out index b79037748b..ee8afacbf0 100644 --- a/src/test/regress/expected/predicate.out +++ b/src/test/regress/expected/predicate.out @@ -37,7 +37,8 @@ SELECT * FROM pred_tab t WHERE t.a IS NULL; -------------------------- Result One-Time Filter: false -(2 rows) + Replaces: Scan on t +(3 rows) -- Ensure the IS_NULL qual is not reduced to constant-FALSE on nullable -- columns @@ -78,7 +79,8 @@ SELECT * FROM pred_tab t WHERE t.a IS NULL OR t.c IS NULL; -------------------------- Result One-Time Filter: false -(2 rows) + Replaces: Scan on t +(3 rows) -- Ensure the OR clause is not reduced to constant-FALSE when not all branches -- are provably false @@ -140,7 +142,8 @@ SELECT * FROM pred_tab t1 -> Seq Scan on pred_tab t1 -> Result One-Time Filter: false -(5 rows) + Replaces: Scan on t2 +(6 rows) -- Ensure the IS_NULL qual is not reduced to constant-FALSE when the column is -- nullable by an outer join @@ -210,7 +213,8 @@ SELECT * FROM pred_tab t1 -> Seq Scan on pred_tab t1 -> Result One-Time Filter: false -(5 rows) + Replaces: Scan on t2 +(6 rows) -- Ensure the OR clause is not reduced to constant-FALSE when a column is -- made nullable from an outer join diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out index 1c4e37d224..729ea4d760 100644 --- a/src/test/regress/expected/rowsecurity.out +++ b/src/test/regress/expected/rowsecurity.out @@ -3607,7 +3607,8 @@ EXPLAIN (COSTS OFF) SELECT * FROM t1; -------------------------- Result One-Time Filter: false -(2 rows) + Replaces: Scan on t1 +(3 rows) SET SESSION AUTHORIZATION regress_rls_bob; SELECT * FROM t1; @@ -3620,7 +3621,8 @@ EXPLAIN (COSTS OFF) SELECT * FROM t1; -------------------------- Result One-Time Filter: false -(2 rows) + Replaces: Scan on t1 +(3 rows) -- -- COPY TO/FROM @@ -4524,11 +4526,12 @@ SELECT * FROM rls_tbl WHERE a <<< 1000; (0 rows) EXPLAIN (COSTS OFF) SELECT * FROM rls_tbl WHERE a <<< 1000 or a <<< 900; - QUERY PLAN --------------------------- + QUERY PLAN +----------------------------- Result One-Time Filter: false -(2 rows) + Replaces: Scan on rls_tbl +(3 rows) DROP OPERATOR <<< (int, int); DROP FUNCTION op_leak(int, int); diff --git a/src/test/regress/expected/rowtypes.out b/src/test/regress/expected/rowtypes.out index 9168979a62..328b1e142c 100644 --- a/src/test/regress/expected/rowtypes.out +++ b/src/test/regress/expected/rowtypes.out @@ -1324,7 +1324,8 @@ where false; Result Output: (a).f1, (a).f2 One-Time Filter: false -(3 rows) + Replaces: Scan on ss +(4 rows) explain (verbose, costs off) with cte(c) as materialized (select row(1, 2)), @@ -1349,10 +1350,11 @@ where false; Result Output: (cte.c).f1 One-Time Filter: false + Replaces: Scan on cte CTE cte -> Result Output: '(1,2)'::record -(6 rows) +(7 rows) -- -- Tests for component access / FieldSelect diff --git a/src/test/regress/expected/select.out b/src/test/regress/expected/select.out index bab0cc93ff..d1736dac22 100644 --- a/src/test/regress/expected/select.out +++ b/src/test/regress/expected/select.out @@ -962,10 +962,11 @@ create table list_parted_tbl (a int,b int) partition by list (a); create table list_parted_tbl1 partition of list_parted_tbl for values in (1) partition by list(b); explain (costs off) select * from list_parted_tbl; - QUERY PLAN --------------------------- + QUERY PLAN +------------------------------------- Result One-Time Filter: false -(2 rows) + Replaces: Scan on list_parted_tbl +(3 rows) drop table list_parted_tbl; diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out index 40d8056fce..bd25ceabdf 100644 --- a/src/test/regress/expected/subselect.out +++ b/src/test/regress/expected/subselect.out @@ -2645,12 +2645,13 @@ ON B.hundred in (SELECT min(c.hundred) FROM tenk2 C WHERE c.odd = b.odd); -> Subquery Scan on "ANY_subquery" Filter: (b.hundred = "ANY_subquery".min) -> Result + Replaces: Aggregate InitPlan 1 -> Limit -> Index Scan using tenk2_hundred on tenk2 c Index Cond: (hundred IS NOT NULL) Filter: (odd = b.odd) -(16 rows) +(17 rows) -- -- Test VALUES to ARRAY (VtA) transformation @@ -2815,7 +2816,8 @@ EXPLAIN (COSTS OFF) EXECUTE test(NULL, 3.14, NULL); -------------------------- Result One-Time Filter: false -(2 rows) + Replaces: Scan on onek +(3 rows) EXPLAIN (COSTS OFF) EXECUTE test(NULL, 3.14, '-1.5'); QUERY PLAN diff --git a/src/test/regress/expected/tsrf.out b/src/test/regress/expected/tsrf.out index d47b5f6ec5..e73e68af97 100644 --- a/src/test/regress/expected/tsrf.out +++ b/src/test/regress/expected/tsrf.out @@ -92,7 +92,8 @@ SELECT unnest(ARRAY[1, 2]) FROM few WHERE false; Output: unnest('{1,2}'::integer[]) -> Result One-Time Filter: false -(4 rows) + Replaces: Scan on few +(5 rows) SELECT unnest(ARRAY[1, 2]) FROM few WHERE false; unnest @@ -108,7 +109,8 @@ SELECT * FROM few f1, Result Output: f1.id, f1.dataa, f1.datab, ss.unnest One-Time Filter: false -(3 rows) + Replaces: Join on f1, ss +(4 rows) SELECT * FROM few f1, (SELECT unnest(ARRAY[1,2]) FROM few f2 WHERE false OFFSET 0) ss; -- 2.39.5