Re: Implement targetlist SRFs using ROWS FROM() (was Changed SRF in targetlist handling) - Mailing list pgsql-hackers
| From | Tom Lane |
|---|---|
| Subject | Re: Implement targetlist SRFs using ROWS FROM() (was Changed SRF in targetlist handling) |
| Date | |
| Msg-id | 557.1473895705@sss.pgh.pa.us Whole thread Raw |
| In response to | Re: Implement targetlist SRFs using ROWS FROM() (was Changed SRF in targetlist handling) (Andres Freund <andres@anarazel.de>) |
| Responses |
Re: Implement targetlist SRFs using ROWS FROM() (was
Changed SRF in targetlist handling)
(Andres Freund <andres@anarazel.de>)
Re: Implement targetlist SRFs using ROWS FROM() (was Changed SRF in targetlist handling) (Andres Freund <andres@anarazel.de>) |
| List | pgsql-hackers |
Andres Freund <andres@anarazel.de> writes:
> On 2016-09-12 19:35:22 -0400, Tom Lane wrote:
>> Anyway I'll draft a prototype and then we can compare.
> Ok, cool.
Here's a draft patch that is just meant to investigate what the planner
changes might look like if we do it in the pipelined-result way.
Accordingly, I didn't touch the executor, but just had it emit regular
Result nodes for SRF-execution steps. However, the SRFs are all
guaranteed to appear at top level of their respective tlists, so that
those Results could be replaced with something that works like
nodeFunctionscan.
A difficulty with this restriction is that if you have a query like
"select f1, generate_series(1,2) / 10 from tab" then you end up with both
a SRF-executing Result and a separate scalar-projection Result above it,
because the division-by-ten has to happen in a separate plan level.
The planner's notions about the cost of Result make it think that this is
quite expensive --- mainly because the upper Result will be iterated once
per SRF output row, so that you get hit with cpu_tuple_cost per output row.
And that in turn leads it to change plans in one or two cases in the
regression tests. Maybe that's fine. I'm worried though that it's right
that this will be unduly expensive. So I'm kind of tempted to define the
SRF-executing node as acting more like, say, Agg or WindowFunc, in that
it has a list of SRFs to execute and then it has the ability to project a
scalar tlist on top of those results. That would likely save some cycles
at execution, and it would also eliminate a few of the planner warts seen
below, like the rule about not pushing a new scalar tlist down onto a
SRF-executing Result. I'd have to rewrite split_pathtarget_at_srfs(),
because it'd be implementing quite different rules about how to refactor
targetlists, but that's not a big problem.
On the whole I'm pretty pleased with this approach, at least from the
point of view of the planner. The net addition of planner code is
smaller than what you had, and though I'm no doubt biased, I think this
version is much cleaner. Also, though this patch doesn't address exactly
how we might do it, it's fairly clear that it'd be possible to allow
FDWs and CustomScans to implement SRF execution, eg pushing a SRF down to
a foreign server, in a reasonably straightforward extension of the
existing upper-pathification hooks. If we go with the lateral function
RTE approach, that's going to be somewhere between hard and impossible.
So I think we should continue investigating this way of doing things.
I'll try to take a look at the executor end of it tomorrow. However
I'm leaving Friday for a week's vacation, and may not have anything to
show before that.
regards, tom lane
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 7e092d7..9052273 100644
*** a/src/backend/nodes/outfuncs.c
--- b/src/backend/nodes/outfuncs.c
*************** _outProjectionPath(StringInfo str, const
*** 1817,1822 ****
--- 1817,1823 ----
WRITE_NODE_FIELD(subpath);
WRITE_BOOL_FIELD(dummypp);
+ WRITE_BOOL_FIELD(srfpp);
}
static void
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index 47158f6..7c59c3d 100644
*** a/src/backend/optimizer/plan/createplan.c
--- b/src/backend/optimizer/plan/createplan.c
*************** create_projection_plan(PlannerInfo *root
*** 1421,1428 ****
Plan *subplan;
List *tlist;
! /* Since we intend to project, we don't need to constrain child tlist */
! subplan = create_plan_recurse(root, best_path->subpath, 0);
tlist = build_path_tlist(root, &best_path->path);
--- 1421,1441 ----
Plan *subplan;
List *tlist;
! /*
! * XXX Possibly-temporary hack: if the subpath is a dummy ResultPath,
! * don't bother with it, just make a Result with no input. This avoids an
! * extra Result plan node when doing "SELECT srf()". Depending on what we
! * decide about the desired plan structure for SRF-expanding nodes, this
! * optimization might have to go away, and in any case it'll probably look
! * a good bit different.
! */
! if (IsA(best_path->subpath, ResultPath) &&
! ((ResultPath *) best_path->subpath)->path.pathtarget->exprs == NIL &&
! ((ResultPath *) best_path->subpath)->quals == NIL)
! subplan = NULL;
! else
! /* Since we intend to project, we don't need to constrain child tlist */
! subplan = create_plan_recurse(root, best_path->subpath, 0);
tlist = build_path_tlist(root, &best_path->path);
*************** create_projection_plan(PlannerInfo *root
*** 1441,1448 ****
* creation, but that would add expense to creating Paths we might end up
* not using.)
*/
! if (is_projection_capable_path(best_path->subpath) ||
! tlist_same_exprs(tlist, subplan->targetlist))
{
/* Don't need a separate Result, just assign tlist to subplan */
plan = subplan;
--- 1454,1462 ----
* creation, but that would add expense to creating Paths we might end up
* not using.)
*/
! if (!best_path->srfpp &&
! (is_projection_capable_path(best_path->subpath) ||
! tlist_same_exprs(tlist, subplan->targetlist)))
{
/* Don't need a separate Result, just assign tlist to subplan */
plan = subplan;
*************** is_projection_capable_path(Path *path)
*** 6185,6190 ****
--- 6199,6215 ----
* projection to its dummy path.
*/
return IS_DUMMY_PATH(path);
+ case T_Result:
+
+ /*
+ * If the path is doing SRF evaluation, claim it can't project, so
+ * we don't jam a new tlist into it and thereby break the property
+ * that the SRFs appear at top level.
+ */
+ if (IsA(path, ProjectionPath) &&
+ ((ProjectionPath *) path)->srfpp)
+ return false;
+ break;
default:
break;
}
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index f657ffc..8fff294 100644
*** a/src/backend/optimizer/plan/planner.c
--- b/src/backend/optimizer/plan/planner.c
*************** static List *make_pathkeys_for_window(Pl
*** 153,158 ****
--- 153,160 ----
static PathTarget *make_sort_input_target(PlannerInfo *root,
PathTarget *final_target,
bool *have_postponed_srfs);
+ static void adjust_paths_for_srfs(PlannerInfo *root, RelOptInfo *rel,
+ List *targets, List *targets_contain_srfs);
/*****************************************************************************
*************** grouping_planner(PlannerInfo *root, bool
*** 1440,1447 ****
int64 count_est = 0;
double limit_tuples = -1.0;
bool have_postponed_srfs = false;
- double tlist_rows;
PathTarget *final_target;
RelOptInfo *current_rel;
RelOptInfo *final_rel;
ListCell *lc;
--- 1442,1450 ----
int64 count_est = 0;
double limit_tuples = -1.0;
bool have_postponed_srfs = false;
PathTarget *final_target;
+ List *final_targets;
+ List *final_targets_contain_srfs;
RelOptInfo *current_rel;
RelOptInfo *final_rel;
ListCell *lc;
*************** grouping_planner(PlannerInfo *root, bool
*** 1504,1509 ****
--- 1507,1516 ----
/* Also extract the PathTarget form of the setop result tlist */
final_target = current_rel->cheapest_total_path->pathtarget;
+ /* The setop result tlist couldn't contain any SRFs */
+ Assert(!parse->hasTargetSRFs);
+ final_targets = final_targets_contain_srfs = NIL;
+
/*
* Can't handle FOR [KEY] UPDATE/SHARE here (parser should have
* checked already, but let's make sure).
*************** grouping_planner(PlannerInfo *root, bool
*** 1529,1536 ****
--- 1536,1549 ----
{
/* No set operations, do regular planning */
PathTarget *sort_input_target;
+ List *sort_input_targets;
+ List *sort_input_targets_contain_srfs;
PathTarget *grouping_target;
+ List *grouping_targets;
+ List *grouping_targets_contain_srfs;
PathTarget *scanjoin_target;
+ List *scanjoin_targets;
+ List *scanjoin_targets_contain_srfs;
bool have_grouping;
AggClauseCosts agg_costs;
WindowFuncLists *wflists = NULL;
*************** grouping_planner(PlannerInfo *root, bool
*** 1781,1788 ****
scanjoin_target = grouping_target;
/*
! * Forcibly apply scan/join target to all the Paths for the scan/join
! * rel.
*
* In principle we should re-run set_cheapest() here to identify the
* cheapest path, but it seems unlikely that adding the same tlist
--- 1794,1843 ----
scanjoin_target = grouping_target;
/*
! * If there are any SRFs in the targetlist, we must separate each of
! * these PathTargets into SRF-computing and SRF-free targets. Replace
! * each of the named targets with a SRF-free version, and remember the
! * list of additional projection steps we need to add afterwards.
! */
! if (parse->hasTargetSRFs)
! {
! /* final_target doesn't recompute any SRFs in sort_input_target */
! split_pathtarget_at_srfs(root, final_target, sort_input_target,
! &final_targets,
! &final_targets_contain_srfs);
! final_target = (PathTarget *) linitial(final_targets);
! Assert(!linitial_int(final_targets_contain_srfs));
! /* likewise for sort_input_target vs. grouping_target */
! split_pathtarget_at_srfs(root, sort_input_target, grouping_target,
! &sort_input_targets,
! &sort_input_targets_contain_srfs);
! sort_input_target = (PathTarget *) linitial(sort_input_targets);
! Assert(!linitial_int(sort_input_targets_contain_srfs));
! /* likewise for grouping_target vs. scanjoin_target */
! split_pathtarget_at_srfs(root, grouping_target, scanjoin_target,
! &grouping_targets,
! &grouping_targets_contain_srfs);
! grouping_target = (PathTarget *) linitial(grouping_targets);
! Assert(!linitial_int(grouping_targets_contain_srfs));
! /* scanjoin_target will not have any SRFs precomputed for it */
! split_pathtarget_at_srfs(root, scanjoin_target, NULL,
! &scanjoin_targets,
! &scanjoin_targets_contain_srfs);
! scanjoin_target = (PathTarget *) linitial(scanjoin_targets);
! Assert(!linitial_int(scanjoin_targets_contain_srfs));
! }
! else
! {
! /* initialize lists, just to keep compiler quiet */
! final_targets = final_targets_contain_srfs = NIL;
! sort_input_targets = sort_input_targets_contain_srfs = NIL;
! grouping_targets = grouping_targets_contain_srfs = NIL;
! scanjoin_targets = scanjoin_targets_contain_srfs = NIL;
! }
!
! /*
! * Forcibly apply SRF-free scan/join target to all the Paths for the
! * scan/join rel.
*
* In principle we should re-run set_cheapest() here to identify the
* cheapest path, but it seems unlikely that adding the same tlist
*************** grouping_planner(PlannerInfo *root, bool
*** 1853,1858 ****
--- 1908,1919 ----
current_rel->partial_pathlist = NIL;
}
+ /* Now fix things up if scan/join target contains SRFs */
+ if (parse->hasTargetSRFs)
+ adjust_paths_for_srfs(root, current_rel,
+ scanjoin_targets,
+ scanjoin_targets_contain_srfs);
+
/*
* Save the various upper-rel PathTargets we just computed into
* root->upper_targets[]. The core code doesn't use this, but it
*************** grouping_planner(PlannerInfo *root, bool
*** 1877,1882 ****
--- 1938,1948 ----
&agg_costs,
rollup_lists,
rollup_groupclauses);
+ /* Fix things up if grouping_target contains SRFs */
+ if (parse->hasTargetSRFs)
+ adjust_paths_for_srfs(root, current_rel,
+ grouping_targets,
+ grouping_targets_contain_srfs);
}
/*
*************** grouping_planner(PlannerInfo *root, bool
*** 1892,1897 ****
--- 1958,1968 ----
tlist,
wflists,
activeWindows);
+ /* Fix things up if sort_input_target contains SRFs */
+ if (parse->hasTargetSRFs)
+ adjust_paths_for_srfs(root, current_rel,
+ sort_input_targets,
+ sort_input_targets_contain_srfs);
}
/*
*************** grouping_planner(PlannerInfo *root, bool
*** 1920,1959 ****
final_target,
have_postponed_srfs ? -1.0 :
limit_tuples);
! }
!
! /*
! * If there are set-returning functions in the tlist, scale up the output
! * rowcounts of all surviving Paths to account for that. Note that if any
! * SRFs appear in sorting or grouping columns, we'll have underestimated
! * the numbers of rows passing through earlier steps; but that's such a
! * weird usage that it doesn't seem worth greatly complicating matters to
! * account for it.
! */
! if (parse->hasTargetSRFs)
! tlist_rows = tlist_returns_set_rows(tlist);
! else
! tlist_rows = 1;
!
! if (tlist_rows > 1)
! {
! foreach(lc, current_rel->pathlist)
! {
! Path *path = (Path *) lfirst(lc);
!
! /*
! * We assume that execution costs of the tlist as such were
! * already accounted for. However, it still seems appropriate to
! * charge something more for the executor's general costs of
! * processing the added tuples. The cost is probably less than
! * cpu_tuple_cost, though, so we arbitrarily use half of that.
! */
! path->total_cost += path->rows * (tlist_rows - 1) *
! cpu_tuple_cost / 2;
!
! path->rows *= tlist_rows;
! }
! /* No need to run set_cheapest; we're keeping all paths anyway. */
}
/*
--- 1991,2001 ----
final_target,
have_postponed_srfs ? -1.0 :
limit_tuples);
! /* Fix things up if final_target contains SRFs */
! if (parse->hasTargetSRFs)
! adjust_paths_for_srfs(root, current_rel,
! final_targets,
! final_targets_contain_srfs);
}
/*
*************** get_cheapest_fractional_path(RelOptInfo
*** 5151,5156 ****
--- 5193,5301 ----
}
/*
+ * adjust_paths_for_srfs
+ * Fix up the Paths of the given upperrel to handle tSRFs properly.
+ *
+ * The executor can only handle set-returning functions that appear at the
+ * top level of the targetlist of a Result plan node. If we have any SRFs
+ * that are not at top level, we need to split up the evaluation into multiple
+ * plan levels in which each level satisfies this constraint. This function
+ * modifies each Path of an upperrel that (might) compute any SRFs in its
+ * output tlist to insert appropriate projection steps.
+ *
+ * The given targets and targets_contain_srfs lists are from
+ * split_pathtarget_at_srfs(). We assume the existing Paths emit the first
+ * target in targets.
+ */
+ static void
+ adjust_paths_for_srfs(PlannerInfo *root, RelOptInfo *rel,
+ List *targets, List *targets_contain_srfs)
+ {
+ ListCell *lc;
+
+ Assert(list_length(targets) == list_length(targets_contain_srfs));
+ Assert(!linitial_int(targets_contain_srfs));
+
+ /* If no SRFs appear at this plan level, nothing to do */
+ if (list_length(targets) == 1)
+ return;
+
+ /*
+ * Stack SRF-evaluation nodes atop each path for the rel.
+ *
+ * In principle we should re-run set_cheapest() here to identify the
+ * cheapest path, but it seems unlikely that adding the same tlist eval
+ * costs to all the paths would change that, so we don't bother. Instead,
+ * just assume that the cheapest-startup and cheapest-total paths remain
+ * so. (There should be no parameterized paths anymore, so we needn't
+ * worry about updating cheapest_parameterized_paths.)
+ */
+ foreach(lc, rel->pathlist)
+ {
+ Path *subpath = (Path *) lfirst(lc);
+ Path *newpath = subpath;
+ ListCell *lc1,
+ *lc2;
+
+ Assert(subpath->param_info == NULL);
+ forboth(lc1, targets, lc2, targets_contain_srfs)
+ {
+ PathTarget *thistarget = (PathTarget *) lfirst(lc1);
+ bool contains_srfs = (bool) lfirst_int(lc2);
+
+ /* If this level doesn't contain SRFs, do regular projection */
+ if (contains_srfs)
+ newpath = (Path *) create_srf_projection_path(root,
+ rel,
+ newpath,
+ thistarget);
+ else
+ newpath = (Path *) apply_projection_to_path(root,
+ rel,
+ newpath,
+ thistarget);
+ }
+ lfirst(lc) = newpath;
+ if (subpath == rel->cheapest_startup_path)
+ rel->cheapest_startup_path = newpath;
+ if (subpath == rel->cheapest_total_path)
+ rel->cheapest_total_path = newpath;
+ }
+
+ /* Likewise for partial paths, if any */
+ foreach(lc, rel->partial_pathlist)
+ {
+ Path *subpath = (Path *) lfirst(lc);
+ Path *newpath = subpath;
+ ListCell *lc1,
+ *lc2;
+
+ Assert(subpath->param_info == NULL);
+ forboth(lc1, targets, lc2, targets_contain_srfs)
+ {
+ PathTarget *thistarget = (PathTarget *) lfirst(lc1);
+ bool contains_srfs = (bool) lfirst_int(lc2);
+
+ /* If this level doesn't contain SRFs, do regular projection */
+ if (contains_srfs)
+ newpath = (Path *) create_srf_projection_path(root,
+ rel,
+ newpath,
+ thistarget);
+ else
+ {
+ /* avoid apply_projection_to_path, in case of multiple refs */
+ newpath = (Path *) create_projection_path(root,
+ rel,
+ newpath,
+ thistarget);
+ }
+ }
+ lfirst(lc) = newpath;
+ }
+ }
+
+ /*
* expression_planner
* Perform planner's transformations on a standalone expression.
*
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index 663ffe0..0aa4339 100644
*** a/src/backend/optimizer/util/clauses.c
--- b/src/backend/optimizer/util/clauses.c
*************** static bool contain_agg_clause_walker(No
*** 99,105 ****
static bool get_agg_clause_costs_walker(Node *node,
get_agg_clause_costs_context *context);
static bool find_window_functions_walker(Node *node, WindowFuncLists *lists);
- static bool expression_returns_set_rows_walker(Node *node, double *count);
static bool contain_subplans_walker(Node *node, void *context);
static bool contain_mutable_functions_walker(Node *node, void *context);
static bool contain_volatile_functions_walker(Node *node, void *context);
--- 99,104 ----
*************** find_window_functions_walker(Node *node,
*** 780,893 ****
/*
* expression_returns_set_rows
* Estimate the number of rows returned by a set-returning expression.
! * The result is 1 if there are no set-returning functions.
*
! * We use the product of the rowcount estimates of all the functions in
! * the given tree (this corresponds to the behavior of ExecMakeFunctionResult
! * for nested set-returning functions).
*
* Note: keep this in sync with expression_returns_set() in nodes/nodeFuncs.c.
*/
double
expression_returns_set_rows(Node *clause)
{
! double result = 1;
!
! (void) expression_returns_set_rows_walker(clause, &result);
! return clamp_row_est(result);
! }
!
! static bool
! expression_returns_set_rows_walker(Node *node, double *count)
! {
! if (node == NULL)
! return false;
! if (IsA(node, FuncExpr))
{
! FuncExpr *expr = (FuncExpr *) node;
if (expr->funcretset)
! *count *= get_func_rows(expr->funcid);
}
! if (IsA(node, OpExpr))
{
! OpExpr *expr = (OpExpr *) node;
if (expr->opretset)
{
set_opfuncid(expr);
! *count *= get_func_rows(expr->opfuncid);
}
}
!
! /* Avoid recursion for some cases that can't return a set */
! if (IsA(node, Aggref))
! return false;
! if (IsA(node, WindowFunc))
! return false;
! if (IsA(node, DistinctExpr))
! return false;
! if (IsA(node, NullIfExpr))
! return false;
! if (IsA(node, ScalarArrayOpExpr))
! return false;
! if (IsA(node, BoolExpr))
! return false;
! if (IsA(node, SubLink))
! return false;
! if (IsA(node, SubPlan))
! return false;
! if (IsA(node, AlternativeSubPlan))
! return false;
! if (IsA(node, ArrayExpr))
! return false;
! if (IsA(node, RowExpr))
! return false;
! if (IsA(node, RowCompareExpr))
! return false;
! if (IsA(node, CoalesceExpr))
! return false;
! if (IsA(node, MinMaxExpr))
! return false;
! if (IsA(node, XmlExpr))
! return false;
!
! return expression_tree_walker(node, expression_returns_set_rows_walker,
! (void *) count);
! }
!
! /*
! * tlist_returns_set_rows
! * Estimate the number of rows returned by a set-returning targetlist.
! * The result is 1 if there are no set-returning functions.
! *
! * Here, the result is the largest rowcount estimate of any of the tlist's
! * expressions, not the product as you would get from naively applying
! * expression_returns_set_rows() to the whole tlist. The behavior actually
! * implemented by ExecTargetList produces a number of rows equal to the least
! * common multiple of the expression rowcounts, so that the product would be
! * a worst-case estimate that is typically not realistic. Taking the max as
! * we do here is a best-case estimate that might not be realistic either,
! * but it's probably closer for typical usages. We don't try to compute the
! * actual LCM because we're working with very approximate estimates, so their
! * LCM would be unduly noisy.
! */
! double
! tlist_returns_set_rows(List *tlist)
! {
! double result = 1;
! ListCell *lc;
!
! foreach(lc, tlist)
! {
! TargetEntry *tle = (TargetEntry *) lfirst(lc);
! double colresult;
!
! colresult = expression_returns_set_rows((Node *) tle->expr);
! if (result < colresult)
! result = colresult;
! }
! return result;
}
--- 779,815 ----
/*
* expression_returns_set_rows
* Estimate the number of rows returned by a set-returning expression.
! * The result is 1 if it's not a set-returning expression.
*
! * We should only examine the top-level function or operator; it used to be
! * appropriate to recurse, but not anymore. (Even if there are more SRFs in
! * the function's inputs, their multipliers are accounted for separately.)
*
* Note: keep this in sync with expression_returns_set() in nodes/nodeFuncs.c.
*/
double
expression_returns_set_rows(Node *clause)
{
! if (clause == NULL)
! return 1.0;
! if (IsA(clause, FuncExpr))
{
! FuncExpr *expr = (FuncExpr *) clause;
if (expr->funcretset)
! return clamp_row_est(get_func_rows(expr->funcid));
}
! if (IsA(clause, OpExpr))
{
! OpExpr *expr = (OpExpr *) clause;
if (expr->opretset)
{
set_opfuncid(expr);
! return clamp_row_est(get_func_rows(expr->opfuncid));
}
}
! return 1.0;
}
diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c
index abb7507..5a7891f 100644
*** a/src/backend/optimizer/util/pathnode.c
--- b/src/backend/optimizer/util/pathnode.c
*************** create_projection_path(PlannerInfo *root
*** 2227,2232 ****
--- 2227,2235 ----
(cpu_tuple_cost + target->cost.per_tuple) * subpath->rows;
}
+ /* Assume no SRFs around */
+ pathnode->srfpp = false;
+
return pathnode;
}
*************** apply_projection_to_path(PlannerInfo *ro
*** 2320,2325 ****
--- 2323,2400 ----
}
/*
+ * create_srf_projection_path
+ * Creates a pathnode that represents performing a SRF projection.
+ *
+ * For the moment, we just use ProjectionPath for this, and generate a
+ * Result plan node. That's likely to change.
+ *
+ * 'rel' is the parent relation associated with the result
+ * 'subpath' is the path representing the source of data
+ * 'target' is the PathTarget to be computed
+ */
+ ProjectionPath *
+ create_srf_projection_path(PlannerInfo *root,
+ RelOptInfo *rel,
+ Path *subpath,
+ PathTarget *target)
+ {
+ ProjectionPath *pathnode = makeNode(ProjectionPath);
+ double tlist_rows;
+ ListCell *lc;
+
+ pathnode->path.pathtype = T_Result;
+ pathnode->path.parent = rel;
+ pathnode->path.pathtarget = target;
+ /* For now, assume we are above any joins, so no parameterization */
+ pathnode->path.param_info = NULL;
+ pathnode->path.parallel_aware = false;
+ pathnode->path.parallel_safe = rel->consider_parallel &&
+ subpath->parallel_safe &&
+ is_parallel_safe(root, (Node *) target->exprs);
+ pathnode->path.parallel_workers = subpath->parallel_workers;
+ /* Projection does not change the sort order */
+ pathnode->path.pathkeys = subpath->pathkeys;
+
+ pathnode->subpath = subpath;
+
+ /* Always need the Result node */
+ pathnode->dummypp = false;
+ pathnode->srfpp = true;
+
+ /*
+ * Estimate number of rows produced by SRFs for each row of input; if
+ * there's more than one in this node, use the maximum.
+ */
+ tlist_rows = 1;
+ foreach(lc, target->exprs)
+ {
+ Node *node = (Node *) lfirst(lc);
+ double itemrows;
+
+ itemrows = expression_returns_set_rows(node);
+ if (tlist_rows < itemrows)
+ tlist_rows = itemrows;
+ }
+
+ /*
+ * In addition to the cost of evaluating the tlist, charge cpu_tuple_cost
+ * per input row, and half of cpu_tuple_cost for each added output row.
+ * This is slightly bizarre maybe, but it's what 9.6 did; we may revisit
+ * this estimate later.
+ */
+ pathnode->path.rows = subpath->rows * tlist_rows;
+ pathnode->path.startup_cost = subpath->startup_cost +
+ target->cost.startup;
+ pathnode->path.total_cost = subpath->total_cost +
+ target->cost.startup +
+ (cpu_tuple_cost + target->cost.per_tuple) * subpath->rows +
+ (pathnode->path.rows - subpath->rows) * cpu_tuple_cost / 2;
+
+ return pathnode;
+ }
+
+ /*
* create_sort_path
* Creates a pathnode that represents performing an explicit sort.
*
diff --git a/src/backend/optimizer/util/tlist.c b/src/backend/optimizer/util/tlist.c
index 68096b3..ede7bb9 100644
*** a/src/backend/optimizer/util/tlist.c
--- b/src/backend/optimizer/util/tlist.c
***************
*** 16,24 ****
--- 16,35 ----
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
+ #include "optimizer/cost.h"
#include "optimizer/tlist.h"
+ typedef struct
+ {
+ List *nextlevel_tlist;
+ bool nextlevel_contains_srfs;
+ } split_pathtarget_context;
+
+ static bool split_pathtarget_walker(Node *node,
+ split_pathtarget_context *context);
+
+
/*****************************************************************************
* Target list creation and searching utilities
*****************************************************************************/
*************** apply_pathtarget_labeling_to_tlist(List
*** 759,761 ****
--- 770,960 ----
i++;
}
}
+
+ /*
+ * split_pathtarget_at_srfs
+ * Split given PathTarget into multiple levels to position SRFs safely
+ *
+ * The executor can only handle set-returning functions that appear at the
+ * top level of the targetlist of a Result plan node. If we have any SRFs
+ * that are not at top level, we need to split up the evaluation into multiple
+ * plan levels in which each level satisfies this constraint. This function
+ * creates appropriate PathTarget(s) for each level.
+ *
+ * As an example, consider the tlist expression
+ * x + srf1(srf2(y + z))
+ * This expression should appear as-is in the top PathTarget, but below that
+ * we must have a PathTarget containing
+ * x, srf1(srf2(y + z))
+ * and below that, another PathTarget containing
+ * x, srf2(y + z)
+ * and below that, another PathTarget containing
+ * x, y, z
+ * When these tlists are processed by setrefs.c, subexpressions that match
+ * output expressions of the next lower tlist will be replaced by Vars,
+ * so that what the executor gets are tlists looking like
+ * Var1 + Var2
+ * Var1, srf1(Var2)
+ * Var1, srf2(Var2 + Var3)
+ * x, y, z
+ * which satisfy the desired property.
+ *
+ * In some cases, a SRF has already been evaluated in some previous plan level
+ * and we shouldn't expand it again (that is, what we see in the target is
+ * already meant as a reference to a lower subexpression). So, don't expand
+ * any tlist expressions that appear in input_target, if that's not NULL.
+ * In principle we might need to consider matching subexpressions to
+ * input_target, but for now it's not necessary because only ORDER BY and
+ * GROUP BY expressions are at issue and those will look the same at both
+ * plan levels.
+ *
+ * The outputs of this function are two parallel lists, one a list of
+ * PathTargets and the other an integer list of bool flags indicating
+ * whether the corresponding PathTarget contains any top-level SRFs.
+ * The lists are given in the order they'd need to be evaluated in, with
+ * the "lowest" PathTarget first. So the last list entry is always the
+ * originally given PathTarget, and any entries before it indicate evaluation
+ * levels that must be inserted below it. The first list entry must not
+ * contain any SRFs, since it will typically be attached to a plan node
+ * that cannot evaluate SRFs.
+ *
+ * Note: using a list for the flags may seem like overkill, since there
+ * are only a few possible patterns for which levels contain SRFs.
+ * But this representation decouples callers from that knowledge.
+ */
+ void
+ split_pathtarget_at_srfs(PlannerInfo *root,
+ PathTarget *target, PathTarget *input_target,
+ List **targets, List **targets_contain_srfs)
+ {
+ /* Initialize output lists to empty; we prepend to them within loop */
+ *targets = *targets_contain_srfs = NIL;
+
+ /* Loop to consider each level of PathTarget we need */
+ for (;;)
+ {
+ bool target_contains_srfs = false;
+ split_pathtarget_context context;
+ ListCell *lc;
+
+ context.nextlevel_tlist = NIL;
+ context.nextlevel_contains_srfs = false;
+
+ /*
+ * Scan the PathTarget looking for SRFs. Top-level SRFs are handled
+ * in this loop, ones lower down are found by split_pathtarget_walker.
+ */
+ foreach(lc, target->exprs)
+ {
+ Node *node = (Node *) lfirst(lc);
+
+ /*
+ * A tlist item that is just a reference to an expression already
+ * computed in input_target need not be evaluated here, so just
+ * make sure it's included in the next PathTarget.
+ */
+ if (input_target && list_member(input_target->exprs, node))
+ {
+ context.nextlevel_tlist = lappend(context.nextlevel_tlist, node);
+ continue;
+ }
+
+ /* Else, we need to compute this expression. */
+ if (IsA(node, FuncExpr) &&
+ ((FuncExpr *) node)->funcretset)
+ {
+ /* Top-level SRF: it can be evaluated here */
+ target_contains_srfs = true;
+ /* Recursively examine SRF's inputs */
+ split_pathtarget_walker((Node *) ((FuncExpr *) node)->args,
+ &context);
+ }
+ else if (IsA(node, OpExpr) &&
+ ((OpExpr *) node)->opretset)
+ {
+ /* Same as above, but for set-returning operator */
+ target_contains_srfs = true;
+ split_pathtarget_walker((Node *) ((OpExpr *) node)->args,
+ &context);
+ }
+ else
+ {
+ /* Not a top-level SRF, so recursively examine expression */
+ split_pathtarget_walker(node, &context);
+ }
+ }
+
+ /*
+ * Prepend current target and associated flag to output lists.
+ */
+ *targets = lcons(target, *targets);
+ *targets_contain_srfs = lcons_int(target_contains_srfs,
+ *targets_contain_srfs);
+
+ /*
+ * Done if we found no SRFs anywhere in this target; the tentative
+ * tlist we built for the next level can be discarded.
+ */
+ if (!target_contains_srfs && !context.nextlevel_contains_srfs)
+ break;
+
+ /*
+ * Else build the next PathTarget down, and loop back to process it.
+ * Copy the subexpressions to make sure PathTargets don't share
+ * substructure (might be unnecessary, but be safe); and drop any
+ * duplicate entries in the sub-targetlist.
+ */
+ target = create_empty_pathtarget();
+ add_new_columns_to_pathtarget(target,
+ (List *) copyObject(context.nextlevel_tlist));
+ set_pathtarget_cost_width(root, target);
+ }
+ }
+
+ /* Recursively examine expressions for split_pathtarget_at_srfs */
+ static bool
+ split_pathtarget_walker(Node *node, split_pathtarget_context *context)
+ {
+ if (node == NULL)
+ return false;
+ if (IsA(node, Var) ||
+ IsA(node, PlaceHolderVar) ||
+ IsA(node, Aggref) ||
+ IsA(node, GroupingFunc) ||
+ IsA(node, WindowFunc))
+ {
+ /*
+ * Pass these items down to the child plan level for evaluation.
+ *
+ * We assume that these constructs cannot contain any SRFs (if one
+ * does, there will be an executor failure from a misplaced SRF).
+ */
+ context->nextlevel_tlist = lappend(context->nextlevel_tlist, node);
+
+ /* Having done that, we need not examine their sub-structure */
+ return false;
+ }
+ else if ((IsA(node, FuncExpr) &&
+ ((FuncExpr *) node)->funcretset) ||
+ (IsA(node, OpExpr) &&
+ ((OpExpr *) node)->opretset))
+ {
+ /*
+ * Pass SRFs down to the child plan level for evaluation, and mark
+ * that it contains SRFs. (We are not at top level of our own tlist,
+ * else this would have been picked up by split_pathtarget_at_srfs.)
+ */
+ context->nextlevel_tlist = lappend(context->nextlevel_tlist, node);
+ context->nextlevel_contains_srfs = true;
+
+ /* Inputs to the SRF need not be considered here, so we're done */
+ return false;
+ }
+
+ /*
+ * Otherwise, the node is evaluatable within the current PathTarget, so
+ * recurse to examine its inputs.
+ */
+ return expression_tree_walker(node, split_pathtarget_walker,
+ (void *) context);
+ }
diff --git a/src/include/nodes/relation.h b/src/include/nodes/relation.h
index 2709cc7..0cb42b7 100644
*** a/src/include/nodes/relation.h
--- b/src/include/nodes/relation.h
*************** typedef struct ProjectionPath
*** 1293,1298 ****
--- 1293,1299 ----
Path path;
Path *subpath; /* path representing input source */
bool dummypp; /* true if no separate Result is needed */
+ bool srfpp; /* true if SRFs are being evaluated here */
} ProjectionPath;
/*
diff --git a/src/include/optimizer/clauses.h b/src/include/optimizer/clauses.h
index 9abef37..1d0fa30 100644
*** a/src/include/optimizer/clauses.h
--- b/src/include/optimizer/clauses.h
*************** extern bool contain_window_function(Node
*** 54,60 ****
extern WindowFuncLists *find_window_functions(Node *clause, Index maxWinRef);
extern double expression_returns_set_rows(Node *clause);
- extern double tlist_returns_set_rows(List *tlist);
extern bool contain_subplans(Node *clause);
--- 54,59 ----
diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.h
index 71d9154..c452927 100644
*** a/src/include/optimizer/pathnode.h
--- b/src/include/optimizer/pathnode.h
*************** extern Path *apply_projection_to_path(Pl
*** 144,149 ****
--- 144,153 ----
RelOptInfo *rel,
Path *path,
PathTarget *target);
+ extern ProjectionPath *create_srf_projection_path(PlannerInfo *root,
+ RelOptInfo *rel,
+ Path *subpath,
+ PathTarget *target);
extern SortPath *create_sort_path(PlannerInfo *root,
RelOptInfo *rel,
Path *subpath,
diff --git a/src/include/optimizer/tlist.h b/src/include/optimizer/tlist.h
index 0d745a0..edd1e80 100644
*** a/src/include/optimizer/tlist.h
--- b/src/include/optimizer/tlist.h
*************** extern void add_column_to_pathtarget(Pat
*** 61,66 ****
--- 61,69 ----
extern void add_new_column_to_pathtarget(PathTarget *target, Expr *expr);
extern void add_new_columns_to_pathtarget(PathTarget *target, List *exprs);
extern void apply_pathtarget_labeling_to_tlist(List *tlist, PathTarget *target);
+ extern void split_pathtarget_at_srfs(PlannerInfo *root,
+ PathTarget *target, PathTarget *input_target,
+ List **targets, List **targets_contain_srfs);
/* Convenience macro to get a PathTarget with valid cost/width fields */
#define create_pathtarget(root, tlist) \
diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out
index 45208a6..e3804e9 100644
*** a/src/test/regress/expected/aggregates.out
--- b/src/test/regress/expected/aggregates.out
*************** explain (costs off)
*** 823,829 ****
-> Index Only Scan Backward using tenk1_unique2 on tenk1
Index Cond: (unique2 IS NOT NULL)
-> Result
! (7 rows)
select max(unique2), generate_series(1,3) as g from tenk1 order by g desc;
max | g
--- 823,830 ----
-> Index Only Scan Backward using tenk1_unique2 on tenk1
Index Cond: (unique2 IS NOT NULL)
-> Result
! -> Result
! (8 rows)
select max(unique2), generate_series(1,3) as g from tenk1 order by g desc;
max | g
diff --git a/src/test/regress/expected/limit.out b/src/test/regress/expected/limit.out
index 9c3eecf..a7ded3a 100644
*** a/src/test/regress/expected/limit.out
--- b/src/test/regress/expected/limit.out
*************** select currval('testseq');
*** 208,220 ****
explain (verbose, costs off)
select unique1, unique2, generate_series(1,10)
from tenk1 order by unique2 limit 7;
! QUERY PLAN
! ----------------------------------------------------------
Limit
Output: unique1, unique2, (generate_series(1, 10))
! -> Index Scan using tenk1_unique2 on public.tenk1
Output: unique1, unique2, generate_series(1, 10)
! (4 rows)
select unique1, unique2, generate_series(1,10)
from tenk1 order by unique2 limit 7;
--- 208,222 ----
explain (verbose, costs off)
select unique1, unique2, generate_series(1,10)
from tenk1 order by unique2 limit 7;
! QUERY PLAN
!
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit
Output: unique1, unique2, (generate_series(1, 10))
! -> Result
Output: unique1, unique2, generate_series(1, 10)
! -> Index Scan using tenk1_unique2 on public.tenk1
! Output: unique1, unique2, two, four, ten, twenty, hundred, thousand, twothousand, fivethous, tenthous,
odd,even, stringu1, stringu2, string4
! (6 rows)
select unique1, unique2, generate_series(1,10)
from tenk1 order by unique2 limit 7;
diff --git a/src/test/regress/expected/rangefuncs.out b/src/test/regress/expected/rangefuncs.out
index f06cfa4..9634fa1 100644
*** a/src/test/regress/expected/rangefuncs.out
--- b/src/test/regress/expected/rangefuncs.out
*************** SELECT *,
*** 1995,2006 ****
END)
FROM
(VALUES (1,''), (2,'0000000049404'), (3,'FROM 10000000876')) v(id, str);
! id | str | lower
! ----+------------------+------------------
! 1 | |
! 2 | 0000000049404 | 49404
! 3 | FROM 10000000876 | from 10000000876
! (3 rows)
-- check whole-row-Var handling in nested lateral functions (bug #11703)
create function extractq2(t int8_tbl) returns int8 as $$
--- 1995,2004 ----
END)
FROM
(VALUES (1,''), (2,'0000000049404'), (3,'FROM 10000000876')) v(id, str);
! id | str | lower
! ----+---------------+-------
! 2 | 0000000049404 | 49404
! (1 row)
-- check whole-row-Var handling in nested lateral functions (bug #11703)
create function extractq2(t int8_tbl) returns int8 as $$
diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out
index 0fc93d9..e76cb6b 100644
*** a/src/test/regress/expected/subselect.out
--- b/src/test/regress/expected/subselect.out
*************** select * from int4_tbl where
*** 807,830 ****
explain (verbose, costs off)
select * from int4_tbl o where (f1, f1) in
(select f1, generate_series(1,2) / 10 g from int4_tbl i group by f1);
! QUERY PLAN
! ----------------------------------------------------------------
! Hash Semi Join
Output: o.f1
! Hash Cond: (o.f1 = "ANY_subquery".f1)
-> Seq Scan on public.int4_tbl o
Output: o.f1
! -> Hash
Output: "ANY_subquery".f1, "ANY_subquery".g
-> Subquery Scan on "ANY_subquery"
Output: "ANY_subquery".f1, "ANY_subquery".g
Filter: ("ANY_subquery".f1 = "ANY_subquery".g)
! -> HashAggregate
! Output: i.f1, (generate_series(1, 2) / 10)
! Group Key: i.f1
! -> Seq Scan on public.int4_tbl i
! Output: i.f1
! (15 rows)
select * from int4_tbl o where (f1, f1) in
(select f1, generate_series(1,2) / 10 g from int4_tbl i group by f1);
--- 807,834 ----
explain (verbose, costs off)
select * from int4_tbl o where (f1, f1) in
(select f1, generate_series(1,2) / 10 g from int4_tbl i group by f1);
! QUERY PLAN
! -------------------------------------------------------------------
! Nested Loop Semi Join
Output: o.f1
! Join Filter: (o.f1 = "ANY_subquery".f1)
-> Seq Scan on public.int4_tbl o
Output: o.f1
! -> Materialize
Output: "ANY_subquery".f1, "ANY_subquery".g
-> Subquery Scan on "ANY_subquery"
Output: "ANY_subquery".f1, "ANY_subquery".g
Filter: ("ANY_subquery".f1 = "ANY_subquery".g)
! -> Result
! Output: i.f1, ((generate_series(1, 2)) / 10)
! -> Result
! Output: i.f1, generate_series(1, 2)
! -> HashAggregate
! Output: i.f1
! Group Key: i.f1
! -> Seq Scan on public.int4_tbl i
! Output: i.f1
! (19 rows)
select * from int4_tbl o where (f1, f1) in
(select f1, generate_series(1,2) / 10 g from int4_tbl i group by f1);
diff --git a/src/test/regress/expected/tsrf.out b/src/test/regress/expected/tsrf.out
index e9bea41..4e87186 100644
*** a/src/test/regress/expected/tsrf.out
--- b/src/test/regress/expected/tsrf.out
*************** SELECT generate_series(1, generate_serie
*** 43,49 ****
-- srf, with two SRF arguments
SELECT generate_series(generate_series(1,3), generate_series(2, 4));
! ERROR: functions and operators can take at most one set argument
CREATE TABLE few(id int, dataa text, datab text);
INSERT INTO few VALUES(1, 'a', 'foo'),(2, 'a', 'bar'),(3, 'b', 'bar');
-- SRF output order of sorting is maintained, if SRF is not referenced
--- 43,58 ----
-- srf, with two SRF arguments
SELECT generate_series(generate_series(1,3), generate_series(2, 4));
! generate_series
! -----------------
! 1
! 2
! 2
! 3
! 3
! 4
! (6 rows)
!
CREATE TABLE few(id int, dataa text, datab text);
INSERT INTO few VALUES(1, 'a', 'foo'),(2, 'a', 'bar'),(3, 'b', 'bar');
-- SRF output order of sorting is maintained, if SRF is not referenced
pgsql-hackers by date: