Re: Clamping reulst row number of joins.
| От | Tom Lane |
|---|---|
| Тема | Re: Clamping reulst row number of joins. |
| Дата | |
| Msg-id | 21519.1425857436@sss.pgh.pa.us обсуждение исходный текст |
| Ответ на | Re: Clamping reulst row number of joins. (Tom Lane <tgl@sss.pgh.pa.us>) |
| Ответы |
Re: Clamping reulst row number of joins.
(Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>)
|
| Список | pgsql-hackers |
I wrote:
>> Stephen Frost <sfrost@snowman.net> writes:
>>> I've certainly seen and used values() constructs in joins for a variety
>>> of reasons and I do think it'd be worthwhile for the planner to know how
>>> to pull up a VALUES construct.
> I spent a bit of time looking at this, and realized that the blocker
> is the same as the reason why we don't pull up sub-selects with empty
> rangetables ("SELECT expression(s)"). Namely, that the upper query's
> jointree can't handle a null subtree. (This is not only a matter of
> the jointree data structure, but the fact that the whole planner
> identifies relations by bitmapsets of RTE indexes, and subtrees with
> empty RTE sets couldn't be told apart.)
> We could probably fix both cases for order-of-a-hundred lines of new code
> in prepjointree. The plan I'm thinking about is to allow such vacuous
> subquery jointrees to be pulled up, but only if they are in a place in
> the upper query's jointree where it's okay to delete the subtree. This
> would basically be two cases: (1) the immediate parent is a FromExpr that
> would have at least one remaining child, or (2) the immediate parent is
> an INNER JOIN whose other child isn't also being deleted (so that we can
> convert the JoinExpr to a nonempty FromExpr, or just use the other child
> as-is if the JoinExpr has no quals).
Here's a draft patch along those lines. Unsurprisingly, it changes the
plans generated for a number of regression-test queries. In most cases
I felt it desirable to force the old plan shape to be retained (by
inserting "offset 0" or equivalent) because the test was trying to test
proper generation of a query plan of that shape. I did add a couple
cases where the optimization was allowed to go through.
The patch is a bit bigger than I'd hoped (a net of about 330 lines added
to prepjointree.c), but it's not hugely ugly, and it doesn't add any
meaningful overhead in cases where no optimization happens. Barring
objections I will commit this.
regards, tom lane
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 775f482..0d3db71 100644
*** a/src/backend/nodes/outfuncs.c
--- b/src/backend/nodes/outfuncs.c
*************** _outPlannerInfo(StringInfo str, const Pl
*** 1762,1767 ****
--- 1762,1768 ----
WRITE_BOOL_FIELD(hasInheritedTarget);
WRITE_BOOL_FIELD(hasJoinRTEs);
WRITE_BOOL_FIELD(hasLateralRTEs);
+ WRITE_BOOL_FIELD(hasDeletedRTEs);
WRITE_BOOL_FIELD(hasHavingQual);
WRITE_BOOL_FIELD(hasPseudoConstantQuals);
WRITE_BOOL_FIELD(hasRecursion);
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index b02a107..88b91f1 100644
*** a/src/backend/optimizer/plan/planner.c
--- b/src/backend/optimizer/plan/planner.c
*************** subquery_planner(PlannerGlobal *glob, Qu
*** 352,359 ****
* Check to see if any subqueries in the jointree can be merged into this
* query.
*/
! parse->jointree = (FromExpr *)
! pull_up_subqueries(root, (Node *) parse->jointree);
/*
* If this is a simple UNION ALL query, flatten it into an appendrel. We
--- 352,358 ----
* Check to see if any subqueries in the jointree can be merged into this
* query.
*/
! pull_up_subqueries(root);
/*
* If this is a simple UNION ALL query, flatten it into an appendrel. We
diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c
index 8a0199b..50acfe4 100644
*** a/src/backend/optimizer/prep/prepjointree.c
--- b/src/backend/optimizer/prep/prepjointree.c
*************** static Node *pull_up_sublinks_qual_recur
*** 65,76 ****
static Node *pull_up_subqueries_recurse(PlannerInfo *root, Node *jtnode,
JoinExpr *lowest_outer_join,
JoinExpr *lowest_nulling_outer_join,
! AppendRelInfo *containing_appendrel);
static Node *pull_up_simple_subquery(PlannerInfo *root, Node *jtnode,
RangeTblEntry *rte,
JoinExpr *lowest_outer_join,
JoinExpr *lowest_nulling_outer_join,
! AppendRelInfo *containing_appendrel);
static Node *pull_up_simple_union_all(PlannerInfo *root, Node *jtnode,
RangeTblEntry *rte);
static void pull_up_union_leaf_queries(Node *setOp, PlannerInfo *root,
--- 65,78 ----
static Node *pull_up_subqueries_recurse(PlannerInfo *root, Node *jtnode,
JoinExpr *lowest_outer_join,
JoinExpr *lowest_nulling_outer_join,
! AppendRelInfo *containing_appendrel,
! bool deletion_ok);
static Node *pull_up_simple_subquery(PlannerInfo *root, Node *jtnode,
RangeTblEntry *rte,
JoinExpr *lowest_outer_join,
JoinExpr *lowest_nulling_outer_join,
! AppendRelInfo *containing_appendrel,
! bool deletion_ok);
static Node *pull_up_simple_union_all(PlannerInfo *root, Node *jtnode,
RangeTblEntry *rte);
static void pull_up_union_leaf_queries(Node *setOp, PlannerInfo *root,
*************** static void pull_up_union_leaf_queries(N
*** 79,85 ****
static void make_setop_translation_list(Query *query, Index newvarno,
List **translated_vars);
static bool is_simple_subquery(Query *subquery, RangeTblEntry *rte,
! JoinExpr *lowest_outer_join);
static bool is_simple_union_all(Query *subquery);
static bool is_simple_union_all_recurse(Node *setOp, Query *setOpQuery,
List *colTypes);
--- 81,92 ----
static void make_setop_translation_list(Query *query, Index newvarno,
List **translated_vars);
static bool is_simple_subquery(Query *subquery, RangeTblEntry *rte,
! JoinExpr *lowest_outer_join,
! bool deletion_ok);
! static Node *pull_up_simple_values(PlannerInfo *root, Node *jtnode,
! RangeTblEntry *rte);
! static bool is_simple_values(PlannerInfo *root, RangeTblEntry *rte,
! bool deletion_ok);
static bool is_simple_union_all(Query *subquery);
static bool is_simple_union_all_recurse(Node *setOp, Query *setOpQuery,
List *colTypes);
*************** static Node *pullup_replace_vars_callbac
*** 95,100 ****
--- 102,108 ----
replace_rte_variables_context *context);
static Query *pullup_replace_vars_subquery(Query *query,
pullup_replace_vars_context *context);
+ static Node *pull_up_subqueries_cleanup(Node *jtnode);
static reduce_outer_joins_state *reduce_outer_joins_pass1(Node *jtnode);
static void reduce_outer_joins_pass2(Node *jtnode,
reduce_outer_joins_state *state,
*************** inline_set_returning_functions(PlannerIn
*** 593,612 ****
* grouping/aggregation then we can merge it into the parent's jointree.
* Also, subqueries that are simple UNION ALL structures can be
* converted into "append relations".
- *
- * This recursively processes the jointree and returns a modified jointree.
*/
! Node *
! pull_up_subqueries(PlannerInfo *root, Node *jtnode)
{
! /* Start off with no containing join nor appendrel */
! return pull_up_subqueries_recurse(root, jtnode, NULL, NULL, NULL);
}
/*
* pull_up_subqueries_recurse
* Recursive guts of pull_up_subqueries.
*
* If this jointree node is within either side of an outer join, then
* lowest_outer_join references the lowest such JoinExpr node; otherwise
* it is NULL. We use this to constrain the effects of LATERAL subqueries.
--- 601,633 ----
* grouping/aggregation then we can merge it into the parent's jointree.
* Also, subqueries that are simple UNION ALL structures can be
* converted into "append relations".
*/
! void
! pull_up_subqueries(PlannerInfo *root)
{
! /* Top level of jointree must always be a FromExpr */
! Assert(IsA(root->parse->jointree, FromExpr));
! /* Reset flag saying we need a deletion cleanup pass */
! root->hasDeletedRTEs = false;
! /* Recursion starts with no containing join nor appendrel */
! root->parse->jointree = (FromExpr *)
! pull_up_subqueries_recurse(root, (Node *) root->parse->jointree,
! NULL, NULL, NULL, false);
! /* Apply cleanup phase if necessary */
! if (root->hasDeletedRTEs)
! root->parse->jointree = (FromExpr *)
! pull_up_subqueries_cleanup((Node *) root->parse->jointree);
! Assert(IsA(root->parse->jointree, FromExpr));
}
/*
* pull_up_subqueries_recurse
* Recursive guts of pull_up_subqueries.
*
+ * This recursively processes the jointree and returns a modified jointree.
+ * Or, if it's valid to drop the current node from the jointree completely,
+ * it returns NULL.
+ *
* If this jointree node is within either side of an outer join, then
* lowest_outer_join references the lowest such JoinExpr node; otherwise
* it is NULL. We use this to constrain the effects of LATERAL subqueries.
*************** pull_up_subqueries(PlannerInfo *root, No
*** 622,649 ****
* This forces use of the PlaceHolderVar mechanism for all non-Var targetlist
* items, and puts some additional restrictions on what can be pulled up.
*
* A tricky aspect of this code is that if we pull up a subquery we have
* to replace Vars that reference the subquery's outputs throughout the
* parent query, including quals attached to jointree nodes above the one
* we are currently processing! We handle this by being careful not to
! * change the jointree structure while recursing: no nodes other than
! * subquery RangeTblRef entries will be replaced. Also, we can't turn
! * pullup_replace_vars loose on the whole jointree, because it'll return a
! * mutated copy of the tree; we have to invoke it just on the quals, instead.
! * This behavior is what makes it reasonable to pass lowest_outer_join and
! * lowest_nulling_outer_join as pointers rather than some more-indirect way
! * of identifying the lowest OJs. Likewise, we don't replace append_rel_list
! * members but only their substructure, so the containing_appendrel reference
! * is safe to use.
*/
static Node *
pull_up_subqueries_recurse(PlannerInfo *root, Node *jtnode,
JoinExpr *lowest_outer_join,
JoinExpr *lowest_nulling_outer_join,
! AppendRelInfo *containing_appendrel)
{
! if (jtnode == NULL)
! return NULL;
if (IsA(jtnode, RangeTblRef))
{
int varno = ((RangeTblRef *) jtnode)->rtindex;
--- 643,681 ----
* This forces use of the PlaceHolderVar mechanism for all non-Var targetlist
* items, and puts some additional restrictions on what can be pulled up.
*
+ * deletion_ok is TRUE if the caller can cope with us returning NULL for a
+ * deletable leaf node (for example, a VALUES RTE that could be pulled up).
+ * If it's FALSE, we'll avoid pullup in such cases.
+ *
* A tricky aspect of this code is that if we pull up a subquery we have
* to replace Vars that reference the subquery's outputs throughout the
* parent query, including quals attached to jointree nodes above the one
* we are currently processing! We handle this by being careful not to
! * change the jointree structure while recursing: no nodes other than leaf
! * RangeTblRef entries and entirely-empty FromExprs will be replaced or
! * deleted. Also, we can't turn pullup_replace_vars loose on the whole
! * jointree, because it'll return a mutated copy of the tree; we have to
! * invoke it just on the quals, instead. This behavior is what makes it
! * reasonable to pass lowest_outer_join and lowest_nulling_outer_join as
! * pointers rather than some more-indirect way of identifying the lowest
! * OJs. Likewise, we don't replace append_rel_list members but only their
! * substructure, so the containing_appendrel reference is safe to use.
! *
! * Because of the rule that no jointree nodes with substructure can be
! * replaced, we cannot fully handle the case of deleting nodes from the tree:
! * when we delete one child of a JoinExpr, we need to replace the JoinExpr
! * with a FromExpr, and that can't happen here. Instead, we set the
! * root->hasDeletedRTEs flag, which tells pull_up_subqueries() that an
! * additional pass over the tree is needed to clean up.
*/
static Node *
pull_up_subqueries_recurse(PlannerInfo *root, Node *jtnode,
JoinExpr *lowest_outer_join,
JoinExpr *lowest_nulling_outer_join,
! AppendRelInfo *containing_appendrel,
! bool deletion_ok)
{
! Assert(jtnode != NULL);
if (IsA(jtnode, RangeTblRef))
{
int varno = ((RangeTblRef *) jtnode)->rtindex;
*************** pull_up_subqueries_recurse(PlannerInfo *
*** 657,669 ****
* unless is_safe_append_member says so.
*/
if (rte->rtekind == RTE_SUBQUERY &&
! is_simple_subquery(rte->subquery, rte, lowest_outer_join) &&
(containing_appendrel == NULL ||
is_safe_append_member(rte->subquery)))
return pull_up_simple_subquery(root, jtnode, rte,
lowest_outer_join,
lowest_nulling_outer_join,
! containing_appendrel);
/*
* Alternatively, is it a simple UNION ALL subquery? If so, flatten
--- 689,703 ----
* unless is_safe_append_member says so.
*/
if (rte->rtekind == RTE_SUBQUERY &&
! is_simple_subquery(rte->subquery, rte,
! lowest_outer_join, deletion_ok) &&
(containing_appendrel == NULL ||
is_safe_append_member(rte->subquery)))
return pull_up_simple_subquery(root, jtnode, rte,
lowest_outer_join,
lowest_nulling_outer_join,
! containing_appendrel,
! deletion_ok);
/*
* Alternatively, is it a simple UNION ALL subquery? If so, flatten
*************** pull_up_subqueries_recurse(PlannerInfo *
*** 678,696 ****
is_simple_union_all(rte->subquery))
return pull_up_simple_union_all(root, jtnode, rte);
/* Otherwise, do nothing at this node. */
}
else if (IsA(jtnode, FromExpr))
{
FromExpr *f = (FromExpr *) jtnode;
ListCell *l;
Assert(containing_appendrel == NULL);
foreach(l, f->fromlist)
lfirst(l) = pull_up_subqueries_recurse(root, lfirst(l),
lowest_outer_join,
lowest_nulling_outer_join,
! NULL);
}
else if (IsA(jtnode, JoinExpr))
{
--- 712,779 ----
is_simple_union_all(rte->subquery))
return pull_up_simple_union_all(root, jtnode, rte);
+ /*
+ * Or perhaps it's a simple VALUES RTE?
+ *
+ * We don't allow VALUES pullup below an outer join nor into an
+ * appendrel (such cases are impossible anyway at the moment).
+ */
+ if (rte->rtekind == RTE_VALUES &&
+ lowest_outer_join == NULL &&
+ containing_appendrel == NULL &&
+ is_simple_values(root, rte, deletion_ok))
+ return pull_up_simple_values(root, jtnode, rte);
+
/* Otherwise, do nothing at this node. */
}
else if (IsA(jtnode, FromExpr))
{
FromExpr *f = (FromExpr *) jtnode;
+ bool have_undeleted_child = false;
ListCell *l;
Assert(containing_appendrel == NULL);
+
+ /*
+ * If the FromExpr has quals, it's not deletable even if its parent
+ * would allow deletion.
+ */
+ if (f->quals)
+ deletion_ok = false;
+
foreach(l, f->fromlist)
+ {
+ /*
+ * In a non-deletable FromExpr, we can allow deletion of child
+ * nodes so long as at least one child remains; so it's okay
+ * either if any previous child survives, or if there's more to
+ * come. If all children are deletable in themselves, we'll force
+ * the last one to remain unflattened.
+ *
+ * As a separate matter, we can allow deletion of all children of
+ * the top-level FromExpr in a query, since that's a special case
+ * anyway.
+ */
+ bool sub_deletion_ok = (deletion_ok ||
+ have_undeleted_child ||
+ lnext(l) != NULL ||
+ f == root->parse->jointree);
+
lfirst(l) = pull_up_subqueries_recurse(root, lfirst(l),
lowest_outer_join,
lowest_nulling_outer_join,
! NULL,
! sub_deletion_ok);
! if (lfirst(l) != NULL)
! have_undeleted_child = true;
! }
!
! if (deletion_ok && !have_undeleted_child)
! {
! /* OK to delete this FromExpr entirely */
! root->hasDeletedRTEs = true; /* probably is set already */
! return NULL;
! }
}
else if (IsA(jtnode, JoinExpr))
{
*************** pull_up_subqueries_recurse(PlannerInfo *
*** 701,714 ****
switch (j->jointype)
{
case JOIN_INNER:
j->larg = pull_up_subqueries_recurse(root, j->larg,
lowest_outer_join,
lowest_nulling_outer_join,
! NULL);
j->rarg = pull_up_subqueries_recurse(root, j->rarg,
lowest_outer_join,
lowest_nulling_outer_join,
! NULL);
break;
case JOIN_LEFT:
case JOIN_SEMI:
--- 784,805 ----
switch (j->jointype)
{
case JOIN_INNER:
+
+ /*
+ * INNER JOIN can allow deletion of either child node, but not
+ * both. So right child gets permission to delete only if
+ * left child didn't get removed.
+ */
j->larg = pull_up_subqueries_recurse(root, j->larg,
lowest_outer_join,
lowest_nulling_outer_join,
! NULL,
! true);
j->rarg = pull_up_subqueries_recurse(root, j->rarg,
lowest_outer_join,
lowest_nulling_outer_join,
! NULL,
! j->larg != NULL);
break;
case JOIN_LEFT:
case JOIN_SEMI:
*************** pull_up_subqueries_recurse(PlannerInfo *
*** 716,746 ****
j->larg = pull_up_subqueries_recurse(root, j->larg,
j,
lowest_nulling_outer_join,
! NULL);
j->rarg = pull_up_subqueries_recurse(root, j->rarg,
j,
j,
! NULL);
break;
case JOIN_FULL:
j->larg = pull_up_subqueries_recurse(root, j->larg,
j,
j,
! NULL);
j->rarg = pull_up_subqueries_recurse(root, j->rarg,
j,
j,
! NULL);
break;
case JOIN_RIGHT:
j->larg = pull_up_subqueries_recurse(root, j->larg,
j,
j,
! NULL);
j->rarg = pull_up_subqueries_recurse(root, j->rarg,
j,
lowest_nulling_outer_join,
! NULL);
break;
default:
elog(ERROR, "unrecognized join type: %d",
--- 807,843 ----
j->larg = pull_up_subqueries_recurse(root, j->larg,
j,
lowest_nulling_outer_join,
! NULL,
! false);
j->rarg = pull_up_subqueries_recurse(root, j->rarg,
j,
j,
! NULL,
! false);
break;
case JOIN_FULL:
j->larg = pull_up_subqueries_recurse(root, j->larg,
j,
j,
! NULL,
! false);
j->rarg = pull_up_subqueries_recurse(root, j->rarg,
j,
j,
! NULL,
! false);
break;
case JOIN_RIGHT:
j->larg = pull_up_subqueries_recurse(root, j->larg,
j,
j,
! NULL,
! false);
j->rarg = pull_up_subqueries_recurse(root, j->rarg,
j,
lowest_nulling_outer_join,
! NULL,
! false);
break;
default:
elog(ERROR, "unrecognized join type: %d",
*************** pull_up_subqueries_recurse(PlannerInfo *
*** 760,767 ****
*
* jtnode is a RangeTblRef that has been tentatively identified as a simple
* subquery by pull_up_subqueries. We return the replacement jointree node,
! * or jtnode itself if we determine that the subquery can't be pulled up after
! * all.
*
* rte is the RangeTblEntry referenced by jtnode. Remaining parameters are
* as for pull_up_subqueries_recurse.
--- 857,864 ----
*
* jtnode is a RangeTblRef that has been tentatively identified as a simple
* subquery by pull_up_subqueries. We return the replacement jointree node,
! * or NULL if the subquery can be deleted entirely, or jtnode itself if we
! * determine that the subquery can't be pulled up after all.
*
* rte is the RangeTblEntry referenced by jtnode. Remaining parameters are
* as for pull_up_subqueries_recurse.
*************** static Node *
*** 770,776 ****
pull_up_simple_subquery(PlannerInfo *root, Node *jtnode, RangeTblEntry *rte,
JoinExpr *lowest_outer_join,
JoinExpr *lowest_nulling_outer_join,
! AppendRelInfo *containing_appendrel)
{
Query *parse = root->parse;
int varno = ((RangeTblRef *) jtnode)->rtindex;
--- 867,874 ----
pull_up_simple_subquery(PlannerInfo *root, Node *jtnode, RangeTblEntry *rte,
JoinExpr *lowest_outer_join,
JoinExpr *lowest_nulling_outer_join,
! AppendRelInfo *containing_appendrel,
! bool deletion_ok)
{
Query *parse = root->parse;
int varno = ((RangeTblRef *) jtnode)->rtindex;
*************** pull_up_simple_subquery(PlannerInfo *roo
*** 832,845 ****
* pull_up_subqueries' processing is complete for its jointree and
* rangetable.
*
! * Note: we should pass NULL for containing-join info even if we are
! * within an outer join in the upper query; the lower query starts with a
! * clean slate for outer-join semantics. Likewise, we say we aren't
! * handling an appendrel member.
*/
! subquery->jointree = (FromExpr *)
! pull_up_subqueries_recurse(subroot, (Node *) subquery->jointree,
! NULL, NULL, NULL);
/*
* Now we must recheck whether the subquery is still simple enough to pull
--- 930,941 ----
* pull_up_subqueries' processing is complete for its jointree and
* rangetable.
*
! * Note: it's okay that the subquery's recursion starts with NULL for
! * containing-join info, even if we are within an outer join in the upper
! * query; the lower query starts with a clean slate for outer-join
! * semantics. Likewise, we needn't pass down appendrel state.
*/
! pull_up_subqueries(subroot);
/*
* Now we must recheck whether the subquery is still simple enough to pull
*************** pull_up_simple_subquery(PlannerInfo *roo
*** 849,855 ****
* easier just to keep this "if" looking the same as the one in
* pull_up_subqueries_recurse.
*/
! if (is_simple_subquery(subquery, rte, lowest_outer_join) &&
(containing_appendrel == NULL || is_safe_append_member(subquery)))
{
/* good to go */
--- 945,952 ----
* easier just to keep this "if" looking the same as the one in
* pull_up_subqueries_recurse.
*/
! if (is_simple_subquery(subquery, rte,
! lowest_outer_join, deletion_ok) &&
(containing_appendrel == NULL || is_safe_append_member(subquery)))
{
/* good to go */
*************** pull_up_simple_subquery(PlannerInfo *roo
*** 1075,1082 ****
/*
* Return the adjusted subquery jointree to replace the RangeTblRef entry
! * in parent's jointree.
*/
return (Node *) subquery->jointree;
}
--- 1172,1189 ----
/*
* Return the adjusted subquery jointree to replace the RangeTblRef entry
! * in parent's jointree; or, if we're flattening a subquery with empty
! * FROM list, return NULL to signal deletion of the subquery from the
! * parent jointree (and set hasDeletedRTEs to ensure cleanup later).
*/
+ if (subquery->jointree->fromlist == NIL)
+ {
+ Assert(deletion_ok);
+ Assert(subquery->jointree->quals == NULL);
+ root->hasDeletedRTEs = true;
+ return NULL;
+ }
+
return (Node *) subquery->jointree;
}
*************** pull_up_union_leaf_queries(Node *setOp,
*** 1203,1214 ****
* must build the AppendRelInfo first, because this will modify it.)
* Note that we can pass NULL for containing-join info even if we're
* actually under an outer join, because the child's expressions
! * aren't going to propagate up to the join.
*/
rtr = makeNode(RangeTblRef);
rtr->rtindex = childRTindex;
(void) pull_up_subqueries_recurse(root, (Node *) rtr,
! NULL, NULL, appinfo);
}
else if (IsA(setOp, SetOperationStmt))
{
--- 1310,1324 ----
* must build the AppendRelInfo first, because this will modify it.)
* Note that we can pass NULL for containing-join info even if we're
* actually under an outer join, because the child's expressions
! * aren't going to propagate up to the join. Also, we ignore the
! * possibility that pull_up_subqueries_recurse() returns a different
! * jointree node than what we pass it; if it does, the important thing
! * is that it replaced the child relid in the AppendRelInfo node.
*/
rtr = makeNode(RangeTblRef);
rtr->rtindex = childRTindex;
(void) pull_up_subqueries_recurse(root, (Node *) rtr,
! NULL, NULL, appinfo, false);
}
else if (IsA(setOp, SetOperationStmt))
{
*************** make_setop_translation_list(Query *query
*** 1263,1272 ****
* (Note subquery is not necessarily equal to rte->subquery; it could be a
* processed copy of that.)
* lowest_outer_join is the lowest outer join above the subquery, or NULL.
*/
static bool
is_simple_subquery(Query *subquery, RangeTblEntry *rte,
! JoinExpr *lowest_outer_join)
{
/*
* Let's just make sure it's a valid subselect ...
--- 1373,1384 ----
* (Note subquery is not necessarily equal to rte->subquery; it could be a
* processed copy of that.)
* lowest_outer_join is the lowest outer join above the subquery, or NULL.
+ * deletion_ok is TRUE if it'd be okay to delete the subquery entirely.
*/
static bool
is_simple_subquery(Query *subquery, RangeTblEntry *rte,
! JoinExpr *lowest_outer_join,
! bool deletion_ok)
{
/*
* Let's just make sure it's a valid subselect ...
*************** is_simple_subquery(Query *subquery, Rang
*** 1315,1320 ****
--- 1427,1455 ----
return false;
/*
+ * Don't pull up a subquery with an empty jointree, unless it has no quals
+ * and deletion_ok is TRUE. query_planner() will correctly generate a
+ * Result plan for a jointree that's totally empty, but we can't cope with
+ * an empty FromExpr appearing lower down in a jointree: we identify join
+ * rels via baserelid sets, so we couldn't distinguish a join containing
+ * such a FromExpr from one without it. This would for example break the
+ * PlaceHolderVar mechanism, since we'd have no way to identify where to
+ * evaluate a PHV coming out of the subquery. We can only handle such
+ * cases if the place where the subquery is linked is a FromExpr or inner
+ * JOIN that would still be nonempty after removal of the subquery, so
+ * that it's still identifiable via its contained baserelids. Safe
+ * contexts are signaled by deletion_ok. But even in a safe context, we
+ * must keep the subquery if it has any quals, because it's unclear where
+ * to put them in the upper query. (Note that deletion of a subquery is
+ * also dependent on the check below that its targetlist contains no
+ * set-returning functions. Deletion from a FROM list or inner JOIN is
+ * okay only if the subquery must return exactly one row.)
+ */
+ if (subquery->jointree->fromlist == NIL &&
+ (subquery->jointree->quals || !deletion_ok))
+ return false;
+
+ /*
* If the subquery is LATERAL, check for pullup restrictions from that.
*/
if (rte->lateral)
*************** is_simple_subquery(Query *subquery, Rang
*** 1373,1379 ****
* Don't pull up a subquery that has any set-returning functions in its
* targetlist. Otherwise we might well wind up inserting set-returning
* functions into places where they mustn't go, such as quals of higher
! * queries.
*/
if (expression_returns_set((Node *) subquery->targetList))
return false;
--- 1508,1514 ----
* Don't pull up a subquery that has any set-returning functions in its
* targetlist. Otherwise we might well wind up inserting set-returning
* functions into places where they mustn't go, such as quals of higher
! * queries. This also ensures deletion of an empty jointree is valid.
*/
if (expression_returns_set((Node *) subquery->targetList))
return false;
*************** is_simple_subquery(Query *subquery, Rang
*** 1389,1407 ****
if (contain_volatile_functions((Node *) subquery->targetList))
return false;
/*
! * Don't pull up a subquery with an empty jointree. query_planner() will
! * correctly generate a Result plan for a jointree that's totally empty,
! * but we can't cope with an empty FromExpr appearing lower down in a
! * jointree: we identify join rels via baserelid sets, so we couldn't
! * distinguish a join containing such a FromExpr from one without it. This
! * would for example break the PlaceHolderVar mechanism, since we'd have
! * no way to identify where to evaluate a PHV coming out of the subquery.
! * Not worth working hard on this, just to collapse SubqueryScan/Result
! * into Result; especially since the SubqueryScan can often be optimized
! * away by setrefs.c anyway.
*/
! if (subquery->jointree->fromlist == NIL)
return false;
return true;
--- 1524,1682 ----
if (contain_volatile_functions((Node *) subquery->targetList))
return false;
+ return true;
+ }
+
+ /*
+ * pull_up_simple_values
+ * Pull up a single simple VALUES RTE.
+ *
+ * jtnode is a RangeTblRef that has been identified as a simple VALUES RTE
+ * by pull_up_subqueries. We always return NULL indicating that the RTE
+ * can be deleted entirely (all failure cases should have been detected by
+ * is_simple_values()).
+ *
+ * rte is the RangeTblEntry referenced by jtnode. Because of the limited
+ * possible usage of VALUES RTEs, we do not need the remaining parameters
+ * of pull_up_subqueries_recurse.
+ */
+ static Node *
+ pull_up_simple_values(PlannerInfo *root, Node *jtnode, RangeTblEntry *rte)
+ {
+ Query *parse = root->parse;
+ int varno = ((RangeTblRef *) jtnode)->rtindex;
+ List *values_list;
+ List *tlist;
+ AttrNumber attrno;
+ pullup_replace_vars_context rvcontext;
+ ListCell *lc;
+
+ Assert(rte->rtekind == RTE_VALUES);
+ Assert(list_length(rte->values_lists) == 1);
+
/*
! * Need a modifiable copy of the VALUES list to hack on, just in case it's
! * multiply referenced.
*/
! values_list = (List *) copyObject(linitial(rte->values_lists));
!
! /*
! * The VALUES RTE can't contain any Vars of level zero, let alone any that
! * are join aliases, so no need to flatten join alias Vars.
! */
! Assert(!contain_vars_of_level((Node *) values_list, 0));
!
! /*
! * Set up required context data for pullup_replace_vars. In particular,
! * we have to make the VALUES list look like a subquery targetlist.
! */
! tlist = NIL;
! attrno = 1;
! foreach(lc, values_list)
! {
! tlist = lappend(tlist,
! makeTargetEntry((Expr *) lfirst(lc),
! attrno,
! NULL,
! false));
! attrno++;
! }
! rvcontext.root = root;
! rvcontext.targetlist = tlist;
! rvcontext.target_rte = rte;
! rvcontext.relids = NULL;
! rvcontext.outer_hasSubLinks = &parse->hasSubLinks;
! rvcontext.varno = varno;
! rvcontext.need_phvs = false;
! rvcontext.wrap_non_vars = false;
! /* initialize cache array with indexes 0 .. length(tlist) */
! rvcontext.rv_cache = palloc0((list_length(tlist) + 1) *
! sizeof(Node *));
!
! /*
! * Replace all of the top query's references to the RTE's outputs with
! * copies of the adjusted VALUES expressions, being careful not to replace
! * any of the jointree structure. (This'd be a lot cleaner if we could use
! * query_tree_mutator.) Much of this should be no-ops in the dummy Query
! * that surrounds a VALUES RTE, but it's not enough code to be worth
! * removing.
! */
! parse->targetList = (List *)
! pullup_replace_vars((Node *) parse->targetList, &rvcontext);
! parse->returningList = (List *)
! pullup_replace_vars((Node *) parse->returningList, &rvcontext);
! replace_vars_in_jointree((Node *) parse->jointree, &rvcontext, NULL);
! Assert(parse->setOperations == NULL);
! parse->havingQual = pullup_replace_vars(parse->havingQual, &rvcontext);
!
! /*
! * There should be no appendrels to fix, nor any join alias Vars, nor any
! * outer joins and hence no PlaceHolderVars.
! */
! Assert(root->append_rel_list == NIL);
! Assert(list_length(parse->rtable) == 1);
! Assert(root->join_info_list == NIL);
! Assert(root->lateral_info_list == NIL);
! Assert(root->placeholder_list == NIL);
!
! /*
! * Return NULL to signal deletion of the VALUES RTE from the parent
! * jointree (and set hasDeletedRTEs to ensure cleanup later).
! */
! root->hasDeletedRTEs = true;
! return NULL;
! }
!
! /*
! * is_simple_values
! * Check a VALUES RTE in the range table to see if it's simple enough
! * to pull up into the parent query.
! *
! * rte is the RTE_VALUES RangeTblEntry to check.
! * deletion_ok is TRUE if it'd be okay to delete the VALUES RTE entirely.
! */
! static bool
! is_simple_values(PlannerInfo *root, RangeTblEntry *rte, bool deletion_ok)
! {
! Assert(rte->rtekind == RTE_VALUES);
!
! /*
! * We can only pull up a VALUES RTE if deletion_ok is TRUE. It's
! * basically the same case as a sub-select with empty FROM list; see
! * comments in is_simple_subquery().
! */
! if (!deletion_ok)
! return false;
!
! /*
! * Also, there must be exactly one VALUES list, else it's not semantically
! * correct to delete the VALUES RTE.
! */
! if (list_length(rte->values_lists) != 1)
! return false;
!
! /*
! * Because VALUES can't appear under an outer join (or at least, we won't
! * try to pull it up if it does), we need not worry about LATERAL.
! */
!
! /*
! * Don't pull up a VALUES that contains any set-returning or volatile
! * functions. Again, the considerations here are basically identical to
! * restrictions on a subquery's targetlist.
! */
! if (expression_returns_set((Node *) rte->values_lists) ||
! contain_volatile_functions((Node *) rte->values_lists))
! return false;
!
! /*
! * Do not pull up a VALUES that's not the only RTE in its parent query.
! * This is actually the only case that the parser will generate at the
! * moment, and assuming this is true greatly simplifies
! * pull_up_simple_values().
! */
! if (list_length(root->parse->rtable) != 1 ||
! rte != (RangeTblEntry *) linitial(root->parse->rtable))
return false;
return true;
*************** pullup_replace_vars_subquery(Query *quer
*** 1909,1914 ****
--- 2184,2248 ----
NULL);
}
+ /*
+ * pull_up_subqueries_cleanup
+ * Recursively fix up jointree after deletion of some subqueries.
+ *
+ * The jointree now contains some NULL subtrees, which we need to get rid of.
+ * In a FromExpr, just rebuild the child-node list with null entries deleted.
+ * In an inner JOIN, replace the JoinExpr node with a one-child FromExpr.
+ */
+ static Node *
+ pull_up_subqueries_cleanup(Node *jtnode)
+ {
+ Assert(jtnode != NULL);
+ if (IsA(jtnode, RangeTblRef))
+ {
+ /* Nothing to do at leaf nodes. */
+ }
+ else if (IsA(jtnode, FromExpr))
+ {
+ FromExpr *f = (FromExpr *) jtnode;
+ List *newfrom = NIL;
+ ListCell *l;
+
+ foreach(l, f->fromlist)
+ {
+ Node *child = (Node *) lfirst(l);
+
+ if (child == NULL)
+ continue;
+ child = pull_up_subqueries_cleanup(child);
+ newfrom = lappend(newfrom, child);
+ }
+ f->fromlist = newfrom;
+ }
+ else if (IsA(jtnode, JoinExpr))
+ {
+ JoinExpr *j = (JoinExpr *) jtnode;
+
+ if (j->larg)
+ j->larg = pull_up_subqueries_cleanup(j->larg);
+ if (j->rarg)
+ j->rarg = pull_up_subqueries_cleanup(j->rarg);
+ if (j->larg == NULL)
+ {
+ Assert(j->jointype == JOIN_INNER);
+ Assert(j->rarg != NULL);
+ return (Node *) makeFromExpr(list_make1(j->rarg), j->quals);
+ }
+ else if (j->rarg == NULL)
+ {
+ Assert(j->jointype == JOIN_INNER);
+ return (Node *) makeFromExpr(list_make1(j->larg), j->quals);
+ }
+ }
+ else
+ elog(ERROR, "unrecognized node type: %d",
+ (int) nodeTag(jtnode));
+ return jtnode;
+ }
+
/*
* flatten_simple_union_all
diff --git a/src/include/nodes/relation.h b/src/include/nodes/relation.h
index 6845a40..b221086 100644
*** a/src/include/nodes/relation.h
--- b/src/include/nodes/relation.h
*************** typedef struct PlannerInfo
*** 245,250 ****
--- 245,251 ----
* inheritance child rel */
bool hasJoinRTEs; /* true if any RTEs are RTE_JOIN kind */
bool hasLateralRTEs; /* true if any RTEs are marked LATERAL */
+ bool hasDeletedRTEs; /* true if any RTE was deleted from jointree */
bool hasHavingQual; /* true if havingQual was non-null */
bool hasPseudoConstantQuals; /* true if any RestrictInfo has
* pseudoconstant = true */
diff --git a/src/include/optimizer/prep.h b/src/include/optimizer/prep.h
index 385f4a8..05e46c5 100644
*** a/src/include/optimizer/prep.h
--- b/src/include/optimizer/prep.h
***************
*** 23,29 ****
*/
extern void pull_up_sublinks(PlannerInfo *root);
extern void inline_set_returning_functions(PlannerInfo *root);
! extern Node *pull_up_subqueries(PlannerInfo *root, Node *jtnode);
extern void flatten_simple_union_all(PlannerInfo *root);
extern void reduce_outer_joins(PlannerInfo *root);
extern Relids get_relids_in_jointree(Node *jtnode, bool include_joins);
--- 23,29 ----
*/
extern void pull_up_sublinks(PlannerInfo *root);
extern void inline_set_returning_functions(PlannerInfo *root);
! extern void pull_up_subqueries(PlannerInfo *root);
extern void flatten_simple_union_all(PlannerInfo *root);
extern void reduce_outer_joins(PlannerInfo *root);
extern Relids get_relids_in_jointree(Node *jtnode, bool include_joins);
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index ca3a17b..88a373b 100644
*** a/src/test/regress/expected/join.out
--- b/src/test/regress/expected/join.out
*************** select * from generate_series(100,200) g
*** 3595,3600 ****
--- 3595,3620 ----
explain (costs off)
select count(*) from tenk1 a,
tenk1 b join lateral (values(a.unique1)) ss(x) on b.unique2 = ss.x;
+ QUERY PLAN
+ ------------------------------------------------------------
+ Aggregate
+ -> Merge Join
+ Merge Cond: (a.unique1 = b.unique2)
+ -> Index Only Scan using tenk1_unique1 on tenk1 a
+ -> Index Only Scan using tenk1_unique2 on tenk1 b
+ (5 rows)
+
+ select count(*) from tenk1 a,
+ tenk1 b join lateral (values(a.unique1)) ss(x) on b.unique2 = ss.x;
+ count
+ -------
+ 10000
+ (1 row)
+
+ -- lateral with VALUES, no flattening possible
+ explain (costs off)
+ select count(*) from tenk1 a,
+ tenk1 b join lateral (values(a.unique1),(-1)) ss(x) on b.unique2 = ss.x;
QUERY PLAN
------------------------------------------------------------------
Aggregate
*************** explain (costs off)
*** 3608,3614 ****
(8 rows)
select count(*) from tenk1 a,
! tenk1 b join lateral (values(a.unique1)) ss(x) on b.unique2 = ss.x;
count
-------
10000
--- 3628,3634 ----
(8 rows)
select count(*) from tenk1 a,
! tenk1 b join lateral (values(a.unique1),(-1)) ss(x) on b.unique2 = ss.x;
count
-------
10000
*************** select * from
*** 4176,4182 ****
cross join
lateral (select q1, coalesce(ss1.x,q2) as y from int8_tbl d) ss2
) on c.q2 = ss2.q1,
! lateral (select ss2.y) ss3;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop
--- 4196,4202 ----
cross join
lateral (select q1, coalesce(ss1.x,q2) as y from int8_tbl d) ss2
) on c.q2 = ss2.q1,
! lateral (select ss2.y offset 0) ss3;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop
*************** select c.*,a.*,ss1.q1,ss2.q1,ss3.* from
*** 4258,4266 ****
-- check processing of postponed quals (bug #9041)
explain (verbose, costs off)
select * from
! (select 1 as x) x cross join (select 2 as y) y
left join lateral (
! select * from (select 3 as z) z where z.z = x.x
) zz on zz.z = y.y;
QUERY PLAN
----------------------------------------------
--- 4278,4286 ----
-- check processing of postponed quals (bug #9041)
explain (verbose, costs off)
select * from
! (select 1 as x offset 0) x cross join (select 2 as y offset 0) y
left join lateral (
! select * from (select 3 as z offset 0) z where z.z = x.x
) zz on zz.z = y.y;
QUERY PLAN
----------------------------------------------
diff --git a/src/test/regress/expected/rangefuncs.out b/src/test/regress/expected/rangefuncs.out
index 7991e99..6dabe50 100644
*** a/src/test/regress/expected/rangefuncs.out
--- b/src/test/regress/expected/rangefuncs.out
*************** select x from int8_tbl, extractq2(int8_t
*** 2034,2040 ****
(5 rows)
create function extractq2_2(t int8_tbl) returns table(ret1 int8) as $$
! select extractq2(t)
$$ language sql immutable;
explain (verbose, costs off)
select x from int8_tbl, extractq2_2(int8_tbl) f(x);
--- 2034,2040 ----
(5 rows)
create function extractq2_2(t int8_tbl) returns table(ret1 int8) as $$
! select extractq2(t) offset 0
$$ language sql immutable;
explain (verbose, costs off)
select x from int8_tbl, extractq2_2(int8_tbl) f(x);
*************** select x from int8_tbl, extractq2_2(int8
*** 2058,2060 ****
--- 2058,2082 ----
-4567890123456789
(5 rows)
+ -- without the "offset 0", this function gets optimized quite differently
+ create function extractq2_2_opt(t int8_tbl) returns table(ret1 int8) as $$
+ select extractq2(t)
+ $$ language sql immutable;
+ explain (verbose, costs off)
+ select x from int8_tbl, extractq2_2_opt(int8_tbl) f(x);
+ QUERY PLAN
+ -----------------------------
+ Seq Scan on public.int8_tbl
+ Output: int8_tbl.q2
+ (2 rows)
+
+ select x from int8_tbl, extractq2_2_opt(int8_tbl) f(x);
+ x
+ -------------------
+ 456
+ 4567890123456789
+ 123
+ 4567890123456789
+ -4567890123456789
+ (5 rows)
+
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index 6005476..82e5789 100644
*** a/src/test/regress/sql/join.sql
--- b/src/test/regress/sql/join.sql
*************** explain (costs off)
*** 1113,1118 ****
--- 1113,1125 ----
select count(*) from tenk1 a,
tenk1 b join lateral (values(a.unique1)) ss(x) on b.unique2 = ss.x;
+ -- lateral with VALUES, no flattening possible
+ explain (costs off)
+ select count(*) from tenk1 a,
+ tenk1 b join lateral (values(a.unique1),(-1)) ss(x) on b.unique2 = ss.x;
+ select count(*) from tenk1 a,
+ tenk1 b join lateral (values(a.unique1),(-1)) ss(x) on b.unique2 = ss.x;
+
-- lateral injecting a strange outer join condition
explain (costs off)
select * from int8_tbl a,
*************** select * from
*** 1223,1229 ****
cross join
lateral (select q1, coalesce(ss1.x,q2) as y from int8_tbl d) ss2
) on c.q2 = ss2.q1,
! lateral (select ss2.y) ss3;
-- case that breaks the old ph_may_need optimization
explain (verbose, costs off)
--- 1230,1236 ----
cross join
lateral (select q1, coalesce(ss1.x,q2) as y from int8_tbl d) ss2
) on c.q2 = ss2.q1,
! lateral (select ss2.y offset 0) ss3;
-- case that breaks the old ph_may_need optimization
explain (verbose, costs off)
*************** select c.*,a.*,ss1.q1,ss2.q1,ss3.* from
*** 1241,1249 ****
-- check processing of postponed quals (bug #9041)
explain (verbose, costs off)
select * from
! (select 1 as x) x cross join (select 2 as y) y
left join lateral (
! select * from (select 3 as z) z where z.z = x.x
) zz on zz.z = y.y;
-- test some error cases where LATERAL should have been used but wasn't
--- 1248,1256 ----
-- check processing of postponed quals (bug #9041)
explain (verbose, costs off)
select * from
! (select 1 as x offset 0) x cross join (select 2 as y offset 0) y
left join lateral (
! select * from (select 3 as z offset 0) z where z.z = x.x
) zz on zz.z = y.y;
-- test some error cases where LATERAL should have been used but wasn't
diff --git a/src/test/regress/sql/rangefuncs.sql b/src/test/regress/sql/rangefuncs.sql
index 470571b..9484023 100644
*** a/src/test/regress/sql/rangefuncs.sql
--- b/src/test/regress/sql/rangefuncs.sql
*************** select x from int8_tbl, extractq2(int8_t
*** 621,630 ****
select x from int8_tbl, extractq2(int8_tbl) f(x);
create function extractq2_2(t int8_tbl) returns table(ret1 int8) as $$
! select extractq2(t)
$$ language sql immutable;
explain (verbose, costs off)
select x from int8_tbl, extractq2_2(int8_tbl) f(x);
select x from int8_tbl, extractq2_2(int8_tbl) f(x);
--- 621,641 ----
select x from int8_tbl, extractq2(int8_tbl) f(x);
create function extractq2_2(t int8_tbl) returns table(ret1 int8) as $$
! select extractq2(t) offset 0
$$ language sql immutable;
explain (verbose, costs off)
select x from int8_tbl, extractq2_2(int8_tbl) f(x);
select x from int8_tbl, extractq2_2(int8_tbl) f(x);
+
+ -- without the "offset 0", this function gets optimized quite differently
+
+ create function extractq2_2_opt(t int8_tbl) returns table(ret1 int8) as $$
+ select extractq2(t)
+ $$ language sql immutable;
+
+ explain (verbose, costs off)
+ select x from int8_tbl, extractq2_2_opt(int8_tbl) f(x);
+
+ select x from int8_tbl, extractq2_2_opt(int8_tbl) f(x);
В списке pgsql-hackers по дате отправления: