Re: EquivalenceClasses and subqueries and PlaceHolderVars, oh my - Mailing list pgsql-hackers
| From | Tom Lane |
|---|---|
| Subject | Re: EquivalenceClasses and subqueries and PlaceHolderVars, oh my |
| Date | |
| Msg-id | 11635.1331910974@sss.pgh.pa.us Whole thread Raw |
| In response to | Re: EquivalenceClasses and subqueries and PlaceHolderVars, oh my (Tom Lane <tgl@sss.pgh.pa.us>) |
| Responses |
Re: EquivalenceClasses and subqueries and PlaceHolderVars, oh my
|
| List | pgsql-hackers |
I wrote:
> So I now propose reverting the earlier two patches (but not their
> regression test cases of course) and instead hacking MergeAppend plan
> building as per (2).
Attached is a draft patch for that. There are several things going on
here:
* Revert the preceding two patches (except for their regression test
cases).
* Install defenses to ensure that child EC members are ignored except in
very specific contexts, and improve documentation around that. The most
significant change is that get_eclass_for_sort_expr now takes a Relids
argument, and won't consider child members unless they match the Relids.
It turns out that the places that were trying to match indexes to ECs
already acted that way; which I think I had done just as a speed
optimization, but it turns out to be important for correctness too.
* Rewrite prepare_sort_from_pathkeys() to allow required sort column
numbers to be passed in, thus fixing Teodor's original problem more
directly. As part of that, I removed createplan.c's add_sort_column()
code, which was meant as a last-ditch check that we don't build sorts
with useless duplicate sort columns. As far as I can tell, that's dead
code and has been for a long time, because we already eliminate
duplicate pathkeys or SortGroupClause list entries far upstream of this.
Even if there are some corner cases where it does something useful,
that's rare enough to not really justify expending the cycles. I had to
remove it because if it did fire and remove a sort column, the outer
loop in prepare_sort_from_pathkeys() wouldn't be in sync with the input
column-number array.
* While testing this I noticed that planagg.c failed to optimize min/max
aggregates on appendrels that are made from UNION ALL subqueries rather
than inherited relations. So this patch also tweaks planagg.c to handle
such cases.
Viewing the problem this way, the only known symptom is the originally
reported "MergeAppend child's targetlist doesn't match MergeAppend",
which of course is not an issue before 9.1, so I'm not going to try
to back-patch further than 9.1. It is possible that we need some of the
child EC member defenses further back, but I'll await some evidence of
user-visible bugs first.
regards, tom lane
diff --git a/src/backend/optimizer/README b/src/backend/optimizer/README
index ee450fb02e4e9e8942ff5a0d6b062c9ae5d18cba..9ab1fdf8154b964be91eb54ff2a3f38e9705af74 100644
*** a/src/backend/optimizer/README
--- b/src/backend/optimizer/README
*************** it's possible that it belongs to more th
*** 496,501 ****
--- 496,509 ----
families to ensure that we can make use of an index belonging to any one of
the families for mergejoin purposes.)
+ An EquivalenceClass can contain "em_is_child" members, which are copies
+ of members that contain appendrel parent relation Vars, transposed to
+ contain the equivalent child-relation variables or expressions. These
+ members are *not* full-fledged members of the EquivalenceClass and do not
+ affect the class's overall properties at all. They are kept only to
+ simplify matching of child-relation expressions to EquivalenceClasses.
+ Most operations on EquivalenceClasses should ignore child members.
+
PathKeys
--------
diff --git a/src/backend/optimizer/path/equivclass.c b/src/backend/optimizer/path/equivclass.c
index b653d6cb35ca338c183bb944757081e46ca50c50..c115c2148db109476dd607f99a178e25a5f76a24 100644
*** a/src/backend/optimizer/path/equivclass.c
--- b/src/backend/optimizer/path/equivclass.c
*************** add_eq_member(EquivalenceClass *ec, Expr
*** 491,496 ****
--- 491,505 ----
* sortref is the SortGroupRef of the originating SortGroupClause, if any,
* or zero if not. (It should never be zero if the expression is volatile!)
*
+ * If rel is not NULL, it identifies a specific relation we're considering
+ * a path for, and indicates that child EC members for that relation can be
+ * considered. Otherwise child members are ignored. (Note: since child EC
+ * members aren't guaranteed unique, a non-NULL value means that there could
+ * be more than one EC that matches the expression; if so it's order-dependent
+ * which one you get. This is annoying but it only happens in corner cases,
+ * so for now we live with just reporting the first match. See also
+ * generate_implied_equalities_for_indexcol and match_pathkeys_to_index.)
+ *
* If create_it is TRUE, we'll build a new EquivalenceClass when there is no
* match. If create_it is FALSE, we just return NULL when no match.
*
*************** get_eclass_for_sort_expr(PlannerInfo *ro
*** 511,516 ****
--- 520,526 ----
Oid opcintype,
Oid collation,
Index sortref,
+ Relids rel,
bool create_it)
{
EquivalenceClass *newec;
*************** get_eclass_for_sort_expr(PlannerInfo *ro
*** 549,554 ****
--- 559,571 ----
EquivalenceMember *cur_em = (EquivalenceMember *) lfirst(lc2);
/*
+ * Ignore child members unless they match the request.
+ */
+ if (cur_em->em_is_child &&
+ !bms_equal(cur_em->em_relids, rel))
+ continue;
+
+ /*
* If below an outer join, don't match constants: they're not as
* constant as they look.
*/
*************** reconsider_outer_join_clause(PlannerInfo
*** 1505,1510 ****
--- 1522,1528 ----
{
EquivalenceMember *cur_em = (EquivalenceMember *) lfirst(lc2);
+ Assert(!cur_em->em_is_child); /* no children yet */
if (equal(outervar, cur_em->em_expr))
{
match = true;
*************** reconsider_full_join_clause(PlannerInfo
*** 1626,1631 ****
--- 1644,1650 ----
foreach(lc2, cur_ec->ec_members)
{
coal_em = (EquivalenceMember *) lfirst(lc2);
+ Assert(!coal_em->em_is_child); /* no children yet */
if (IsA(coal_em->em_expr, CoalesceExpr))
{
CoalesceExpr *cexpr = (CoalesceExpr *) coal_em->em_expr;
*************** exprs_known_equal(PlannerInfo *root, Nod
*** 1747,1752 ****
--- 1766,1773 ----
{
EquivalenceMember *em = (EquivalenceMember *) lfirst(lc2);
+ if (em->em_is_child)
+ continue; /* ignore children here */
if (equal(item1, em->em_expr))
item1member = true;
else if (equal(item2, em->em_expr))
*************** add_child_rel_equivalences(PlannerInfo *
*** 1800,1805 ****
--- 1821,1829 ----
{
EquivalenceMember *cur_em = (EquivalenceMember *) lfirst(lc2);
+ if (cur_em->em_is_child)
+ continue; /* ignore children here */
+
/* Does it reference (only) parent_rel? */
if (bms_equal(cur_em->em_relids, parent_rel->relids))
{
*************** generate_implied_equalities_for_indexcol
*** 1908,1914 ****
!bms_is_subset(rel->relids, cur_ec->ec_relids))
continue;
! /* Scan members, looking for a match to the indexable column */
cur_em = NULL;
foreach(lc2, cur_ec->ec_members)
{
--- 1932,1947 ----
!bms_is_subset(rel->relids, cur_ec->ec_relids))
continue;
! /*
! * Scan members, looking for a match to the indexable column. Note
! * that child EC members are considered, but only when they belong to
! * the target relation. (Unlike regular members, the same expression
! * could be a child member of more than one EC. Therefore, it's
! * potentially order-dependent which EC a child relation's index
! * column gets matched to. This is annoying but it only happens in
! * corner cases, so for now we live with just reporting the first
! * match. See also get_eclass_for_sort_expr.)
! */
cur_em = NULL;
foreach(lc2, cur_ec->ec_members)
{
*************** generate_implied_equalities_for_indexcol
*** 1933,1938 ****
--- 1966,1974 ----
Oid eq_op;
RestrictInfo *rinfo;
+ if (other_em->em_is_child)
+ continue; /* ignore children here */
+
/* Make sure it'll be a join to a different rel */
if (other_em == cur_em ||
bms_overlap(other_em->em_relids, rel->relids))
*************** eclass_useful_for_merging(EquivalenceCla
*** 2187,2194 ****
{
EquivalenceMember *cur_em = (EquivalenceMember *) lfirst(lc);
! if (!cur_em->em_is_child &&
! !bms_overlap(cur_em->em_relids, rel->relids))
return true;
}
--- 2223,2232 ----
{
EquivalenceMember *cur_em = (EquivalenceMember *) lfirst(lc);
! if (cur_em->em_is_child)
! continue; /* ignore children here */
!
! if (!bms_overlap(cur_em->em_relids, rel->relids))
return true;
}
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index 2f088b797879fd67f27b5b8adbcdfce292fce0ea..89b42da6b46517d86ffebe882348aa7376520b81 100644
*** a/src/backend/optimizer/path/indxpath.c
--- b/src/backend/optimizer/path/indxpath.c
*************** match_pathkeys_to_index(IndexOptInfo *in
*** 2157,2163 ****
if (pathkey->pk_eclass->ec_has_volatile)
return;
! /* Try to match eclass member expression(s) to index */
foreach(lc2, pathkey->pk_eclass->ec_members)
{
EquivalenceMember *member = (EquivalenceMember *) lfirst(lc2);
--- 2157,2170 ----
if (pathkey->pk_eclass->ec_has_volatile)
return;
! /*
! * Try to match eclass member expression(s) to index. Note that child
! * EC members are considered, but only when they belong to the target
! * relation. (Unlike regular members, the same expression could be a
! * child member of more than one EC. Therefore, the same index could
! * be considered to match more than one pathkey list, which is OK
! * here. See also get_eclass_for_sort_expr.)
! */
foreach(lc2, pathkey->pk_eclass->ec_members)
{
EquivalenceMember *member = (EquivalenceMember *) lfirst(lc2);
*************** match_index_to_operand(Node *operand,
*** 2581,2595 ****
int indkey;
/*
- * Ignore any PlaceHolderVar nodes above the operand. This is needed so
- * that we can successfully use expression-index constraints pushed down
- * through appendrels (UNION ALL). It's safe because a PlaceHolderVar
- * appearing in a relation-scan-level expression is certainly a no-op.
- */
- while (operand && IsA(operand, PlaceHolderVar))
- operand = (Node *) ((PlaceHolderVar *) operand)->phexpr;
-
- /*
* Ignore any RelabelType node above the operand. This is needed to be
* able to apply indexscanning in binary-compatible-operator cases. Note:
* we can assume there is at most one RelabelType node;
--- 2588,2593 ----
diff --git a/src/backend/optimizer/path/pathkeys.c b/src/backend/optimizer/path/pathkeys.c
index 653387e582d8900bd9353d8f78ccb7764f5dc727..4ff8016666d7f2785c0d64ae9012d686e1ab7382 100644
*** a/src/backend/optimizer/path/pathkeys.c
--- b/src/backend/optimizer/path/pathkeys.c
*************** canonicalize_pathkeys(PlannerInfo *root,
*** 221,226 ****
--- 221,231 ----
* If the PathKey is being generated from a SortGroupClause, sortref should be
* the SortGroupClause's SortGroupRef; otherwise zero.
*
+ * If rel is not NULL, it identifies a specific relation we're considering
+ * a path for, and indicates that child EC members for that relation can be
+ * considered. Otherwise child members are ignored. (See the comments for
+ * get_eclass_for_sort_expr.)
+ *
* create_it is TRUE if we should create any missing EquivalenceClass
* needed to represent the sort key. If it's FALSE, we return NULL if the
* sort key isn't already present in any EquivalenceClass.
*************** make_pathkey_from_sortinfo(PlannerInfo *
*** 237,242 ****
--- 242,248 ----
bool reverse_sort,
bool nulls_first,
Index sortref,
+ Relids rel,
bool create_it,
bool canonicalize)
{
*************** make_pathkey_from_sortinfo(PlannerInfo *
*** 268,274 ****
/* Now find or (optionally) create a matching EquivalenceClass */
eclass = get_eclass_for_sort_expr(root, expr, opfamilies,
opcintype, collation,
! sortref, create_it);
/* Fail if no EC and !create_it */
if (!eclass)
--- 274,280 ----
/* Now find or (optionally) create a matching EquivalenceClass */
eclass = get_eclass_for_sort_expr(root, expr, opfamilies,
opcintype, collation,
! sortref, rel, create_it);
/* Fail if no EC and !create_it */
if (!eclass)
*************** make_pathkey_from_sortop(PlannerInfo *ro
*** 320,325 ****
--- 326,332 ----
(strategy == BTGreaterStrategyNumber),
nulls_first,
sortref,
+ NULL,
create_it,
canonicalize);
}
*************** build_index_pathkeys(PlannerInfo *root,
*** 546,551 ****
--- 553,559 ----
reverse_sort,
nulls_first,
0,
+ index->rel->relids,
false,
true);
*************** convert_subquery_pathkeys(PlannerInfo *r
*** 636,641 ****
--- 644,650 ----
sub_member->em_datatype,
sub_eclass->ec_collation,
0,
+ rel->relids,
false);
/*
*************** convert_subquery_pathkeys(PlannerInfo *r
*** 680,685 ****
--- 689,697 ----
Oid sub_expr_coll = sub_eclass->ec_collation;
ListCell *k;
+ if (sub_member->em_is_child)
+ continue; /* ignore children here */
+
foreach(k, sub_tlist)
{
TargetEntry *tle = (TargetEntry *) lfirst(k);
*************** convert_subquery_pathkeys(PlannerInfo *r
*** 719,724 ****
--- 731,737 ----
sub_expr_type,
sub_expr_coll,
0,
+ rel->relids,
false);
/*
*************** initialize_mergeclause_eclasses(PlannerI
*** 910,915 ****
--- 923,929 ----
lefttype,
((OpExpr *) clause)->inputcollid,
0,
+ NULL,
true);
restrictinfo->right_ec =
get_eclass_for_sort_expr(root,
*************** initialize_mergeclause_eclasses(PlannerI
*** 918,923 ****
--- 932,938 ----
righttype,
((OpExpr *) clause)->inputcollid,
0,
+ NULL,
true);
}
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index 94140d304f754236955452e26b80de8276ca729b..ef2a1e70c96607f40290bf58770befc00da9853c 100644
*** a/src/backend/optimizer/plan/createplan.c
--- b/src/backend/optimizer/plan/createplan.c
*************** static Sort *make_sort(PlannerInfo *root
*** 152,163 ****
--- 152,168 ----
double limit_tuples);
static Plan *prepare_sort_from_pathkeys(PlannerInfo *root,
Plan *lefttree, List *pathkeys,
+ Relids relids,
+ const AttrNumber *reqColIdx,
bool adjust_tlist_in_place,
int *p_numsortkeys,
AttrNumber **p_sortColIdx,
Oid **p_sortOperators,
Oid **p_collations,
bool **p_nullsFirst);
+ static EquivalenceMember *find_ec_member_for_tle(EquivalenceClass *ec,
+ TargetEntry *tle,
+ Relids relids);
static Material *make_material(Plan *lefttree);
*************** create_merge_append_plan(PlannerInfo *ro
*** 706,711 ****
--- 711,718 ----
/* Compute sort column info, and adjust MergeAppend's tlist as needed */
(void) prepare_sort_from_pathkeys(root, plan, pathkeys,
+ NULL,
+ NULL,
true,
&node->numCols,
&node->sortColIdx,
*************** create_merge_append_plan(PlannerInfo *ro
*** 733,738 ****
--- 740,747 ----
/* Compute sort column info, and adjust subplan's tlist as needed */
subplan = prepare_sort_from_pathkeys(root, subplan, pathkeys,
+ subpath->parent->relids,
+ node->sortColIdx,
false,
&numsortkeys,
&sortColIdx,
*************** fix_indexqual_operand(Node *node, IndexO
*** 2695,2705 ****
ListCell *indexpr_item;
/*
! * Remove any PlaceHolderVars or binary-compatible relabeling of the
! * indexkey (this must match logic in match_index_to_operand()).
*/
- while (IsA(node, PlaceHolderVar))
- node = (Node *) ((PlaceHolderVar *) node)->phexpr;
if (IsA(node, RelabelType))
node = (Node *) ((RelabelType *) node)->arg;
--- 2704,2711 ----
ListCell *indexpr_item;
/*
! * Remove any binary-compatible relabeling of the indexkey
*/
if (IsA(node, RelabelType))
node = (Node *) ((RelabelType *) node)->arg;
*************** make_sort(PlannerInfo *root, Plan *leftt
*** 3516,3570 ****
}
/*
- * add_sort_column --- utility subroutine for building sort info arrays
- *
- * We need this routine because the same column might be selected more than
- * once as a sort key column; if so, the extra mentions are redundant.
- *
- * Caller is assumed to have allocated the arrays large enough for the
- * max possible number of columns. Return value is the new column count.
- */
- static int
- add_sort_column(AttrNumber colIdx, Oid sortOp, Oid coll, bool nulls_first,
- int numCols, AttrNumber *sortColIdx,
- Oid *sortOperators, Oid *collations, bool *nullsFirst)
- {
- int i;
-
- Assert(OidIsValid(sortOp));
-
- for (i = 0; i < numCols; i++)
- {
- /*
- * Note: we check sortOp because it's conceivable that "ORDER BY foo
- * USING <, foo USING <<<" is not redundant, if <<< distinguishes
- * values that < considers equal. We need not check nulls_first
- * however because a lower-order column with the same sortop but
- * opposite nulls direction is redundant.
- *
- * We could probably consider sort keys with the same sortop and
- * different collations to be redundant too, but for the moment treat
- * them as not redundant. This will be needed if we ever support
- * collations with different notions of equality.
- */
- if (sortColIdx[i] == colIdx &&
- sortOperators[numCols] == sortOp &&
- collations[numCols] == coll)
- {
- /* Already sorting by this col, so extra sort key is useless */
- return numCols;
- }
- }
-
- /* Add the column */
- sortColIdx[numCols] = colIdx;
- sortOperators[numCols] = sortOp;
- collations[numCols] = coll;
- nullsFirst[numCols] = nulls_first;
- return numCols + 1;
- }
-
- /*
* prepare_sort_from_pathkeys
* Prepare to sort according to given pathkeys
*
--- 3522,3527 ----
*************** add_sort_column(AttrNumber colIdx, Oid s
*** 3573,3580 ****
* plan targetlist if needed to add resjunk sort columns.
*
* Input parameters:
! * 'lefttree' is the node which yields input tuples
* 'pathkeys' is the list of pathkeys by which the result is to be sorted
* 'adjust_tlist_in_place' is TRUE if lefttree must be modified in-place
*
* We must convert the pathkey information into arrays of sort key column
--- 3530,3539 ----
* plan targetlist if needed to add resjunk sort columns.
*
* Input parameters:
! * 'lefttree' is the plan node which yields input tuples
* 'pathkeys' is the list of pathkeys by which the result is to be sorted
+ * 'relids' identifies the child relation being sorted, if any
+ * 'reqColIdx' is NULL or an array of required sort key column numbers
* 'adjust_tlist_in_place' is TRUE if lefttree must be modified in-place
*
* We must convert the pathkey information into arrays of sort key column
*************** add_sort_column(AttrNumber colIdx, Oid s
*** 3582,3587 ****
--- 3541,3554 ----
* which is the representation the executor wants. These are returned into
* the output parameters *p_numsortkeys etc.
*
+ * When looking for matches to an EquivalenceClass's members, we will only
+ * consider child EC members if they match 'relids'. This protects against
+ * possible incorrect matches to child expressions that contain no Vars.
+ *
+ * If reqColIdx isn't NULL then it contains sort key column numbers that
+ * we should match. This is used when making child plans for a MergeAppend;
+ * it's an error if we can't match the columns.
+ *
* If the pathkeys include expressions that aren't simple Vars, we will
* usually need to add resjunk items to the input plan's targetlist to
* compute these expressions, since the Sort/MergeAppend node itself won't
*************** add_sort_column(AttrNumber colIdx, Oid s
*** 3596,3601 ****
--- 3563,3570 ----
*/
static Plan *
prepare_sort_from_pathkeys(PlannerInfo *root, Plan *lefttree, List *pathkeys,
+ Relids relids,
+ const AttrNumber *reqColIdx,
bool adjust_tlist_in_place,
int *p_numsortkeys,
AttrNumber **p_sortColIdx,
*************** prepare_sort_from_pathkeys(PlannerInfo *
*** 3626,3631 ****
--- 3595,3601 ----
{
PathKey *pathkey = (PathKey *) lfirst(i);
EquivalenceClass *ec = pathkey->pk_eclass;
+ EquivalenceMember *em;
TargetEntry *tle = NULL;
Oid pk_datatype = InvalidOid;
Oid sortop;
*************** prepare_sort_from_pathkeys(PlannerInfo *
*** 3645,3660 ****
Assert(list_length(ec->ec_members) == 1);
pk_datatype = ((EquivalenceMember *) linitial(ec->ec_members))->em_datatype;
}
else
{
/*
* Otherwise, we can sort by any non-constant expression listed in
! * the pathkey's EquivalenceClass. For now, we take the first one
! * that corresponds to an available item in the tlist. If there
! * isn't any, use the first one that is an expression in the
! * input's vars. (The non-const restriction only matters if the
! * EC is below_outer_join; but if it isn't, it won't contain
! * consts anyway, else we'd have discarded the pathkey as
* redundant.)
*
* XXX if we have a choice, is there any way of figuring out which
--- 3615,3655 ----
Assert(list_length(ec->ec_members) == 1);
pk_datatype = ((EquivalenceMember *) linitial(ec->ec_members))->em_datatype;
}
+ else if (reqColIdx != NULL)
+ {
+ /*
+ * If we are given a sort column number to match, only consider
+ * the single TLE at that position. It's possible that there
+ * is no such TLE, in which case fall through and generate a
+ * resjunk targetentry (we assume this must have happened in the
+ * parent plan as well). If there is a TLE but it doesn't match
+ * the pathkey's EC, we do the same, which is probably the wrong
+ * thing but we'll leave it to caller to complain about the
+ * mismatch.
+ */
+ tle = get_tle_by_resno(tlist, reqColIdx[numsortkeys]);
+ if (tle)
+ {
+ em = find_ec_member_for_tle(ec, tle, relids);
+ if (em)
+ {
+ /* found expr at right place in tlist */
+ pk_datatype = em->em_datatype;
+ }
+ else
+ tle = NULL;
+ }
+ }
else
{
/*
* Otherwise, we can sort by any non-constant expression listed in
! * the pathkey's EquivalenceClass. For now, we take the first
! * tlist item found in the EC. If there's no match, we'll generate
! * a resjunk entry using the first EC member that is an expression
! * in the input's vars. (The non-const restriction only matters
! * if the EC is below_outer_join; but if it isn't, it won't
! * contain consts anyway, else we'd have discarded the pathkey as
* redundant.)
*
* XXX if we have a choice, is there any way of figuring out which
*************** prepare_sort_from_pathkeys(PlannerInfo *
*** 3663,3671 ****
--- 3658,3693 ----
* in the same equivalence class...) Not clear that we ever will
* have an interesting choice in practice, so it may not matter.
*/
+ foreach(j, tlist)
+ {
+ tle = (TargetEntry *) lfirst(j);
+ em = find_ec_member_for_tle(ec, tle, relids);
+ if (em)
+ {
+ /* found expr already in tlist */
+ pk_datatype = em->em_datatype;
+ break;
+ }
+ tle = NULL;
+ }
+ }
+
+ if (!tle)
+ {
+ /*
+ * No matching tlist item; look for a computable expression.
+ * Note that we treat Aggrefs as if they were variables; this
+ * is necessary when attempting to sort the output from an Agg
+ * node for use in a WindowFunc (since grouping_planner will
+ * have treated the Aggrefs as variables, too).
+ */
+ Expr *sortexpr = NULL;
+
foreach(j, ec->ec_members)
{
EquivalenceMember *em = (EquivalenceMember *) lfirst(j);
+ List *exprvars;
+ ListCell *k;
/*
* We shouldn't be trying to sort by an equivalence class that
*************** prepare_sort_from_pathkeys(PlannerInfo *
*** 3675,3765 ****
if (em->em_is_const)
continue;
- tle = tlist_member((Node *) em->em_expr, tlist);
- if (tle)
- {
- pk_datatype = em->em_datatype;
- break; /* found expr already in tlist */
- }
-
/*
! * We can also use it if the pathkey expression is a relabel
! * of the tlist entry, or vice versa. This is needed for
! * binary-compatible cases (cf. make_pathkey_from_sortinfo).
! * We prefer an exact match, though, so we do the basic search
! * first.
*/
! tle = tlist_member_ignore_relabel((Node *) em->em_expr, tlist);
! if (tle)
{
pk_datatype = em->em_datatype;
! break; /* found expr already in tlist */
}
}
! if (!tle)
{
! /*
! * No matching tlist item; look for a computable expression.
! * Note that we treat Aggrefs as if they were variables; this
! * is necessary when attempting to sort the output from an Agg
! * node for use in a WindowFunc (since grouping_planner will
! * have treated the Aggrefs as variables, too).
! */
! Expr *sortexpr = NULL;
!
! foreach(j, ec->ec_members)
! {
! EquivalenceMember *em = (EquivalenceMember *) lfirst(j);
! List *exprvars;
! ListCell *k;
!
! if (em->em_is_const)
! continue;
! sortexpr = em->em_expr;
! exprvars = pull_var_clause((Node *) sortexpr,
! PVC_INCLUDE_AGGREGATES,
! PVC_INCLUDE_PLACEHOLDERS);
! foreach(k, exprvars)
! {
! if (!tlist_member_ignore_relabel(lfirst(k), tlist))
! break;
! }
! list_free(exprvars);
! if (!k)
! {
! pk_datatype = em->em_datatype;
! break; /* found usable expression */
! }
! }
! if (!j)
! elog(ERROR, "could not find pathkey item to sort");
!
! /*
! * Do we need to insert a Result node?
! */
! if (!adjust_tlist_in_place &&
! !is_projection_capable_plan(lefttree))
! {
! /* copy needed so we don't modify input's tlist below */
! tlist = copyObject(tlist);
! lefttree = (Plan *) make_result(root, tlist, NULL,
! lefttree);
! }
! /* Don't bother testing is_projection_capable_plan again */
! adjust_tlist_in_place = true;
! /*
! * Add resjunk entry to input's tlist
! */
! tle = makeTargetEntry(sortexpr,
! list_length(tlist) + 1,
! NULL,
! true);
! tlist = lappend(tlist, tle);
! lefttree->targetlist = tlist; /* just in case NIL before */
! }
}
/*
--- 3697,3753 ----
if (em->em_is_const)
continue;
/*
! * Ignore child members unless they match the rel being sorted.
*/
! if (em->em_is_child &&
! !bms_equal(em->em_relids, relids))
! continue;
!
! sortexpr = em->em_expr;
! exprvars = pull_var_clause((Node *) sortexpr,
! PVC_INCLUDE_AGGREGATES,
! PVC_INCLUDE_PLACEHOLDERS);
! foreach(k, exprvars)
! {
! // if (!tlist_member_ignore_relabel(lfirst(k), tlist))
! if (!tlist_member(lfirst(k), tlist))
! break;
! }
! list_free(exprvars);
! if (!k)
{
pk_datatype = em->em_datatype;
! break; /* found usable expression */
}
}
+ if (!j)
+ elog(ERROR, "could not find pathkey item to sort");
! /*
! * Do we need to insert a Result node?
! */
! if (!adjust_tlist_in_place &&
! !is_projection_capable_plan(lefttree))
{
! /* copy needed so we don't modify input's tlist below */
! tlist = copyObject(tlist);
! lefttree = (Plan *) make_result(root, tlist, NULL,
! lefttree);
! }
! /* Don't bother testing is_projection_capable_plan again */
! adjust_tlist_in_place = true;
! /*
! * Add resjunk entry to input's tlist
! */
! tle = makeTargetEntry(sortexpr,
! list_length(tlist) + 1,
! NULL,
! true);
! tlist = lappend(tlist, tle);
! lefttree->targetlist = tlist; /* just in case NIL before */
}
/*
*************** prepare_sort_from_pathkeys(PlannerInfo *
*** 3775,3793 ****
pathkey->pk_strategy, pk_datatype, pk_datatype,
pathkey->pk_opfamily);
! /*
! * The column might already be selected as a sort key, if the pathkeys
! * contain duplicate entries. (This can happen in scenarios where
! * multiple mergejoinable clauses mention the same var, for example.)
! * So enter it only once in the sort arrays.
! */
! numsortkeys = add_sort_column(tle->resno,
! sortop,
! pathkey->pk_eclass->ec_collation,
! pathkey->pk_nulls_first,
! numsortkeys,
! sortColIdx, sortOperators,
! collations, nullsFirst);
}
Assert(numsortkeys > 0);
--- 3763,3774 ----
pathkey->pk_strategy, pk_datatype, pk_datatype,
pathkey->pk_opfamily);
! /* Add the column to the sort arrays */
! sortColIdx[numsortkeys] = tle->resno;
! sortOperators[numsortkeys] = sortop;
! collations[numsortkeys] = ec->ec_collation;
! nullsFirst[numsortkeys] = pathkey->pk_nulls_first;
! numsortkeys++;
}
Assert(numsortkeys > 0);
*************** prepare_sort_from_pathkeys(PlannerInfo *
*** 3803,3808 ****
--- 3784,3840 ----
}
/*
+ * find_ec_member_for_tle
+ * Locate an EquivalenceClass member matching the given TLE, if any
+ *
+ * Child EC members are ignored unless they match 'relids'.
+ */
+ static EquivalenceMember *
+ find_ec_member_for_tle(EquivalenceClass *ec,
+ TargetEntry *tle,
+ Relids relids)
+ {
+ Expr *tlexpr;
+ ListCell *lc;
+
+ /* We ignore binary-compatible relabeling on both ends */
+ tlexpr = tle->expr;
+ while (tlexpr && IsA(tlexpr, RelabelType))
+ tlexpr = ((RelabelType *) tlexpr)->arg;
+
+ foreach(lc, ec->ec_members)
+ {
+ EquivalenceMember *em = (EquivalenceMember *) lfirst(lc);
+ Expr *emexpr;
+
+ /*
+ * We shouldn't be trying to sort by an equivalence class that
+ * contains a constant, so no need to consider such cases any
+ * further.
+ */
+ if (em->em_is_const)
+ continue;
+
+ /*
+ * Ignore child members unless they match the rel being sorted.
+ */
+ if (em->em_is_child &&
+ !bms_equal(em->em_relids, relids))
+ continue;
+
+ /* Match if same expression (after stripping relabel) */
+ emexpr = em->em_expr;
+ while (emexpr && IsA(emexpr, RelabelType))
+ emexpr = ((RelabelType *) emexpr)->arg;
+
+ if (equal(emexpr, tlexpr))
+ return em;
+ }
+
+ return NULL;
+ }
+
+ /*
* make_sort_from_pathkeys
* Create sort plan to sort according to given pathkeys
*
*************** make_sort_from_pathkeys(PlannerInfo *roo
*** 3823,3828 ****
--- 3855,3862 ----
/* Compute sort column info, and adjust lefttree as needed */
lefttree = prepare_sort_from_pathkeys(root, lefttree, pathkeys,
+ NULL,
+ NULL,
false,
&numsortkeys,
&sortColIdx,
*************** make_sort_from_sortclauses(PlannerInfo *
*** 3854,3862 ****
Oid *collations;
bool *nullsFirst;
! /*
! * We will need at most list_length(sortcls) sort columns; possibly less
! */
numsortkeys = list_length(sortcls);
sortColIdx = (AttrNumber *) palloc(numsortkeys * sizeof(AttrNumber));
sortOperators = (Oid *) palloc(numsortkeys * sizeof(Oid));
--- 3888,3894 ----
Oid *collations;
bool *nullsFirst;
! /* Convert list-ish representation to arrays wanted by executor */
numsortkeys = list_length(sortcls);
sortColIdx = (AttrNumber *) palloc(numsortkeys * sizeof(AttrNumber));
sortOperators = (Oid *) palloc(numsortkeys * sizeof(Oid));
*************** make_sort_from_sortclauses(PlannerInfo *
*** 3864,3890 ****
nullsFirst = (bool *) palloc(numsortkeys * sizeof(bool));
numsortkeys = 0;
-
foreach(l, sortcls)
{
SortGroupClause *sortcl = (SortGroupClause *) lfirst(l);
TargetEntry *tle = get_sortgroupclause_tle(sortcl, sub_tlist);
! /*
! * Check for the possibility of duplicate order-by clauses --- the
! * parser should have removed 'em, but no point in sorting
! * redundantly.
! */
! numsortkeys = add_sort_column(tle->resno, sortcl->sortop,
! exprCollation((Node *) tle->expr),
! sortcl->nulls_first,
! numsortkeys,
! sortColIdx, sortOperators,
! collations, nullsFirst);
}
- Assert(numsortkeys > 0);
-
return make_sort(root, lefttree, numsortkeys,
sortColIdx, sortOperators, collations,
nullsFirst, -1.0);
--- 3896,3913 ----
nullsFirst = (bool *) palloc(numsortkeys * sizeof(bool));
numsortkeys = 0;
foreach(l, sortcls)
{
SortGroupClause *sortcl = (SortGroupClause *) lfirst(l);
TargetEntry *tle = get_sortgroupclause_tle(sortcl, sub_tlist);
! sortColIdx[numsortkeys] = tle->resno;
! sortOperators[numsortkeys] = sortcl->sortop;
! collations[numsortkeys] = exprCollation((Node *) tle->expr);
! nullsFirst[numsortkeys] = sortcl->nulls_first;
! numsortkeys++;
}
return make_sort(root, lefttree, numsortkeys,
sortColIdx, sortOperators, collations,
nullsFirst, -1.0);
*************** make_sort_from_groupcols(PlannerInfo *ro
*** 3910,3916 ****
Plan *lefttree)
{
List *sub_tlist = lefttree->targetlist;
- int grpno = 0;
ListCell *l;
int numsortkeys;
AttrNumber *sortColIdx;
--- 3933,3938 ----
*************** make_sort_from_groupcols(PlannerInfo *ro
*** 3918,3926 ****
Oid *collations;
bool *nullsFirst;
! /*
! * We will need at most list_length(groupcls) sort columns; possibly less
! */
numsortkeys = list_length(groupcls);
sortColIdx = (AttrNumber *) palloc(numsortkeys * sizeof(AttrNumber));
sortOperators = (Oid *) palloc(numsortkeys * sizeof(Oid));
--- 3940,3946 ----
Oid *collations;
bool *nullsFirst;
! /* Convert list-ish representation to arrays wanted by executor */
numsortkeys = list_length(groupcls);
sortColIdx = (AttrNumber *) palloc(numsortkeys * sizeof(AttrNumber));
sortOperators = (Oid *) palloc(numsortkeys * sizeof(Oid));
*************** make_sort_from_groupcols(PlannerInfo *ro
*** 3928,3955 ****
nullsFirst = (bool *) palloc(numsortkeys * sizeof(bool));
numsortkeys = 0;
-
foreach(l, groupcls)
{
SortGroupClause *grpcl = (SortGroupClause *) lfirst(l);
! TargetEntry *tle = get_tle_by_resno(sub_tlist, grpColIdx[grpno]);
! /*
! * Check for the possibility of duplicate group-by clauses --- the
! * parser should have removed 'em, but no point in sorting
! * redundantly.
! */
! numsortkeys = add_sort_column(tle->resno, grpcl->sortop,
! exprCollation((Node *) tle->expr),
! grpcl->nulls_first,
! numsortkeys,
! sortColIdx, sortOperators,
! collations, nullsFirst);
! grpno++;
}
- Assert(numsortkeys > 0);
-
return make_sort(root, lefttree, numsortkeys,
sortColIdx, sortOperators, collations,
nullsFirst, -1.0);
--- 3948,3965 ----
nullsFirst = (bool *) palloc(numsortkeys * sizeof(bool));
numsortkeys = 0;
foreach(l, groupcls)
{
SortGroupClause *grpcl = (SortGroupClause *) lfirst(l);
! TargetEntry *tle = get_tle_by_resno(sub_tlist, grpColIdx[numsortkeys]);
! sortColIdx[numsortkeys] = tle->resno;
! sortOperators[numsortkeys] = grpcl->sortop;
! collations[numsortkeys] = exprCollation((Node *) tle->expr);
! nullsFirst[numsortkeys] = grpcl->nulls_first;
! numsortkeys++;
}
return make_sort(root, lefttree, numsortkeys,
sortColIdx, sortOperators, collations,
nullsFirst, -1.0);
diff --git a/src/backend/optimizer/plan/planagg.c b/src/backend/optimizer/plan/planagg.c
index 1e2284a9306c17f0bcaa303f1fa645a712ab5b7c..7e2c6d2c31f269a1deaaabe1bf0d0930150ebe18 100644
*** a/src/backend/optimizer/plan/planagg.c
--- b/src/backend/optimizer/plan/planagg.c
*************** preprocess_minmax_aggregates(PlannerInfo
*** 99,107 ****
* We also restrict the query to reference exactly one table, since join
* conditions can't be handled reasonably. (We could perhaps handle a
* query containing cartesian-product joins, but it hardly seems worth the
! * trouble.) However, the single real table could be buried in several
! * levels of FromExpr due to subqueries. Note the single table could be
! * an inheritance parent, too.
*/
jtnode = parse->jointree;
while (IsA(jtnode, FromExpr))
--- 99,108 ----
* We also restrict the query to reference exactly one table, since join
* conditions can't be handled reasonably. (We could perhaps handle a
* query containing cartesian-product joins, but it hardly seems worth the
! * trouble.) However, the single table could be buried in several levels
! * of FromExpr due to subqueries. Note the "single" table could be an
! * inheritance parent, too, including the case of a UNION ALL subquery
! * that's been flattened to an appendrel.
*/
jtnode = parse->jointree;
while (IsA(jtnode, FromExpr))
*************** preprocess_minmax_aggregates(PlannerInfo
*** 114,120 ****
return;
rtr = (RangeTblRef *) jtnode;
rte = planner_rt_fetch(rtr->rtindex, root);
! if (rte->rtekind != RTE_RELATION)
return;
/*
--- 115,125 ----
return;
rtr = (RangeTblRef *) jtnode;
rte = planner_rt_fetch(rtr->rtindex, root);
! if (rte->rtekind == RTE_RELATION)
! /* ordinary relation, ok */ ;
! else if (rte->rtekind == RTE_SUBQUERY && rte->inh)
! /* flattened UNION ALL subquery, ok */ ;
! else
return;
/*
diff --git a/src/backend/optimizer/plan/planmain.c b/src/backend/optimizer/plan/planmain.c
index 68968d9655a43f5b33e578406cc6a72e1995067d..c439e9652c1b2be8456eac2a382cc8b02b4699f4 100644
*** a/src/backend/optimizer/plan/planmain.c
--- b/src/backend/optimizer/plan/planmain.c
*************** query_planner(PlannerInfo *root, List *t
*** 176,182 ****
*/
build_base_rel_tlists(root, tlist);
! find_placeholders_in_query(root);
joinlist = deconstruct_jointree(root);
--- 176,182 ----
*/
build_base_rel_tlists(root, tlist);
! find_placeholders_in_jointree(root);
joinlist = deconstruct_jointree(root);
diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c
index 59a52687f9dfc082b3b01321fe3eada7997498a0..2fd795b45929014257930edd4af620e7e8d7a5a7 100644
*** a/src/backend/optimizer/prep/prepjointree.c
--- b/src/backend/optimizer/prep/prepjointree.c
*************** pull_up_simple_subquery(PlannerInfo *roo
*** 866,880 ****
parse->havingQual = pullup_replace_vars(parse->havingQual, &rvcontext);
/*
! * Replace references in the translated_vars lists of appendrels, too.
! * We do it this way because we must preserve the AppendRelInfo structs.
*/
foreach(lc, root->append_rel_list)
{
AppendRelInfo *appinfo = (AppendRelInfo *) lfirst(lc);
appinfo->translated_vars = (List *)
pullup_replace_vars((Node *) appinfo->translated_vars, &rvcontext);
}
/*
--- 866,887 ----
parse->havingQual = pullup_replace_vars(parse->havingQual, &rvcontext);
/*
! * Replace references in the translated_vars lists of appendrels. When
! * pulling up an appendrel member, we do not need PHVs in the list of the
! * parent appendrel --- there isn't any outer join between. Elsewhere, use
! * PHVs for safety. (This analysis could be made tighter but it seems
! * unlikely to be worth much trouble.)
*/
foreach(lc, root->append_rel_list)
{
AppendRelInfo *appinfo = (AppendRelInfo *) lfirst(lc);
+ bool save_need_phvs = rvcontext.need_phvs;
+ if (appinfo == containing_appendrel)
+ rvcontext.need_phvs = false;
appinfo->translated_vars = (List *)
pullup_replace_vars((Node *) appinfo->translated_vars, &rvcontext);
+ rvcontext.need_phvs = save_need_phvs;
}
/*
*************** pullup_replace_vars_callback(Var *var,
*** 1482,1512 ****
if (newnode && IsA(newnode, Var) &&
((Var *) newnode)->varlevelsup == 0)
{
! /*
! * Simple Vars normally escape being wrapped. However, in
! * wrap_non_vars mode (ie, we are dealing with an appendrel
! * member), we must ensure that each tlist entry expands to a
! * distinct expression, else we may have problems with
! * improperly placing identical entries into different
! * EquivalenceClasses. Therefore, we wrap a Var in a
! * PlaceHolderVar if it duplicates any earlier entry in the
! * tlist (ie, we've got "SELECT x, x, ..."). Since each PHV
! * is distinct, this fixes the ambiguity. We can use
! * tlist_member to detect whether there's an earlier
! * duplicate.
! */
! wrap = (rcon->wrap_non_vars &&
! tlist_member(newnode, rcon->targetlist) != tle);
}
else if (newnode && IsA(newnode, PlaceHolderVar) &&
((PlaceHolderVar *) newnode)->phlevelsup == 0)
{
! /*
! * No need to directly wrap a PlaceHolderVar with another one,
! * either, unless we need to prevent duplication.
! */
! wrap = (rcon->wrap_non_vars &&
! tlist_member(newnode, rcon->targetlist) != tle);
}
else if (rcon->wrap_non_vars)
{
--- 1489,1502 ----
if (newnode && IsA(newnode, Var) &&
((Var *) newnode)->varlevelsup == 0)
{
! /* Simple Vars always escape being wrapped */
! wrap = false;
}
else if (newnode && IsA(newnode, PlaceHolderVar) &&
((PlaceHolderVar *) newnode)->phlevelsup == 0)
{
! /* No need to wrap a PlaceHolderVar with another one, either */
! wrap = false;
}
else if (rcon->wrap_non_vars)
{
diff --git a/src/backend/optimizer/util/placeholder.c b/src/backend/optimizer/util/placeholder.c
index 44a41e3e9b89a811f779efbfedf8b1dfab13b503..93f1c2cdfa4e1aadec221fa7ef2ccc833709daf8 100644
*** a/src/backend/optimizer/util/placeholder.c
--- b/src/backend/optimizer/util/placeholder.c
*************** find_placeholder_info(PlannerInfo *root,
*** 104,144 ****
}
/*
! * find_placeholders_in_query
! * Search the query for PlaceHolderVars, and build PlaceHolderInfos
! *
! * We need to examine the jointree, but not the targetlist, because
! * build_base_rel_tlists() will already have made entries for any PHVs
! * in the targetlist.
*
! * We also need to search for PHVs in AppendRelInfo translated_vars
! * lists. In most cases, translated_vars entries aren't directly referenced
! * elsewhere, but we need to create PlaceHolderInfo entries for them to
! * support set_rel_width() calculations for the appendrel child relations.
*/
void
! find_placeholders_in_query(PlannerInfo *root)
{
/* We need do nothing if the query contains no PlaceHolderVars */
if (root->glob->lastPHId != 0)
{
! /* Recursively search the jointree */
Assert(root->parse->jointree != NULL &&
IsA(root->parse->jointree, FromExpr));
(void) find_placeholders_recurse(root, (Node *) root->parse->jointree);
-
- /*
- * Also search the append_rel_list for translated vars that are PHVs.
- * Barring finding them elsewhere in the query, they do not need any
- * ph_may_need bits, only to be present in the PlaceHolderInfo list.
- */
- mark_placeholders_in_expr(root, (Node *) root->append_rel_list, NULL);
}
}
/*
* find_placeholders_recurse
! * One recursion level of jointree search for find_placeholders_in_query.
*
* jtnode is the current jointree node to examine.
*
--- 104,131 ----
}
/*
! * find_placeholders_in_jointree
! * Search the jointree for PlaceHolderVars, and build PlaceHolderInfos
*
! * We don't need to look at the targetlist because build_base_rel_tlists()
! * will already have made entries for any PHVs in the tlist.
*/
void
! find_placeholders_in_jointree(PlannerInfo *root)
{
/* We need do nothing if the query contains no PlaceHolderVars */
if (root->glob->lastPHId != 0)
{
! /* Start recursion at top of jointree */
Assert(root->parse->jointree != NULL &&
IsA(root->parse->jointree, FromExpr));
(void) find_placeholders_recurse(root, (Node *) root->parse->jointree);
}
}
/*
* find_placeholders_recurse
! * One recursion level of find_placeholders_in_jointree.
*
* jtnode is the current jointree node to examine.
*
diff --git a/src/include/nodes/relation.h b/src/include/nodes/relation.h
index 8616223f24a8cbe5424b89599b331538db05dd76..d901a85e7ac680d89d8e71569da73d901991d771 100644
*** a/src/include/nodes/relation.h
--- b/src/include/nodes/relation.h
*************** typedef struct EquivalenceClass
*** 572,583 ****
* EquivalenceMember - one member expression of an EquivalenceClass
*
* em_is_child signifies that this element was built by transposing a member
! * for an inheritance parent relation to represent the corresponding expression
! * on an inheritance child. These elements are used for constructing
! * inner-indexscan paths for the child relation (other types of join are
! * driven from transposed joininfo-list entries) and for constructing
! * MergeAppend paths for the whole inheritance tree. Note that the EC's
! * ec_relids field does NOT include the child relation.
*
* em_datatype is usually the same as exprType(em_expr), but can be
* different when dealing with a binary-compatible opfamily; in particular
--- 572,589 ----
* EquivalenceMember - one member expression of an EquivalenceClass
*
* em_is_child signifies that this element was built by transposing a member
! * for an appendrel parent relation to represent the corresponding expression
! * for an appendrel child. These members are used for determining the
! * pathkeys of scans on the child relation and for explicitly sorting the
! * child when necessary to build a MergeAppend path for the whole appendrel
! * tree. An em_is_child member has no impact on the properties of the EC as a
! * whole; in particular the EC's ec_relids field does NOT include the child
! * relation. An em_is_child member should never be marked em_is_const nor
! * cause ec_has_const or ec_has_volatile to be set, either. Thus, em_is_child
! * members are not really full-fledged members of the EC, but just reflections
! * or doppelgangers of real members. Most operations on EquivalenceClasses
! * should ignore em_is_child members, and those that don't should test
! * em_relids to make sure they only consider relevant members.
*
* em_datatype is usually the same as exprType(em_expr), but can be
* different when dealing with a binary-compatible opfamily; in particular
diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h
index 082b54e120f3c4e6ae0f91cacc24bd1bcda98323..caaa7c24a1cf21230e44a5db83c34c8d9d7608e3 100644
*** a/src/include/optimizer/paths.h
--- b/src/include/optimizer/paths.h
*************** extern EquivalenceClass *get_eclass_for_
*** 110,115 ****
--- 110,116 ----
Oid opcintype,
Oid collation,
Index sortref,
+ Relids rel,
bool create_it);
extern void generate_base_implied_equalities(PlannerInfo *root);
extern List *generate_join_implied_equalities(PlannerInfo *root,
diff --git a/src/include/optimizer/placeholder.h b/src/include/optimizer/placeholder.h
index 2d83fb1578fc007d6b16607aef10615c2db78acb..b8ac25367e54366560465ad045b02622363751c7 100644
*** a/src/include/optimizer/placeholder.h
--- b/src/include/optimizer/placeholder.h
*************** extern PlaceHolderVar *make_placeholder_
*** 21,27 ****
Relids phrels);
extern PlaceHolderInfo *find_placeholder_info(PlannerInfo *root,
PlaceHolderVar *phv, bool create_new_ph);
! extern void find_placeholders_in_query(PlannerInfo *root);
extern void mark_placeholder_maybe_needed(PlannerInfo *root,
PlaceHolderInfo *phinfo, Relids relids);
extern void update_placeholder_eval_levels(PlannerInfo *root,
--- 21,27 ----
Relids phrels);
extern PlaceHolderInfo *find_placeholder_info(PlannerInfo *root,
PlaceHolderVar *phv, bool create_new_ph);
! extern void find_placeholders_in_jointree(PlannerInfo *root);
extern void mark_placeholder_maybe_needed(PlannerInfo *root,
PlaceHolderInfo *phinfo, Relids relids);
extern void update_placeholder_eval_levels(PlannerInfo *root,
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index 6ef1cbdec7972a1d32a6fe2d1c6f1a227eb84f1c..d8f20e8ce2ce1d89540b806c8861e5a3ee1a3175 100644
*** a/src/test/regress/expected/inherit.out
--- b/src/test/regress/expected/inherit.out
*************** drop cascades to table matest2
*** 1067,1077 ****
drop cascades to table matest3
--
-- Test merge-append for UNION ALL append relations
- -- Check handling of duplicated, constant, or volatile targetlist items
--
set enable_seqscan = off;
set enable_indexscan = on;
set enable_bitmapscan = off;
explain (costs off)
SELECT thousand, tenthous FROM tenk1
UNION ALL
--- 1067,1077 ----
drop cascades to table matest3
--
-- Test merge-append for UNION ALL append relations
--
set enable_seqscan = off;
set enable_indexscan = on;
set enable_bitmapscan = off;
+ -- Check handling of duplicated, constant, or volatile targetlist items
explain (costs off)
SELECT thousand, tenthous FROM tenk1
UNION ALL
*************** ORDER BY thousand, tenthous;
*** 1120,1125 ****
--- 1120,1180 ----
-> Index Only Scan using tenk1_thous_tenthous on tenk1
(7 rows)
+ -- Check min/max aggregate optimization
+ explain (costs off)
+ SELECT min(x) FROM
+ (SELECT unique1 AS x FROM tenk1 a
+ UNION ALL
+ SELECT unique2 AS x FROM tenk1 b) s;
+ QUERY PLAN
+ --------------------------------------------------------------------
+ Result
+ InitPlan 1 (returns $0)
+ -> Limit
+ -> Merge Append
+ Sort Key: a.unique1
+ -> Index Only Scan using tenk1_unique1 on tenk1 a
+ Index Cond: (unique1 IS NOT NULL)
+ -> Index Only Scan using tenk1_unique2 on tenk1 b
+ Index Cond: (unique2 IS NOT NULL)
+ (9 rows)
+
+ explain (costs off)
+ SELECT min(y) FROM
+ (SELECT unique1 AS x, unique1 AS y FROM tenk1 a
+ UNION ALL
+ SELECT unique2 AS x, unique2 AS y FROM tenk1 b) s;
+ QUERY PLAN
+ --------------------------------------------------------------------
+ Result
+ InitPlan 1 (returns $0)
+ -> Limit
+ -> Merge Append
+ Sort Key: a.unique1
+ -> Index Only Scan using tenk1_unique1 on tenk1 a
+ Index Cond: (unique1 IS NOT NULL)
+ -> Index Only Scan using tenk1_unique2 on tenk1 b
+ Index Cond: (unique2 IS NOT NULL)
+ (9 rows)
+
+ -- XXX planner doesn't recognize that index on unique2 is sufficiently sorted
+ explain (costs off)
+ SELECT x, y FROM
+ (SELECT thousand AS x, tenthous AS y FROM tenk1 a
+ UNION ALL
+ SELECT unique2 AS x, unique2 AS y FROM tenk1 b) s
+ ORDER BY x, y;
+ QUERY PLAN
+ -------------------------------------------------------------------
+ Result
+ -> Merge Append
+ Sort Key: a.thousand, a.tenthous
+ -> Index Only Scan using tenk1_thous_tenthous on tenk1 a
+ -> Sort
+ Sort Key: b.unique2, b.unique2
+ -> Index Only Scan using tenk1_unique2 on tenk1 b
+ (7 rows)
+
reset enable_seqscan;
reset enable_indexscan;
reset enable_bitmapscan;
diff --git a/src/test/regress/expected/union.out b/src/test/regress/expected/union.out
index 2913f3d8f378796b711f78404dcc90620f5eadf2..405c5847cd1b5cbdddb92b08b30f13b5feac869d 100644
*** a/src/test/regress/expected/union.out
--- b/src/test/regress/expected/union.out
*************** explain (costs off)
*** 503,505 ****
--- 503,519 ----
reset enable_seqscan;
reset enable_indexscan;
reset enable_bitmapscan;
+ -- Test constraint exclusion of UNION ALL subqueries
+ explain (costs off)
+ SELECT * FROM
+ (SELECT 1 AS t, * FROM tenk1 a
+ UNION ALL
+ SELECT 2 AS t, * FROM tenk1 b) c
+ WHERE t = 2;
+ QUERY PLAN
+ ---------------------------------
+ Result
+ -> Append
+ -> Seq Scan on tenk1 b
+ (3 rows)
+
diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql
index 6fdbd18c25173dab58c069b8a93a7bf86e7a716e..eec8192928d92e5b6c19586cdbd0103e58a13875 100644
*** a/src/test/regress/sql/inherit.sql
--- b/src/test/regress/sql/inherit.sql
*************** drop table matest0 cascade;
*** 326,338 ****
--
-- Test merge-append for UNION ALL append relations
- -- Check handling of duplicated, constant, or volatile targetlist items
--
set enable_seqscan = off;
set enable_indexscan = on;
set enable_bitmapscan = off;
explain (costs off)
SELECT thousand, tenthous FROM tenk1
UNION ALL
--- 326,338 ----
--
-- Test merge-append for UNION ALL append relations
--
set enable_seqscan = off;
set enable_indexscan = on;
set enable_bitmapscan = off;
+ -- Check handling of duplicated, constant, or volatile targetlist items
explain (costs off)
SELECT thousand, tenthous FROM tenk1
UNION ALL
*************** UNION ALL
*** 351,356 ****
--- 351,377 ----
SELECT thousand, random()::integer FROM tenk1
ORDER BY thousand, tenthous;
+ -- Check min/max aggregate optimization
+ explain (costs off)
+ SELECT min(x) FROM
+ (SELECT unique1 AS x FROM tenk1 a
+ UNION ALL
+ SELECT unique2 AS x FROM tenk1 b) s;
+
+ explain (costs off)
+ SELECT min(y) FROM
+ (SELECT unique1 AS x, unique1 AS y FROM tenk1 a
+ UNION ALL
+ SELECT unique2 AS x, unique2 AS y FROM tenk1 b) s;
+
+ -- XXX planner doesn't recognize that index on unique2 is sufficiently sorted
+ explain (costs off)
+ SELECT x, y FROM
+ (SELECT thousand AS x, tenthous AS y FROM tenk1 a
+ UNION ALL
+ SELECT unique2 AS x, unique2 AS y FROM tenk1 b) s
+ ORDER BY x, y;
+
reset enable_seqscan;
reset enable_indexscan;
reset enable_bitmapscan;
diff --git a/src/test/regress/sql/union.sql b/src/test/regress/sql/union.sql
index b5c2128f3f3826f49cd1a7287067dbc7ff275ed4..752ae470f0d1b38a1aeda87ae31d6b63e3e5cb3c 100644
*** a/src/test/regress/sql/union.sql
--- b/src/test/regress/sql/union.sql
*************** explain (costs off)
*** 199,201 ****
--- 199,209 ----
reset enable_seqscan;
reset enable_indexscan;
reset enable_bitmapscan;
+
+ -- Test constraint exclusion of UNION ALL subqueries
+ explain (costs off)
+ SELECT * FROM
+ (SELECT 1 AS t, * FROM tenk1 a
+ UNION ALL
+ SELECT 2 AS t, * FROM tenk1 b) c
+ WHERE t = 2;
pgsql-hackers by date: