diff options
| -rw-r--r-- | contrib/postgres_fdw/expected/postgres_fdw.out | 19 | ||||
| -rw-r--r-- | contrib/postgres_fdw/sql/postgres_fdw.sql | 4 | ||||
| -rw-r--r-- | src/backend/executor/execAmi.c | 30 | ||||
| -rw-r--r-- | src/backend/optimizer/path/allpaths.c | 36 | ||||
| -rw-r--r-- | src/backend/optimizer/plan/createplan.c | 8 | ||||
| -rw-r--r-- | src/backend/optimizer/plan/setrefs.c | 264 | ||||
| -rw-r--r-- | src/backend/optimizer/util/pathnode.c | 42 | ||||
| -rw-r--r-- | src/test/regress/expected/inherit.out | 73 | ||||
| -rw-r--r-- | src/test/regress/expected/partition_join.out | 61 | ||||
| -rw-r--r-- | src/test/regress/expected/partition_prune.out | 526 | ||||
| -rw-r--r-- | src/test/regress/expected/rowsecurity.out | 22 | ||||
| -rw-r--r-- | src/test/regress/expected/union.out | 9 |
12 files changed, 598 insertions, 496 deletions
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index 42108bd3d46..2be67bca02a 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -8427,17 +8427,16 @@ SELECT t1.a,t2.b,t3.c FROM fprt1 t1 INNER JOIN fprt2 t2 ON (t1.a = t2.b) INNER J 400 | 400 | 0008 (4 rows) --- left outer join + nullable clasue -EXPLAIN (COSTS OFF) +-- left outer join + nullable clause +EXPLAIN (VERBOSE, COSTS OFF) SELECT t1.a,t2.b,t2.c FROM fprt1 t1 LEFT JOIN (SELECT * FROM fprt2 WHERE a < 10) t2 ON (t1.a = t2.b and t1.b = t2.a) WHERE t1.a < 10 ORDER BY 1,2,3; - QUERY PLAN ------------------------------------------------------------------------------------ - Sort - Sort Key: t1.a, ftprt2_p1.b, ftprt2_p1.c - -> Append - -> Foreign Scan - Relations: (public.ftprt1_p1 t1) LEFT JOIN (public.ftprt2_p1 fprt2) -(5 rows) + QUERY PLAN +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + Foreign Scan + Output: t1.a, ftprt2_p1.b, ftprt2_p1.c + Relations: (public.ftprt1_p1 t1) LEFT JOIN (public.ftprt2_p1 fprt2) + Remote SQL: SELECT r6.a, r9.b, r9.c FROM (public.fprt1_p1 r6 LEFT JOIN public.fprt2_p1 r9 ON (((r6.a = r9.b)) AND ((r6.b = r9.a)) AND ((r9.a < 10)))) WHERE ((r6.a < 10)) ORDER BY r6.a ASC NULLS LAST, r9.b ASC NULLS LAST, r9.c ASC NULLS LAST +(4 rows) SELECT t1.a,t2.b,t2.c FROM fprt1 t1 LEFT JOIN (SELECT * FROM fprt2 WHERE a < 10) t2 ON (t1.a = t2.b and t1.b = t2.a) WHERE t1.a < 10 ORDER BY 1,2,3; a | b | c diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index eb9d1ad59d1..4728511abf3 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -2309,8 +2309,8 @@ EXPLAIN (COSTS OFF) SELECT t1.a,t2.b,t3.c FROM fprt1 t1 INNER JOIN fprt2 t2 ON (t1.a = t2.b) INNER JOIN fprt1 t3 ON (t2.b = t3.a) WHERE t1.a % 25 =0 ORDER BY 1,2,3; SELECT t1.a,t2.b,t3.c FROM fprt1 t1 INNER JOIN fprt2 t2 ON (t1.a = t2.b) INNER JOIN fprt1 t3 ON (t2.b = t3.a) WHERE t1.a % 25 =0 ORDER BY 1,2,3; --- left outer join + nullable clasue -EXPLAIN (COSTS OFF) +-- left outer join + nullable clause +EXPLAIN (VERBOSE, COSTS OFF) SELECT t1.a,t2.b,t2.c FROM fprt1 t1 LEFT JOIN (SELECT * FROM fprt2 WHERE a < 10) t2 ON (t1.a = t2.b and t1.b = t2.a) WHERE t1.a < 10 ORDER BY 1,2,3; SELECT t1.a,t2.b,t2.c FROM fprt1 t1 LEFT JOIN (SELECT * FROM fprt2 WHERE a < 10) t2 ON (t1.a = t2.b and t1.b = t2.a) WHERE t1.a < 10 ORDER BY 1,2,3; diff --git a/src/backend/executor/execAmi.c b/src/backend/executor/execAmi.c index 187f892421f..1f18e5d3a2f 100644 --- a/src/backend/executor/execAmi.c +++ b/src/backend/executor/execAmi.c @@ -447,6 +447,36 @@ ExecSupportsMarkRestore(Path *pathnode) return false; /* childless Result */ } + case T_Append: + { + AppendPath *appendPath = castNode(AppendPath, pathnode); + + /* + * If there's exactly one child, then there will be no Append + * in the final plan, so we can handle mark/restore if the + * child plan node can. + */ + if (list_length(appendPath->subpaths) == 1) + return ExecSupportsMarkRestore((Path *) linitial(appendPath->subpaths)); + /* Otherwise, Append can't handle it */ + return false; + } + + case T_MergeAppend: + { + MergeAppendPath *mapath = castNode(MergeAppendPath, pathnode); + + /* + * Like the Append case above, single-subpath MergeAppends + * won't be in the final plan, so just return the child's + * mark/restore ability. + */ + if (list_length(mapath->subpaths) == 1) + return ExecSupportsMarkRestore((Path *) linitial(mapath->subpaths)); + /* Otherwise, MergeAppend can't handle it */ + return false; + } + default: break; } diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c index b2c5c833f72..da0d7787214 100644 --- a/src/backend/optimizer/path/allpaths.c +++ b/src/backend/optimizer/path/allpaths.c @@ -1044,8 +1044,8 @@ set_append_rel_size(PlannerInfo *root, RelOptInfo *rel, /* * We have to copy the parent's targetlist and quals to the child, * with appropriate substitution of variables. If any constant false - * or NULL clauses turn up, we can disregard the child right away. - * If not, we can apply constraint exclusion with just the + * or NULL clauses turn up, we can disregard the child right away. If + * not, we can apply constraint exclusion with just the * baserestrictinfo quals. */ if (!apply_child_basequals(root, rel, childrel, childRTE, appinfo)) @@ -1708,6 +1708,38 @@ add_paths_to_append_rel(PlannerInfo *root, RelOptInfo *rel, required_outer, 0, false, partitioned_rels, -1)); } + + /* + * When there is only a single child relation, the Append path can inherit + * any ordering available for the child rel's path, so that it's useful to + * consider ordered partial paths. Above we only considered the cheapest + * partial path for each child, but let's also make paths using any + * partial paths that have pathkeys. + */ + if (list_length(live_childrels) == 1) + { + RelOptInfo *childrel = (RelOptInfo *) linitial(live_childrels); + + foreach(l, childrel->partial_pathlist) + { + Path *path = (Path *) lfirst(l); + AppendPath *appendpath; + + /* + * Skip paths with no pathkeys. Also skip the cheapest partial + * path, since we already used that above. + */ + if (path->pathkeys == NIL || + path == linitial(childrel->partial_pathlist)) + continue; + + appendpath = create_append_path(root, rel, NIL, list_make1(path), + NULL, path->parallel_workers, + true, + partitioned_rels, partial_rows); + add_partial_path(rel, (Path *) appendpath); + } + } } /* diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c index 93c56c657ce..979c3c212fd 100644 --- a/src/backend/optimizer/plan/createplan.c +++ b/src/backend/optimizer/plan/createplan.c @@ -1134,10 +1134,10 @@ create_append_plan(PlannerInfo *root, AppendPath *best_path) } /* - * XXX ideally, if there's just one child, we'd not bother to generate an - * Append node but just return the single child. At the moment this does - * not work because the varno of the child scan plan won't match the - * parent-rel Vars it'll be asked to emit. + * And build the Append plan. Note that if there's just one child, the + * Append is pretty useless; but we wait till setrefs.c to get rid of it. + * Doing so here doesn't work because the varno of the child scan plan + * won't match the parent-rel Vars it'll be asked to emit. */ plan = make_append(subplans, best_path->first_partial_path, diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c index 0213a376705..4204ca4025d 100644 --- a/src/backend/optimizer/plan/setrefs.c +++ b/src/backend/optimizer/plan/setrefs.c @@ -94,12 +94,19 @@ static Plan *set_subqueryscan_references(PlannerInfo *root, SubqueryScan *plan, int rtoffset); static bool trivial_subqueryscan(SubqueryScan *plan); +static Plan *clean_up_removed_plan_level(Plan *parent, Plan *child); static void set_foreignscan_references(PlannerInfo *root, ForeignScan *fscan, int rtoffset); static void set_customscan_references(PlannerInfo *root, CustomScan *cscan, int rtoffset); +static Plan *set_append_references(PlannerInfo *root, + Append *aplan, + int rtoffset); +static Plan *set_mergeappend_references(PlannerInfo *root, + MergeAppend *mplan, + int rtoffset); static Node *fix_scan_expr(PlannerInfo *root, Node *node, int rtoffset); static Node *fix_scan_expr_mutator(Node *node, fix_scan_expr_context *context); static bool fix_scan_expr_walker(Node *node, fix_scan_expr_context *context); @@ -181,19 +188,22 @@ static List *set_returning_clause_references(PlannerInfo *root, * 8. We assign every plan node in the tree a unique ID. * * We also perform one final optimization step, which is to delete - * SubqueryScan plan nodes that aren't doing anything useful (ie, have - * no qual and a no-op targetlist). The reason for doing this last is that + * SubqueryScan, Append, and MergeAppend plan nodes that aren't doing + * anything useful. The reason for doing this last is that * it can't readily be done before set_plan_references, because it would - * break set_upper_references: the Vars in the subquery's top tlist - * wouldn't match up with the Vars in the outer plan tree. The SubqueryScan + * break set_upper_references: the Vars in the child plan's top tlist + * wouldn't match up with the Vars in the outer plan tree. A SubqueryScan * serves a necessary function as a buffer between outer query and subquery * variable numbering ... but after we've flattened the rangetable this is * no longer a problem, since then there's only one rtindex namespace. + * Likewise, Append and MergeAppend buffer between the parent and child vars + * of an appendrel, but we don't need to worry about that once we've done + * set_plan_references. * * set_plan_references recursively traverses the whole plan tree. * * The return value is normally the same Plan node passed in, but can be - * different when the passed-in Plan is a SubqueryScan we decide isn't needed. + * different when the passed-in Plan is a node we decide isn't needed. * * The flattened rangetable entries are appended to root->glob->finalrtable. * Also, rowmarks entries are appended to root->glob->finalrowmarks, and the @@ -897,71 +907,15 @@ set_plan_refs(PlannerInfo *root, Plan *plan, int rtoffset) } break; case T_Append: - { - Append *splan = (Append *) plan; - - /* - * Append, like Sort et al, doesn't actually evaluate its - * targetlist or check quals. - */ - set_dummy_tlist_references(plan, rtoffset); - Assert(splan->plan.qual == NIL); - foreach(l, splan->appendplans) - { - lfirst(l) = set_plan_refs(root, - (Plan *) lfirst(l), - rtoffset); - } - if (splan->part_prune_info) - { - foreach(l, splan->part_prune_info->prune_infos) - { - List *prune_infos = lfirst(l); - ListCell *l2; - - foreach(l2, prune_infos) - { - PartitionedRelPruneInfo *pinfo = lfirst(l2); - - pinfo->rtindex += rtoffset; - } - } - } - } - break; + /* Needs special treatment, see comments below */ + return set_append_references(root, + (Append *) plan, + rtoffset); case T_MergeAppend: - { - MergeAppend *splan = (MergeAppend *) plan; - - /* - * MergeAppend, like Sort et al, doesn't actually evaluate its - * targetlist or check quals. - */ - set_dummy_tlist_references(plan, rtoffset); - Assert(splan->plan.qual == NIL); - foreach(l, splan->mergeplans) - { - lfirst(l) = set_plan_refs(root, - (Plan *) lfirst(l), + /* Needs special treatment, see comments below */ + return set_mergeappend_references(root, + (MergeAppend *) plan, rtoffset); - } - if (splan->part_prune_info) - { - foreach(l, splan->part_prune_info->prune_infos) - { - List *prune_infos = lfirst(l); - ListCell *l2; - - foreach(l2, prune_infos) - { - PartitionedRelPruneInfo *pinfo = lfirst(l2); - - pinfo->rtindex += rtoffset; - } - } - } - } - break; case T_RecursiveUnion: /* This doesn't evaluate targetlist or check quals either */ set_dummy_tlist_references(plan, rtoffset); @@ -1086,30 +1040,7 @@ set_subqueryscan_references(PlannerInfo *root, /* * We can omit the SubqueryScan node and just pull up the subplan. */ - ListCell *lp, - *lc; - - result = plan->subplan; - - /* We have to be sure we don't lose any initplans */ - result->initPlan = list_concat(plan->scan.plan.initPlan, - result->initPlan); - - /* - * We also have to transfer the SubqueryScan's result-column names - * into the subplan, else columns sent to client will be improperly - * labeled if this is the topmost plan level. Copy the "source - * column" information too. - */ - forboth(lp, plan->scan.plan.targetlist, lc, result->targetlist) - { - TargetEntry *ptle = (TargetEntry *) lfirst(lp); - TargetEntry *ctle = (TargetEntry *) lfirst(lc); - - ctle->resname = ptle->resname; - ctle->resorigtbl = ptle->resorigtbl; - ctle->resorigcol = ptle->resorigcol; - } + result = clean_up_removed_plan_level((Plan *) plan, plan->subplan); } else { @@ -1191,6 +1122,30 @@ trivial_subqueryscan(SubqueryScan *plan) } /* + * clean_up_removed_plan_level + * Do necessary cleanup when we strip out a SubqueryScan, Append, etc + * + * We are dropping the "parent" plan in favor of returning just its "child". + * A few small tweaks are needed. + */ +static Plan * +clean_up_removed_plan_level(Plan *parent, Plan *child) +{ + /* We have to be sure we don't lose any initplans */ + child->initPlan = list_concat(parent->initPlan, + child->initPlan); + + /* + * We also have to transfer the parent's column labeling info into the + * child, else columns sent to client will be improperly labeled if this + * is the topmost plan level. resjunk and so on may be important too. + */ + apply_tlist_labeling(child->targetlist, parent->targetlist); + + return child; +} + +/* * set_foreignscan_references * Do set_plan_references processing on a ForeignScan */ @@ -1341,6 +1296,131 @@ set_customscan_references(PlannerInfo *root, } /* + * set_append_references + * Do set_plan_references processing on an Append + * + * We try to strip out the Append entirely; if we can't, we have + * to do the normal processing on it. + */ +static Plan * +set_append_references(PlannerInfo *root, + Append *aplan, + int rtoffset) +{ + ListCell *l; + + /* + * Append, like Sort et al, doesn't actually evaluate its targetlist or + * check quals. If it's got exactly one child plan, then it's not doing + * anything useful at all, and we can strip it out. + */ + Assert(aplan->plan.qual == NIL); + + /* First, we gotta recurse on the children */ + foreach(l, aplan->appendplans) + { + lfirst(l) = set_plan_refs(root, (Plan *) lfirst(l), rtoffset); + } + + /* Now, if there's just one, forget the Append and return that child */ + if (list_length(aplan->appendplans) == 1) + return clean_up_removed_plan_level((Plan *) aplan, + (Plan *) linitial(aplan->appendplans)); + + /* + * Otherwise, clean up the Append as needed. It's okay to do this after + * recursing to the children, because set_dummy_tlist_references doesn't + * look at those. + */ + set_dummy_tlist_references((Plan *) aplan, rtoffset); + + if (aplan->part_prune_info) + { + foreach(l, aplan->part_prune_info->prune_infos) + { + List *prune_infos = lfirst(l); + ListCell *l2; + + foreach(l2, prune_infos) + { + PartitionedRelPruneInfo *pinfo = lfirst(l2); + + pinfo->rtindex += rtoffset; + } + } + } + + /* We don't need to recurse to lefttree or righttree ... */ + Assert(aplan->plan.lefttree == NULL); + Assert(aplan->plan.righttree == NULL); + + return (Plan *) aplan; +} + +/* + * set_mergeappend_references + * Do set_plan_references processing on a MergeAppend + * + * We try to strip out the MergeAppend entirely; if we can't, we have + * to do the normal processing on it. + */ +static Plan * +set_mergeappend_references(PlannerInfo *root, + MergeAppend *mplan, + int rtoffset) +{ + ListCell *l; + + /* + * MergeAppend, like Sort et al, doesn't actually evaluate its targetlist + * or check quals. If it's got exactly one child plan, then it's not + * doing anything useful at all, and we can strip it out. + */ + Assert(mplan->plan.qual == NIL); + + /* First, we gotta recurse on the children */ + foreach(l, mplan->mergeplans) + { + lfirst(l) = set_plan_refs(root, (Plan *) lfirst(l), rtoffset); + } + + /* Now, if there's just one, forget the MergeAppend and return that child */ + if (list_length(mplan->mergeplans) == 1) + return clean_up_removed_plan_level((Plan *) mplan, + (Plan *) linitial(mplan->mergeplans)); + + /* + * Otherwise, clean up the MergeAppend as needed. It's okay to do this + * after recursing to the children, because set_dummy_tlist_references + * doesn't look at those. + */ + set_dummy_tlist_references((Plan *) mplan, rtoffset); + + if (mplan->part_prune_info) + { + foreach(l, mplan->part_prune_info->prune_infos) + { + List *prune_infos = lfirst(l); + ListCell *l2; + + foreach(l2, prune_infos) + { + PartitionedRelPruneInfo *pinfo = lfirst(l2); + + pinfo->rtindex += rtoffset; + } + } + } + + /* We don't need to recurse to lefttree or righttree ... */ + Assert(mplan->plan.lefttree == NULL); + Assert(mplan->plan.righttree == NULL); + + return (Plan *) mplan; +} + + +/* * copyVar * Copy a Var node. * diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c index 169e51e7921..56de8fc370a 100644 --- a/src/backend/optimizer/util/pathnode.c +++ b/src/backend/optimizer/util/pathnode.c @@ -1242,7 +1242,6 @@ create_append_path(PlannerInfo *root, pathnode->path.parallel_aware = parallel_aware; pathnode->path.parallel_safe = rel->consider_parallel; pathnode->path.parallel_workers = parallel_workers; - pathnode->path.pathkeys = NIL; /* result is always considered unsorted */ pathnode->partitioned_rels = list_copy(partitioned_rels); /* @@ -1276,7 +1275,26 @@ create_append_path(PlannerInfo *root, Assert(!parallel_aware || pathnode->path.parallel_safe); - cost_append(pathnode); + /* + * If there's exactly one child path, the Append is a no-op and will be + * discarded later (in setrefs.c); therefore, we can inherit the child's + * size, cost, and pathkeys if any. Otherwise, it's unsorted, and we must + * do the normal costsize calculation. + */ + if (list_length(pathnode->subpaths) == 1) + { + Path *child = (Path *) linitial(pathnode->subpaths); + + pathnode->path.rows = child->rows; + pathnode->path.startup_cost = child->startup_cost; + pathnode->path.total_cost = child->total_cost; + pathnode->path.pathkeys = child->pathkeys; + } + else + { + pathnode->path.pathkeys = NIL; /* unsorted if more than 1 subpath */ + cost_append(pathnode); + } /* If the caller provided a row estimate, override the computed value. */ if (rows >= 0) @@ -1408,11 +1426,21 @@ create_merge_append_path(PlannerInfo *root, Assert(bms_equal(PATH_REQ_OUTER(subpath), required_outer)); } - /* Now we can compute total costs of the MergeAppend */ - cost_merge_append(&pathnode->path, root, - pathkeys, list_length(subpaths), - input_startup_cost, input_total_cost, - pathnode->path.rows); + /* + * Now we can compute total costs of the MergeAppend. If there's exactly + * one child path, the MergeAppend is a no-op and will be discarded later + * (in setrefs.c); otherwise we do the normal cost calculation. + */ + if (list_length(subpaths) == 1) + { + pathnode->path.startup_cost = input_startup_cost; + pathnode->path.total_cost = input_total_cost; + } + else + cost_merge_append(&pathnode->path, root, + pathkeys, list_length(subpaths), + input_startup_cost, input_total_cost, + pathnode->path.rows); return pathnode; } diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out index 565d947b6d9..7518148df07 100644 --- a/src/test/regress/expected/inherit.out +++ b/src/test/regress/expected/inherit.out @@ -1770,12 +1770,11 @@ explain (costs off) select * from list_parted; (4 rows) explain (costs off) select * from list_parted where a is null; - QUERY PLAN --------------------------------- - Append - -> Seq Scan on part_null_xy - Filter: (a IS NULL) -(3 rows) + QUERY PLAN +-------------------------- + Seq Scan on part_null_xy + Filter: (a IS NULL) +(2 rows) explain (costs off) select * from list_parted where a is not null; QUERY PLAN @@ -1800,20 +1799,18 @@ explain (costs off) select * from list_parted where a in ('ab', 'cd', 'ef'); (5 rows) explain (costs off) select * from list_parted where a = 'ab' or a in (null, 'cd'); - QUERY PLAN ---------------------------------------------------------------------------------------- - Append - -> Seq Scan on part_ab_cd - Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[]))) -(3 rows) + QUERY PLAN +--------------------------------------------------------------------------------- + Seq Scan on part_ab_cd + Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[]))) +(2 rows) explain (costs off) select * from list_parted where a = 'ab'; - QUERY PLAN ------------------------------------------- - Append - -> Seq Scan on part_ab_cd - Filter: ((a)::text = 'ab'::text) -(3 rows) + QUERY PLAN +------------------------------------ + Seq Scan on part_ab_cd + Filter: ((a)::text = 'ab'::text) +(2 rows) create table range_list_parted ( a int, @@ -1893,12 +1890,11 @@ explain (costs off) select * from range_list_parted where a is null; /* Should only select rows from the null-accepting partition */ explain (costs off) select * from range_list_parted where b is null; - QUERY PLAN ------------------------------------- - Append - -> Seq Scan on part_40_inf_null - Filter: (b IS NULL) -(3 rows) + QUERY PLAN +------------------------------ + Seq Scan on part_40_inf_null + Filter: (b IS NULL) +(2 rows) explain (costs off) select * from range_list_parted where a is not null and a < 67; QUERY PLAN @@ -2021,12 +2017,11 @@ explain (costs off) select * from mcrparted where a > -1; -- scans all partition (15 rows) explain (costs off) select * from mcrparted where a = 20 and abs(b) = 10 and c > 10; -- scans mcrparted4 - QUERY PLAN ------------------------------------------------------------ - Append - -> Seq Scan on mcrparted4 - Filter: ((c > 10) AND (a = 20) AND (abs(b) = 10)) -(3 rows) + QUERY PLAN +----------------------------------------------------- + Seq Scan on mcrparted4 + Filter: ((c > 10) AND (a = 20) AND (abs(b) = 10)) +(2 rows) explain (costs off) select * from mcrparted where a = 20 and c > 20; -- scans mcrparted3, mcrparte4, mcrparte5, mcrparted_def QUERY PLAN @@ -2050,22 +2045,18 @@ create table parted_minmax1 partition of parted_minmax for values from (1) to (1 create index parted_minmax1i on parted_minmax1 (a, b); insert into parted_minmax values (1,'12345'); explain (costs off) select min(a), max(a) from parted_minmax where b = '12345'; - QUERY PLAN -------------------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------- Result InitPlan 1 (returns $0) -> Limit - -> Merge Append - Sort Key: parted_minmax1.a - -> Index Only Scan using parted_minmax1i on parted_minmax1 - Index Cond: ((a IS NOT NULL) AND (b = '12345'::text)) + -> Index Only Scan using parted_minmax1i on parted_minmax1 + Index Cond: ((a IS NOT NULL) AND (b = '12345'::text)) InitPlan 2 (returns $1) -> Limit - -> Merge Append - Sort Key: parted_minmax1_1.a DESC - -> Index Only Scan Backward using parted_minmax1i on parted_minmax1 parted_minmax1_1 - Index Cond: ((a IS NOT NULL) AND (b = '12345'::text)) -(13 rows) + -> Index Only Scan Backward using parted_minmax1i on parted_minmax1 parted_minmax1_1 + Index Cond: ((a IS NOT NULL) AND (b = '12345'::text)) +(9 rows) select min(a), max(a) from parted_minmax where b = '12345'; min | max diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out index bbdc373782e..e19535d6c77 100644 --- a/src/test/regress/expected/partition_join.out +++ b/src/test/regress/expected/partition_join.out @@ -186,19 +186,18 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT 50 phv, * FROM prt1 WHERE prt1.b = 0) -- Join with pruned partitions from joining relations 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 < 450 AND t2.b > 250 AND t1.b = 0 ORDER BY t1.a, t2.b; - QUERY PLAN ------------------------------------------------------------ + QUERY PLAN +----------------------------------------------------- Sort Sort Key: t1.a - -> Append - -> Hash Join - Hash Cond: (t2.b = t1.a) - -> Seq Scan on prt2_p2 t2 - Filter: (b > 250) - -> Hash - -> Seq Scan on prt1_p2 t1 - Filter: ((a < 450) AND (b = 0)) -(10 rows) + -> Hash Join + Hash Cond: (t2.b = t1.a) + -> Seq Scan on prt2_p2 t2 + Filter: (b > 250) + -> Hash + -> Seq Scan on prt1_p2 t1 + Filter: ((a < 450) AND (b = 0)) +(9 rows) SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.a < 450 AND t2.b > 250 AND t1.b = 0 ORDER BY t1.a, t2.b; a | c | b | c @@ -1480,10 +1479,9 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_l t1, prt2_l t2 WHERE t1.a = t2.b AND t1 -> Seq Scan on prt2_l_p3_p1 t2_3 -> Seq Scan on prt2_l_p3_p2 t2_4 -> Hash - -> Append - -> Seq Scan on prt1_l_p3_p1 t1_3 - Filter: (b = 0) -(29 rows) + -> Seq Scan on prt1_l_p3_p1 t1_3 + Filter: (b = 0) +(28 rows) SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_l t1, prt2_l t2 WHERE t1.a = t2.b AND t1.b = 0 ORDER BY t1.a, t2.b; a | c | b | c @@ -1526,10 +1524,9 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_l t1 LEFT JOIN prt2_l t2 ON t1.a = t2.b -> Seq Scan on prt2_l_p3_p1 t2_3 -> Seq Scan on prt2_l_p3_p2 t2_4 -> Hash - -> Append - -> Seq Scan on prt1_l_p3_p1 t1_3 - Filter: (b = 0) -(30 rows) + -> Seq Scan on prt1_l_p3_p1 t1_3 + Filter: (b = 0) +(29 rows) SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_l t1 LEFT JOIN prt2_l t2 ON t1.a = t2.b AND t1.c = t2.c WHERE t1.b = 0 ORDER BY t1.a, t2.b; a | c | b | c @@ -1580,10 +1577,9 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_l t1 RIGHT JOIN prt2_l t2 ON t1.a = t2.b -> Seq Scan on prt1_l_p3_p1 t1_3 -> Seq Scan on prt1_l_p3_p2 t1_4 -> Hash - -> Append - -> Seq Scan on prt2_l_p3_p1 t2_3 - Filter: (a = 0) -(30 rows) + -> Seq Scan on prt2_l_p3_p1 t2_3 + Filter: (a = 0) +(29 rows) SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_l t1 RIGHT JOIN prt2_l t2 ON t1.a = t2.b AND t1.c = t2.c WHERE t2.a = 0 ORDER BY t1.a, t2.b; a | c | b | c @@ -1629,14 +1625,12 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1_l WHERE prt1_l.b = 0) t1 Filter: (a = 0) -> Hash Full Join Hash Cond: ((prt1_l_p3_p1.a = prt2_l_p3_p1.b) AND ((prt1_l_p3_p1.c)::text = (prt2_l_p3_p1.c)::text)) - -> Append - -> Seq Scan on prt1_l_p3_p1 - Filter: (b = 0) + -> Seq Scan on prt1_l_p3_p1 + Filter: (b = 0) -> Hash - -> Append - -> Seq Scan on prt2_l_p3_p1 - Filter: (a = 0) -(33 rows) + -> Seq Scan on prt2_l_p3_p1 + Filter: (a = 0) +(31 rows) SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1_l WHERE prt1_l.b = 0) t1 FULL JOIN (SELECT * FROM prt2_l WHERE prt2_l.a = 0) t2 ON (t1.a = t2.b AND t1.c = t2.c) ORDER BY t1.a, t2.b; a | c | b | c @@ -1697,9 +1691,8 @@ SELECT * FROM prt1_l t1 LEFT JOIN LATERAL -> Seq Scan on prt1_l_p2_p2 t2_2 Filter: ((t1_2.a = a) AND ((t1_2.c)::text = (c)::text)) -> Nested Loop Left Join - -> Append - -> Seq Scan on prt1_l_p3_p1 t1_3 - Filter: (b = 0) + -> Seq Scan on prt1_l_p3_p1 t1_3 + Filter: (b = 0) -> Hash Join Hash Cond: ((t3_3.b = t2_3.a) AND ((t3_3.c)::text = (t2_3.c)::text)) -> Append @@ -1711,7 +1704,7 @@ SELECT * FROM prt1_l t1 LEFT JOIN LATERAL Filter: ((t1_3.a = a) AND ((t1_3.c)::text = (c)::text)) -> Seq Scan on prt1_l_p3_p2 t2_4 Filter: ((t1_3.a = a) AND ((t1_3.c)::text = (c)::text)) -(45 rows) +(44 rows) SELECT * FROM prt1_l t1 LEFT JOIN LATERAL (SELECT t2.a AS t2a, t2.c AS t2c, t2.b AS t2b, t3.b AS t3b, least(t1.a,t2.a,t3.b) FROM prt1_l t2 JOIN prt2_l t3 ON (t2.a = t3.b AND t2.c = t3.c)) ss diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out index 42753f79184..50ca03b9e31 100644 --- a/src/test/regress/expected/partition_prune.out +++ b/src/test/regress/expected/partition_prune.out @@ -43,20 +43,18 @@ explain (costs off) select * from lp where a > 'a' and a <= 'd'; (7 rows) explain (costs off) select * from lp where a = 'a'; - QUERY PLAN ------------------------------------ - Append - -> Seq Scan on lp_ad - Filter: (a = 'a'::bpchar) -(3 rows) + QUERY PLAN +----------------------------- + Seq Scan on lp_ad + Filter: (a = 'a'::bpchar) +(2 rows) explain (costs off) select * from lp where 'a' = a; /* commuted */ - QUERY PLAN ------------------------------------ - Append - -> Seq Scan on lp_ad - Filter: ('a'::bpchar = a) -(3 rows) + QUERY PLAN +----------------------------- + Seq Scan on lp_ad + Filter: ('a'::bpchar = a) +(2 rows) explain (costs off) select * from lp where a is not null; QUERY PLAN @@ -75,12 +73,11 @@ explain (costs off) select * from lp where a is not null; (11 rows) explain (costs off) select * from lp where a is null; - QUERY PLAN ------------------------------ - Append - -> Seq Scan on lp_null - Filter: (a IS NULL) -(3 rows) + QUERY PLAN +----------------------- + Seq Scan on lp_null + Filter: (a IS NULL) +(2 rows) explain (costs off) select * from lp where a = 'a' or a = 'c'; QUERY PLAN @@ -150,12 +147,11 @@ create table coll_pruning_a partition of coll_pruning for values in ('a'); create table coll_pruning_b partition of coll_pruning for values in ('b'); create table coll_pruning_def partition of coll_pruning default; explain (costs off) select * from coll_pruning where a collate "C" = 'a' collate "C"; - QUERY PLAN ---------------------------------------------- - Append - -> Seq Scan on coll_pruning_a - Filter: (a = 'a'::text COLLATE "C") -(3 rows) + QUERY PLAN +--------------------------------------- + Seq Scan on coll_pruning_a + Filter: (a = 'a'::text COLLATE "C") +(2 rows) -- collation doesn't match the partitioning collation, no pruning occurs explain (costs off) select * from coll_pruning where a collate "POSIX" = 'a' collate "POSIX"; @@ -192,20 +188,18 @@ create table rlp5 partition of rlp for values from (31) to (maxvalue) partition create table rlp5_default partition of rlp5 default; create table rlp5_1 partition of rlp5 for values from (31) to (40); explain (costs off) select * from rlp where a < 1; - QUERY PLAN -------------------------- - Append - -> Seq Scan on rlp1 - Filter: (a < 1) -(3 rows) + QUERY PLAN +------------------- + Seq Scan on rlp1 + Filter: (a < 1) +(2 rows) explain (costs off) select * from rlp where 1 > a; /* commuted */ - QUERY PLAN -------------------------- - Append - -> Seq Scan on rlp1 - Filter: (1 > a) -(3 rows) + QUERY PLAN +------------------- + Seq Scan on rlp1 + Filter: (1 > a) +(2 rows) explain (costs off) select * from rlp where a <= 1; QUERY PLAN @@ -218,20 +212,18 @@ explain (costs off) select * from rlp where a <= 1; (5 rows) explain (costs off) select * from rlp where a = 1; - QUERY PLAN -------------------------- - Append - -> Seq Scan on rlp2 - Filter: (a = 1) -(3 rows) + QUERY PLAN +------------------- + Seq Scan on rlp2 + Filter: (a = 1) +(2 rows) explain (costs off) select * from rlp where a = 1::bigint; /* same as above */ - QUERY PLAN ------------------------------------ - Append - -> Seq Scan on rlp2 - Filter: (a = '1'::bigint) -(3 rows) + QUERY PLAN +----------------------------- + Seq Scan on rlp2 + Filter: (a = '1'::bigint) +(2 rows) explain (costs off) select * from rlp where a = 1::numeric; /* no pruning */ QUERY PLAN @@ -384,20 +376,18 @@ explain (costs off) select * from rlp where a = 16; (9 rows) explain (costs off) select * from rlp where a = 16 and b in ('not', 'in', 'here'); - QUERY PLAN ----------------------------------------------------------------------------- - Append - -> Seq Scan on rlp3_default - Filter: ((a = 16) AND ((b)::text = ANY ('{not,in,here}'::text[]))) -(3 rows) + QUERY PLAN +---------------------------------------------------------------------- + Seq Scan on rlp3_default + Filter: ((a = 16) AND ((b)::text = ANY ('{not,in,here}'::text[]))) +(2 rows) explain (costs off) select * from rlp where a = 16 and b < 'ab'; - QUERY PLAN ---------------------------------------------------------- - Append - -> Seq Scan on rlp3_default - Filter: (((b)::text < 'ab'::text) AND (a = 16)) -(3 rows) + QUERY PLAN +--------------------------------------------------- + Seq Scan on rlp3_default + Filter: (((b)::text < 'ab'::text) AND (a = 16)) +(2 rows) explain (costs off) select * from rlp where a = 16 and b <= 'ab'; QUERY PLAN @@ -410,12 +400,11 @@ explain (costs off) select * from rlp where a = 16 and b <= 'ab'; (5 rows) explain (costs off) select * from rlp where a = 16 and b is null; - QUERY PLAN --------------------------------------------- - Append - -> Seq Scan on rlp3nullxy - Filter: ((b IS NULL) AND (a = 16)) -(3 rows) + QUERY PLAN +-------------------------------------- + Seq Scan on rlp3nullxy + Filter: ((b IS NULL) AND (a = 16)) +(2 rows) explain (costs off) select * from rlp where a = 16 and b is not null; QUERY PLAN @@ -432,12 +421,11 @@ explain (costs off) select * from rlp where a = 16 and b is not null; (9 rows) explain (costs off) select * from rlp where a is null; - QUERY PLAN ------------------------------------- - Append - -> Seq Scan on rlp_default_null - Filter: (a IS NULL) -(3 rows) + QUERY PLAN +------------------------------ + Seq Scan on rlp_default_null + Filter: (a IS NULL) +(2 rows) explain (costs off) select * from rlp where a is not null; QUERY PLAN @@ -486,12 +474,11 @@ explain (costs off) select * from rlp where a > 30; (7 rows) explain (costs off) select * from rlp where a = 30; /* only default is scanned */ - QUERY PLAN ----------------------------------- - Append - -> Seq Scan on rlp_default_30 - Filter: (a = 30) -(3 rows) + QUERY PLAN +---------------------------- + Seq Scan on rlp_default_30 + Filter: (a = 30) +(2 rows) explain (costs off) select * from rlp where a <= 31; QUERY PLAN @@ -528,12 +515,11 @@ explain (costs off) select * from rlp where a <= 31; (29 rows) explain (costs off) select * from rlp where a = 1 or a = 7; - QUERY PLAN --------------------------------------- - Append - -> Seq Scan on rlp2 - Filter: ((a = 1) OR (a = 7)) -(3 rows) + QUERY PLAN +-------------------------------- + Seq Scan on rlp2 + Filter: ((a = 1) OR (a = 7)) +(2 rows) explain (costs off) select * from rlp where a = 1 or b = 'ab'; QUERY PLAN @@ -580,12 +566,11 @@ explain (costs off) select * from rlp where a > 20 and a < 27; (9 rows) explain (costs off) select * from rlp where a = 29; - QUERY PLAN --------------------------------- - Append - -> Seq Scan on rlp4_default - Filter: (a = 29) -(3 rows) + QUERY PLAN +-------------------------- + Seq Scan on rlp4_default + Filter: (a = 29) +(2 rows) explain (costs off) select * from rlp where a >= 29; QUERY PLAN @@ -605,12 +590,11 @@ explain (costs off) select * from rlp where a >= 29; -- redundant clauses are eliminated explain (costs off) select * from rlp where a > 1 and a = 10; /* only default */ - QUERY PLAN ----------------------------------------- - Append - -> Seq Scan on rlp_default_10 - Filter: ((a > 1) AND (a = 10)) -(3 rows) + QUERY PLAN +---------------------------------- + Seq Scan on rlp_default_10 + Filter: ((a > 1) AND (a = 10)) +(2 rows) explain (costs off) select * from rlp where a > 1 and a >=15; /* rlp3 onwards, including default */ QUERY PLAN @@ -797,20 +781,18 @@ explain (costs off) select * from mc3p where a <= 10 and abs(b) < 10; (9 rows) explain (costs off) select * from mc3p where a = 11 and abs(b) = 0; - QUERY PLAN ---------------------------------------------- - Append - -> Seq Scan on mc3p_default - Filter: ((a = 11) AND (abs(b) = 0)) -(3 rows) + QUERY PLAN +--------------------------------------- + Seq Scan on mc3p_default + Filter: ((a = 11) AND (abs(b) = 0)) +(2 rows) explain (costs off) select * from mc3p where a = 20 and abs(b) = 10 and c = 100; - QUERY PLAN ------------------------------------------------------------- - Append - -> Seq Scan on mc3p6 - Filter: ((a = 20) AND (c = 100) AND (abs(b) = 10)) -(3 rows) + QUERY PLAN +------------------------------------------------------ + Seq Scan on mc3p6 + Filter: ((a = 20) AND (c = 100) AND (abs(b) = 10)) +(2 rows) explain (costs off) select * from mc3p where a > 20; QUERY PLAN @@ -962,12 +944,11 @@ explain (costs off) select * from mc2p where a < 2; (9 rows) explain (costs off) select * from mc2p where a = 2 and b < 1; - QUERY PLAN ---------------------------------------- - Append - -> Seq Scan on mc2p3 - Filter: ((b < 1) AND (a = 2)) -(3 rows) + QUERY PLAN +--------------------------------- + Seq Scan on mc2p3 + Filter: ((b < 1) AND (a = 2)) +(2 rows) explain (costs off) select * from mc2p where a > 1; QUERY PLAN @@ -986,53 +967,47 @@ explain (costs off) select * from mc2p where a > 1; (11 rows) explain (costs off) select * from mc2p where a = 1 and b > 1; - QUERY PLAN ---------------------------------------- - Append - -> Seq Scan on mc2p2 - Filter: ((b > 1) AND (a = 1)) -(3 rows) + QUERY PLAN +--------------------------------- + Seq Scan on mc2p2 + Filter: ((b > 1) AND (a = 1)) +(2 rows) -- all partitions but the default one should be pruned explain (costs off) select * from mc2p where a = 1 and b is null; - QUERY PLAN -------------------------------------------- - Append - -> Seq Scan on mc2p_default - Filter: ((b IS NULL) AND (a = 1)) -(3 rows) + QUERY PLAN +------------------------------------- + Seq Scan on mc2p_default + Filter: ((b IS NULL) AND (a = 1)) +(2 rows) explain (costs off) select * from mc2p where a is null and b is null; - QUERY PLAN ------------------------------------------------ - Append - -> Seq Scan on mc2p_default - Filter: ((a IS NULL) AND (b IS NULL)) -(3 rows) + QUERY PLAN +----------------------------------------- + Seq Scan on mc2p_default + Filter: ((a IS NULL) AND (b IS NULL)) +(2 rows) explain (costs off) select * from mc2p where a is null and b = 1; - QUERY PLAN -------------------------------------------- - Append - -> Seq Scan on mc2p_default - Filter: ((a IS NULL) AND (b = 1)) -(3 rows) + QUERY PLAN +------------------------------------- + Seq Scan on mc2p_default + Filter: ((a IS NULL) AND (b = 1)) +(2 rows) explain (costs off) select * from mc2p where a is null; - QUERY PLAN --------------------------------- - Append - -> Seq Scan on mc2p_default - Filter: (a IS NULL) -(3 rows) + QUERY PLAN +-------------------------- + Seq Scan on mc2p_default + Filter: (a IS NULL) +(2 rows) explain (costs off) select * from mc2p where b is null; - QUERY PLAN --------------------------------- - Append - -> Seq Scan on mc2p_default - Filter: (b IS NULL) -(3 rows) + QUERY PLAN +-------------------------- + Seq Scan on mc2p_default + Filter: (b IS NULL) +(2 rows) -- boolean partitioning create table boolpart (a bool) partition by list (a); @@ -1050,20 +1025,18 @@ explain (costs off) select * from boolpart where a in (true, false); (5 rows) explain (costs off) select * from boolpart where a = false; - QUERY PLAN ------------------------------- - Append - -> Seq Scan on boolpart_f - Filter: (NOT a) -(3 rows) + QUERY PLAN +------------------------ + Seq Scan on boolpart_f + Filter: (NOT a) +(2 rows) explain (costs off) select * from boolpart where not a = false; - QUERY PLAN ------------------------------- - Append - -> Seq Scan on boolpart_t - Filter: a -(3 rows) + QUERY PLAN +------------------------ + Seq Scan on boolpart_t + Filter: a +(2 rows) explain (costs off) select * from boolpart where a is true or a is not true; QUERY PLAN @@ -1076,12 +1049,11 @@ explain (costs off) select * from boolpart where a is true or a is not true; (5 rows) explain (costs off) select * from boolpart where a is not true; - QUERY PLAN ---------------------------------- - Append - -> Seq Scan on boolpart_f - Filter: (a IS NOT TRUE) -(3 rows) + QUERY PLAN +--------------------------- + Seq Scan on boolpart_f + Filter: (a IS NOT TRUE) +(2 rows) explain (costs off) select * from boolpart where a is not true and a is not false; QUERY PLAN @@ -1190,10 +1162,9 @@ EXPLAIN (COSTS OFF) SELECT tableoid::regclass as part, a, b FROM part WHERE a IS --------------------------------------------------------------------------- Sort Sort Key: ((part_p2_p1.tableoid)::regclass), part_p2_p1.a, part_p2_p1.b - -> Append - -> Seq Scan on part_p2_p1 - Filter: (a IS NULL) -(5 rows) + -> Seq Scan on part_p2_p1 + Filter: (a IS NULL) +(4 rows) -- -- some more cases @@ -1260,13 +1231,12 @@ explain (costs off) select * from mc2p t1, lateral (select count(*) from mc3p t2 -- also here, because values for all keys are provided explain (costs off) select * from mc2p t1, lateral (select count(*) from mc3p t2 where t2.a = 1 and abs(t2.b) = 1 and t2.c = 1) s where t1.a = 1; - QUERY PLAN --------------------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------- Nested Loop -> Aggregate - -> Append - -> Seq Scan on mc3p1 t2 - Filter: ((a = 1) AND (c = 1) AND (abs(b) = 1)) + -> Seq Scan on mc3p1 t2 + Filter: ((a = 1) AND (c = 1) AND (abs(b) = 1)) -> Append -> Seq Scan on mc2p1 t1 Filter: (a = 1) @@ -1274,7 +1244,7 @@ explain (costs off) select * from mc2p t1, lateral (select count(*) from mc3p t2 Filter: (a = 1) -> Seq Scan on mc2p_default t1_2 Filter: (a = 1) -(12 rows) +(11 rows) -- -- pruning with clauses containing <> operator @@ -1395,12 +1365,11 @@ explain (costs off) select * from coll_pruning_multi where substr(a, 1) = 'a' co -- pruning, with values provided for both keys explain (costs off) select * from coll_pruning_multi where substr(a, 1) = 'e' collate "C" and substr(a, 1) = 'a' collate "POSIX"; - QUERY PLAN ---------------------------------------------------------------------------------------------------------- - Append - -> Seq Scan on coll_pruning_multi2 - Filter: ((substr(a, 1) = 'e'::text COLLATE "C") AND (substr(a, 1) = 'a'::text COLLATE "POSIX")) -(3 rows) + QUERY PLAN +--------------------------------------------------------------------------------------------------- + Seq Scan on coll_pruning_multi2 + Filter: ((substr(a, 1) = 'e'::text COLLATE "C") AND (substr(a, 1) = 'a'::text COLLATE "POSIX")) +(2 rows) -- -- LIKE operators don't prune @@ -1445,12 +1414,11 @@ explain (costs off) select * from rparted_by_int2 where a > 100000000000000; 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 explain (costs off) select * from rparted_by_int2 where a > 100000000000000; - QUERY PLAN -------------------------------------------------- - Append - -> Seq Scan on rparted_by_int2_maxvalue - Filter: (a > '100000000000000'::bigint) -(3 rows) + QUERY PLAN +------------------------------------------- + Seq Scan on rparted_by_int2_maxvalue + Filter: (a > '100000000000000'::bigint) +(2 rows) drop table lp, coll_pruning, rlp, mc3p, mc2p, boolpart, rp, coll_pruning_multi, like_op_noprune, lparted_by_int2, rparted_by_int2; -- @@ -1584,52 +1552,46 @@ explain (costs off) select * from hp where a <> 1 and b <> 'xxx'; -- pruning should work if either a value or a IS NULL clause is provided for -- each of the keys explain (costs off) select * from hp where a is null and b is null; - QUERY PLAN ------------------------------------------------ - Append - -> Seq Scan on hp0 - Filter: ((a IS NULL) AND (b IS NULL)) -(3 rows) + QUERY PLAN +----------------------------------------- + Seq Scan on hp0 + Filter: ((a IS NULL) AND (b IS NULL)) +(2 rows) explain (costs off) select * from hp where a = 1 and b is null; - QUERY PLAN -------------------------------------------- - Append - -> Seq Scan on hp1 - Filter: ((b IS NULL) AND (a = 1)) -(3 rows) + QUERY PLAN +------------------------------------- + Seq Scan on hp1 + Filter: ((b IS NULL) AND (a = 1)) +(2 rows) explain (costs off) select * from hp where a = 1 and b = 'xxx'; - QUERY PLAN -------------------------------------------------- - Append - -> Seq Scan on hp0 - Filter: ((a = 1) AND (b = 'xxx'::text)) -(3 rows) + QUERY PLAN +------------------------------------------- + Seq Scan on hp0 + Filter: ((a = 1) AND (b = 'xxx'::text)) +(2 rows) explain (costs off) select * from hp where a is null and b = 'xxx'; - QUERY PLAN ------------------------------------------------------ - Append - -> Seq Scan on hp2 - Filter: ((a IS NULL) AND (b = 'xxx'::text)) -(3 rows) + QUERY PLAN +----------------------------------------------- + Seq Scan on hp2 + Filter: ((a IS NULL) AND (b = 'xxx'::text)) +(2 rows) explain (costs off) select * from hp where a = 2 and b = 'xxx'; - QUERY PLAN -------------------------------------------------- - Append - -> Seq Scan on hp3 - Filter: ((a = 2) AND (b = 'xxx'::text)) -(3 rows) + QUERY PLAN +------------------------------------------- + Seq Scan on hp3 + Filter: ((a = 2) AND (b = 'xxx'::text)) +(2 rows) explain (costs off) select * from hp where a = 1 and b = 'abcde'; - QUERY PLAN ---------------------------------------------------- - Append - -> Seq Scan on hp2 - Filter: ((a = 1) AND (b = 'abcde'::text)) -(3 rows) + QUERY PLAN +--------------------------------------------- + Seq Scan on hp2 + Filter: ((a = 1) AND (b = 'abcde'::text)) +(2 rows) explain (costs off) select * from hp where (a = 1 and b = 'abcde') or (a = 2 and b = 'xxx') or (a is null and b is null); QUERY PLAN @@ -2878,12 +2840,11 @@ execute part_abc_q1 (1, 2, 3); -- Single partition should be scanned. explain (analyze, costs off, summary off, timing off) execute part_abc_q1 (1, 2, 3); - QUERY PLAN -------------------------------------------------------- - Append (actual rows=0 loops=1) - -> Seq Scan on part_abc_p1 (actual rows=0 loops=1) - Filter: ((a = $1) AND (b = $2) AND (c = $3)) -(3 rows) + QUERY PLAN +------------------------------------------------- + Seq Scan on part_abc_p1 (actual rows=0 loops=1) + Filter: ((a = $1) AND (b = $2) AND (c = $3)) +(2 rows) deallocate part_abc_q1; drop table part_abc; @@ -3205,12 +3166,11 @@ create table pp_arrpart (a int[]) partition by list (a); create table pp_arrpart1 partition of pp_arrpart for values in ('{1}'); create table pp_arrpart2 partition of pp_arrpart for values in ('{2, 3}', '{4, 5}'); explain (costs off) select * from pp_arrpart where a = '{1}'; - QUERY PLAN ----------------------------------------- - Append - -> Seq Scan on pp_arrpart1 - Filter: (a = '{1}'::integer[]) -(3 rows) + QUERY PLAN +---------------------------------- + Seq Scan on pp_arrpart1 + Filter: (a = '{1}'::integer[]) +(2 rows) explain (costs off) select * from pp_arrpart where a = '{1, 2}'; QUERY PLAN @@ -3262,20 +3222,18 @@ select tableoid::regclass, * from pph_arrpart order by 1; (3 rows) explain (costs off) select * from pph_arrpart where a = '{1}'; - QUERY PLAN ----------------------------------------- - Append - -> Seq Scan on pph_arrpart2 - Filter: (a = '{1}'::integer[]) -(3 rows) + QUERY PLAN +---------------------------------- + Seq Scan on pph_arrpart2 + Filter: (a = '{1}'::integer[]) +(2 rows) explain (costs off) select * from pph_arrpart where a = '{1, 2}'; - QUERY PLAN ------------------------------------------- - Append - -> Seq Scan on pph_arrpart1 - Filter: (a = '{1,2}'::integer[]) -(3 rows) + QUERY PLAN +------------------------------------ + Seq Scan on pph_arrpart1 + Filter: (a = '{1,2}'::integer[]) +(2 rows) explain (costs off) select * from pph_arrpart where a in ('{4, 5}', '{1}'); QUERY PLAN @@ -3294,12 +3252,11 @@ create table pp_enumpart (a pp_colors) partition by list (a); create table pp_enumpart_green partition of pp_enumpart for values in ('green'); create table pp_enumpart_blue partition of pp_enumpart for values in ('blue'); explain (costs off) select * from pp_enumpart where a = 'blue'; - QUERY PLAN ------------------------------------------ - Append - -> Seq Scan on pp_enumpart_blue - Filter: (a = 'blue'::pp_colors) -(3 rows) + QUERY PLAN +----------------------------------- + Seq Scan on pp_enumpart_blue + Filter: (a = 'blue'::pp_colors) +(2 rows) explain (costs off) select * from pp_enumpart where a = 'black'; QUERY PLAN @@ -3316,12 +3273,11 @@ create table pp_recpart (a pp_rectype) partition by list (a); create table pp_recpart_11 partition of pp_recpart for values in ('(1,1)'); create table pp_recpart_23 partition of pp_recpart for values in ('(2,3)'); explain (costs off) select * from pp_recpart where a = '(1,1)'::pp_rectype; - QUERY PLAN -------------------------------------------- - Append - -> Seq Scan on pp_recpart_11 - Filter: (a = '(1,1)'::pp_rectype) -(3 rows) + QUERY PLAN +------------------------------------- + Seq Scan on pp_recpart_11 + Filter: (a = '(1,1)'::pp_rectype) +(2 rows) explain (costs off) select * from pp_recpart where a = '(1,2)'::pp_rectype; QUERY PLAN @@ -3337,12 +3293,11 @@ create table pp_intrangepart (a int4range) partition by list (a); create table pp_intrangepart12 partition of pp_intrangepart for values in ('[1,2]'); create table pp_intrangepart2inf partition of pp_intrangepart for values in ('[2,)'); explain (costs off) select * from pp_intrangepart where a = '[1,2]'::int4range; - QUERY PLAN ------------------------------------------- - Append - -> Seq Scan on pp_intrangepart12 - Filter: (a = '[1,3)'::int4range) -(3 rows) + QUERY PLAN +------------------------------------ + Seq Scan on pp_intrangepart12 + Filter: (a = '[1,3)'::int4range) +(2 rows) explain (costs off) select * from pp_intrangepart where a = '(1,2)'::int4range; QUERY PLAN @@ -3359,12 +3314,11 @@ create table pp_lp (a int, value int) partition by list (a); create table pp_lp1 partition of pp_lp for values in(1); create table pp_lp2 partition of pp_lp for values in(2); explain (costs off) select * from pp_lp where a = 1; - QUERY PLAN --------------------------- - Append - -> Seq Scan on pp_lp1 - Filter: (a = 1) -(3 rows) + QUERY PLAN +-------------------- + Seq Scan on pp_lp1 + Filter: (a = 1) +(2 rows) explain (costs off) update pp_lp set value = 10 where a = 1; QUERY PLAN @@ -3529,12 +3483,11 @@ explain (costs off) select * from pp_temp_parent where true; (3 rows) explain (costs off) select * from pp_temp_parent where a = 2; - QUERY PLAN ------------------------------------- - Append - -> Seq Scan on pp_temp_part_def - Filter: (a = 2) -(3 rows) + QUERY PLAN +------------------------------ + Seq Scan on pp_temp_part_def + Filter: (a = 2) +(2 rows) drop table pp_temp_parent; -- Stress run-time partition pruning a bit more, per bug reports @@ -3628,13 +3581,12 @@ create table listp2 partition of listp for values in(2) partition by list(b); create table listp2_10 partition of listp2 for values in (10); explain (analyze, costs off, summary off, timing off) select * from listp where a = (select 2) and b <> 10; - QUERY PLAN -------------------------------------------- - Append (actual rows=0 loops=1) + QUERY PLAN +-------------------------------------------- + Seq Scan on listp1 (actual rows=0 loops=1) + Filter: ((b <> 10) AND (a = $0)) InitPlan 1 (returns $0) - -> Result (actual rows=1 loops=1) - -> Seq Scan on listp1 (never executed) - Filter: ((b <> 10) AND (a = $0)) -(5 rows) + -> Result (never executed) +(4 rows) drop table listp; diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out index 4e45b7f3c86..4b53401b003 100644 --- a/src/test/regress/expected/rowsecurity.out +++ b/src/test/regress/expected/rowsecurity.out @@ -1057,15 +1057,14 @@ NOTICE: f_leak => awesome science fiction (4 rows) EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle); - QUERY PLAN --------------------------------------------------------------------- - Append + QUERY PLAN +-------------------------------------------------------------- + Seq Scan on part_document_fiction + Filter: ((cid < 55) AND (dlevel <= $0) AND f_leak(dtitle)) InitPlan 1 (returns $0) -> Index Scan using uaccount_pkey on uaccount Index Cond: (pguser = CURRENT_USER) - -> Seq Scan on part_document_fiction - Filter: ((cid < 55) AND (dlevel <= $0) AND f_leak(dtitle)) -(6 rows) +(5 rows) -- pp1 ERROR INSERT INTO part_document VALUES (100, 11, 5, 'regress_rls_dave', 'testing pp1'); -- fail @@ -1136,15 +1135,14 @@ NOTICE: f_leak => awesome science fiction (4 rows) EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle); - QUERY PLAN --------------------------------------------------------------------- - Append + QUERY PLAN +-------------------------------------------------------------- + Seq Scan on part_document_fiction + Filter: ((cid < 55) AND (dlevel <= $0) AND f_leak(dtitle)) InitPlan 1 (returns $0) -> Index Scan using uaccount_pkey on uaccount Index Cond: (pguser = CURRENT_USER) - -> Seq Scan on part_document_fiction - Filter: ((cid < 55) AND (dlevel <= $0) AND f_leak(dtitle)) -(6 rows) +(5 rows) -- viewpoint from regress_rls_carol SET SESSION AUTHORIZATION regress_rls_carol; diff --git a/src/test/regress/expected/union.out b/src/test/regress/expected/union.out index 92d427a690f..da70438951d 100644 --- a/src/test/regress/expected/union.out +++ b/src/test/regress/expected/union.out @@ -812,11 +812,10 @@ explain (costs off) UNION ALL SELECT 2 AS t, * FROM tenk1 b) c WHERE t = 2; - QUERY PLAN ---------------------------- - Append - -> Seq Scan on tenk1 b -(2 rows) + QUERY PLAN +--------------------- + Seq Scan on tenk1 b +(1 row) -- Test that we push quals into UNION sub-selects only when it's safe explain (costs off) |