2011/7/2 Hitoshi Harada <[email protected]>:
> 2011/6/29 Yeb Havinga <[email protected]>:
>>
>> On 2011-06-17 09:54, Hitoshi Harada wrote:
>>>
>>> While reviewing the gist/box patch, I found some planner APIs that can
>>> replace parts in my patch. Also, comments in includes wasn't updated
>>> appropriately. Revised patch attached.
>>
>> Hello Hitoshi-san,
>>
>> I read your latest patch implementing parameterizing subquery scans.
>
> Attached is revised version.
I failed to attached the patch. I'm trying again.
>> 1)
>> In the email from june 9 with the patch You wrote: "While IndexScan
>> is simple since its information like costs are well known by the base
>> relation, SubqueryScan should re-plan its Query to gain that, which is
>> expensive."
>>
>> Initial concerns I had were caused by misinterpreting 'replanning' as: for
>> each outer tuple, replan the subquery (it sounds a bit like 'ReScan').
>> Though the general comments in the patch are helpful, I think it would be an
>> improvement to describe why subqueries are planned more than once, i.e.
>> something like
>> "best_inner_subqueryscan
>> Try to find a better subqueryscan path and its associated plan for each
>> join clause that can be pushed down, in addition to the path that is already
>> calculated by set_subquery_pathlist."
>
> I changed comments around set_subquery_pathlist and best_inner_subqueryscan.
>
>> 2)
>> Since 'subquery_is_pushdown_safe' is invariant under join clause push down,
>> it might be possible to have it called only once in set_subquery_pathlist,
>> i.e. by only attaching rel->preprocessed_subquery if the
>> subquery_is_pushdown_safe.
>
> I modified as you suggested.
>
>> 3)
>> /*
>> * set_subquery_pathlist
>> * Build the (single) access path for a subquery RTE
>> */
>> This unchanged comment is still correct, but 'the (single) access path'
>> might have become a bit misleading now there are multiple paths possible,
>> though not by set_subquery_pathlist.
>
> As noted #1.
>
>> 4) somewhere in the patch
>> s/regsitered/registered/
>
> Fixed.
>
>> 5) Regression tests are missing; I think at this point they'd aid in
>> speeding up development/test cycles.
>
> I'm still thinking about it. I can add complex test but the concept of
> regression test focuses on small pieces of simple cases. I don't want
> take pg_regress much more than before.
>
>> 6) Before patching Postgres, I could execute the test with the size_l and
>> size_m tables, after patching against current git HEAD (patch without
>> errors), I get the following error when running the example query:
>> ERROR: plan should not reference subplan's variable
>>
>> I get the same error with the version from june 9 with current git HEAD.
>
> Fixed. Some variable initializing was wrong.
>
>> 7) Since both set_subquery_pathlist and best_inner_subqueryscan push down
>> clauses, as well as add a path and subplan, could a generalized function be
>> made to support both, to reduce duplicate code?
>
> No touch as answered before.
>
> Although I still need to think about suitable regression test case,
> the patch itself can be reviewed again. You may want to try some
> additional tests as you imagine after finding my test case gets
> quicker.
>
> Thanks,
>
> --
> Hitoshi Harada
>
--
Hitoshi Harada
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 681f5f8..039fd7f 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -1557,6 +1557,17 @@ _outTidPath(StringInfo str, TidPath *node)
}
static void
+_outSubqueryPath(StringInfo str, SubqueryPath *node)
+{
+ WRITE_NODE_TYPE("SUBQUERYPATH");
+
+ _outPathInfo(str, (Path *) node);
+ WRITE_NODE_FIELD(subplan);
+ WRITE_NODE_FIELD(subrtable);
+ WRITE_NODE_FIELD(subrowmark);
+}
+
+static void
_outForeignPath(StringInfo str, ForeignPath *node)
{
WRITE_NODE_TYPE("FOREIGNPATH");
@@ -1738,9 +1749,6 @@ _outRelOptInfo(StringInfo str, RelOptInfo *node)
WRITE_NODE_FIELD(indexlist);
WRITE_UINT_FIELD(pages);
WRITE_FLOAT_FIELD(tuples, "%.0f");
- WRITE_NODE_FIELD(subplan);
- WRITE_NODE_FIELD(subrtable);
- WRITE_NODE_FIELD(subrowmark);
WRITE_NODE_FIELD(baserestrictinfo);
WRITE_NODE_FIELD(joininfo);
WRITE_BOOL_FIELD(has_eclass_joins);
@@ -2948,6 +2956,9 @@ _outNode(StringInfo str, void *obj)
case T_TidPath:
_outTidPath(str, obj);
break;
+ case T_SubqueryPath:
+ _outSubqueryPath(str, obj);
+ break;
case T_ForeignPath:
_outForeignPath(str, obj);
break;
diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index 47ab08e..354a3e5 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -31,6 +31,7 @@
#include "optimizer/planner.h"
#include "optimizer/prep.h"
#include "optimizer/restrictinfo.h"
+#include "optimizer/subselect.h"
#include "optimizer/var.h"
#include "parser/parse_clause.h"
#include "parser/parsetree.h"
@@ -677,6 +678,12 @@ has_multiple_baserels(PlannerInfo *root)
/*
* set_subquery_pathlist
* Build the (single) access path for a subquery RTE
+ *
+ * Although we build only one access path for the subquery,
+ * join search process may find another path by pushing down
+ * the nestloop param into subquery. The finding process will
+ * be done much later than here, but some common operation like
+ * preprocessing subquery are shared.
*/
static void
set_subquery_pathlist(PlannerInfo *root, RelOptInfo *rel,
@@ -687,7 +694,9 @@ set_subquery_pathlist(PlannerInfo *root, RelOptInfo *rel,
bool *differentTypes;
double tuple_fraction;
PlannerInfo *subroot;
- List *pathkeys;
+ Plan *subplan;
+ List *pathkeys;
+ bool issafe = false;
/*
* Must copy the Query so that planning doesn't mess up the RTE contents
@@ -721,7 +730,7 @@ set_subquery_pathlist(PlannerInfo *root, RelOptInfo *rel,
* push down a pushable qual, because it'd result in a worse plan?
*/
if (rel->baserestrictinfo != NIL &&
- subquery_is_pushdown_safe(subquery, subquery, differentTypes))
+ (issafe = subquery_is_pushdown_safe(subquery, subquery, differentTypes)))
{
/* OK to consider pushing down individual quals */
List *upperrestrictlist = NIL;
@@ -749,6 +758,10 @@ set_subquery_pathlist(PlannerInfo *root, RelOptInfo *rel,
pfree(differentTypes);
+ /* save it for later use in best_inner_subqueryscan */
+ if (issafe)
+ rel->preprocessed_subquery = copyObject(subquery);
+
/*
* We can safely pass the outer tuple_fraction down to the subquery if the
* outer level has no joining, aggregation, or sorting to do. Otherwise
@@ -766,27 +779,187 @@ set_subquery_pathlist(PlannerInfo *root, RelOptInfo *rel,
tuple_fraction = root->tuple_fraction;
/* Generate the plan for the subquery */
- rel->subplan = subquery_planner(root->glob, subquery,
- root,
- false, tuple_fraction,
- &subroot);
- rel->subrtable = subroot->parse->rtable;
- rel->subrowmark = subroot->rowMarks;
+ subplan = subquery_planner(root->glob, subquery,
+ root,
+ false, tuple_fraction,
+ &subroot);
/* Mark rel with estimated output rows, width, etc */
- set_subquery_size_estimates(root, rel, subroot);
+ set_subquery_size_estimates(root, rel, subroot, subplan);
/* Convert subquery pathkeys to outer representation */
- pathkeys = convert_subquery_pathkeys(root, rel, subroot->query_pathkeys);
+ pathkeys = convert_subquery_pathkeys(root, rel,
+ subroot->query_pathkeys, subplan);
/* Generate appropriate path */
- add_path(rel, create_subqueryscan_path(rel, pathkeys));
+ add_path(rel, create_subqueryscan_path(rel, pathkeys,
+ subplan, subroot->parse->rtable, subroot->rowMarks));
/* Select cheapest path (pretty easy in this case...) */
set_cheapest(rel);
}
/*
+ * best_inner_subqueryscan
+ *
+ * As the inner scan relation, try to find another subquer path which may
+ * be better by pushing down some join clauses. If such possibility is found,
+ * return the path in *cheapest_start_path or *cheapest_total_path.
+ * Currently both paths will be resulted in the same, whereas further
+ * improvement might find cases they'd be different.
+ *
+ * It re-uses modified subquery (a.k.a. rel->preprocessed_subquery.) The basic
+ * push-down of baserestrictinfo was common among base path (done in
+ * set_subquery_pathlist()) so this routine doesn't care it. But once we find
+ * push-down-able join clause, the parsed subquery will be modified after copied.
+ */
+void
+best_inner_subqueryscan(PlannerInfo *root, RelOptInfo *rel,
+ RelOptInfo *outer_rel, List *join_clause,
+ Path **cheapest_start_path, Path **cheapest_total_path)
+{
+ Query *parse = root->parse;
+ Query *subquery;
+ Index rti = rel->relid;
+ RangeTblEntry *rte = planner_rt_fetch(rti, root);
+ bool *differentTypes;
+ double tuple_fraction;
+ PlannerInfo *subroot;
+ Plan *subplan;
+ List *pathkeys;
+ Path *path;
+ bool query_modified = false;
+
+ /* initialize paths to return immediately anytime */
+ *cheapest_start_path = *cheapest_total_path = NULL;
+
+ /*
+ * empty join condition doesn't make sense here
+ */
+ if (join_clause == NIL)
+ return;
+
+ /*
+ * If the subquery is not safe for pushdown, preprocessed
+ * subquery was not saved. Simply skip this process.
+ */
+ if (!rel->preprocessed_subquery)
+ return;
+
+ /* copying the whole Query is expensive; let's copy-on-write */
+ subquery = rel->preprocessed_subquery;
+
+ /* We need a workspace for keeping track of set-op type coercions */
+ differentTypes = (bool *)
+ palloc0((list_length(subquery->targetList) + 1) * sizeof(bool));
+
+ /*
+ * Currently we focus on only cases of aggregate subquery.
+ * I'm not quite sure if there are other useful cases.
+ */
+ if (subquery->hasAggs)
+ {
+ ListCell *l;
+
+ foreach (l, join_clause)
+ {
+ RestrictInfo *rinfo = (RestrictInfo *) lfirst(l);
+ Node *lexpr, *rexpr;
+ Expr *clause = NULL;
+ Param *param;
+ OpExpr *rclause;
+
+ /*
+ * For the first step, only Var = Var join clause is targeted.
+ * This may be improved in the future.
+ */
+ if (!is_opclause(rinfo->clause))
+ continue;
+ rclause = (OpExpr *) rinfo->clause;
+ lexpr = get_leftop((Expr *) rclause);
+ rexpr = get_rightop((Expr *) rclause);
+ if (!(IsA(lexpr, Var) && IsA(rexpr, Var)))
+ continue;
+
+ /* see which arg is mine and outer's */
+ if (bms_is_member(((Var *) lexpr)->varno, outer_rel->relids))
+ {
+ param = assign_nestloop_param(root, (Var *) lexpr);
+ clause = make_opclause(rclause->opno,
+ rclause->opresulttype,
+ false,
+ (Expr *) param,
+ (Expr *) copyObject(rexpr),
+ InvalidOid,
+ rclause->opcollid);
+ }
+ else if (bms_is_member(((Var *) rexpr)->varno, outer_rel->relids))
+ {
+ param = assign_nestloop_param(root, (Var *) rexpr);
+ clause = make_opclause(rclause->opno,
+ rclause->opresulttype,
+ false,
+ (Expr *) copyObject(lexpr),
+ (Expr *) param,
+ InvalidOid,
+ rclause->opcollid);
+ }
+
+ /* TODO:no check isouter or not? */
+ if (clause &&
+ qual_is_pushdown_safe(subquery, rti, (Node *) clause,
+ differentTypes))
+ {
+ /* copy on write */
+ if (rel->preprocessed_subquery == subquery)
+ subquery = copyObject(subquery);
+ subquery_push_qual(subquery, rte, rti, (Node *) clause);
+ query_modified = true;
+ }
+ }
+ }
+
+ pfree(differentTypes);
+
+ /* return immediately if such subquery isn't found. Use original one */
+ if (!query_modified)
+ return;
+
+ /*
+ * We can safely pass the outer tuple_fraction down to the subquery if the
+ * outer level has no joining, aggregation, or sorting to do. Otherwise
+ * we'd better tell the subquery to plan for full retrieval. (XXX This
+ * could probably be made more intelligent ...)
+ */
+ if (parse->hasAggs ||
+ parse->groupClause ||
+ parse->havingQual ||
+ parse->distinctClause ||
+ parse->sortClause ||
+ has_multiple_baserels(root))
+ tuple_fraction = 0.0; /* default case */
+ else
+ tuple_fraction = root->tuple_fraction;
+
+ /* Generate the plan for the subquery */
+ subplan = subquery_planner(root->glob, subquery,
+ root,
+ false, tuple_fraction,
+ &subroot);
+
+ /* Convert subquery pathkeys to outer representation */
+ pathkeys = convert_subquery_pathkeys(root, rel,
+ subroot->query_pathkeys, subplan);
+
+ /* Generate appropriate path */
+ path = create_subqueryscan_path(rel, pathkeys,
+ subplan, subroot->parse->rtable, subroot->rowMarks);
+
+ /* return the same path for now */
+ *cheapest_start_path = *cheapest_total_path = path;
+}
+
+/*
* set_function_pathlist
* Build the (single) access path for a function RTE
*/
diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index bb38768..b7271c3 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -892,7 +892,7 @@ cost_tidscan(Path *path, PlannerInfo *root,
* Determines and returns the cost of scanning a subquery RTE.
*/
void
-cost_subqueryscan(Path *path, RelOptInfo *baserel)
+cost_subqueryscan(SubqueryPath *path, RelOptInfo *baserel)
{
Cost startup_cost;
Cost run_cost;
@@ -907,15 +907,15 @@ cost_subqueryscan(Path *path, RelOptInfo *baserel)
* any restriction clauses that will be attached to the SubqueryScan node,
* plus cpu_tuple_cost to account for selection and projection overhead.
*/
- path->startup_cost = baserel->subplan->startup_cost;
- path->total_cost = baserel->subplan->total_cost;
+ path->path.startup_cost = path->subplan->startup_cost;
+ path->path.total_cost = path->subplan->total_cost;
startup_cost = baserel->baserestrictcost.startup;
cpu_per_tuple = cpu_tuple_cost + baserel->baserestrictcost.per_tuple;
run_cost = cpu_per_tuple * baserel->tuples;
- path->startup_cost += startup_cost;
- path->total_cost += startup_cost + run_cost;
+ path->path.startup_cost += startup_cost;
+ path->path.total_cost += startup_cost + run_cost;
}
/*
@@ -3222,7 +3222,7 @@ set_joinrel_size_estimates(PlannerInfo *root, RelOptInfo *rel,
*/
void
set_subquery_size_estimates(PlannerInfo *root, RelOptInfo *rel,
- PlannerInfo *subroot)
+ PlannerInfo *subroot, Plan *subplan)
{
RangeTblEntry *rte;
ListCell *lc;
@@ -3233,7 +3233,7 @@ set_subquery_size_estimates(PlannerInfo *root, RelOptInfo *rel,
Assert(rte->rtekind == RTE_SUBQUERY);
/* Copy raw number of output rows from subplan */
- rel->tuples = rel->subplan->plan_rows;
+ rel->tuples = subplan->plan_rows;
/*
* Compute per-output-column width estimates by examining the subquery's
diff --git a/src/backend/optimizer/path/joinpath.c b/src/backend/optimizer/path/joinpath.c
index 7d3cf42..c79fd43 100644
--- a/src/backend/optimizer/path/joinpath.c
+++ b/src/backend/optimizer/path/joinpath.c
@@ -329,7 +329,9 @@ sort_inner_and_outer(PlannerInfo *root,
* inner path, one on the same with materialization, one on the
* cheapest-startup-cost inner path (if different), one on the
* cheapest-total inner-indexscan path (if any), and one on the
- * cheapest-startup inner-indexscan path (if different).
+ * cheapest-startup inner-indexscan path (if different) or on the
+ * cheapest-total inner-subqueryscan path (if any).
+ * Note inner-indexpaths and inner-subquerypaths are not made at the same time.
*
* We also consider mergejoins if mergejoin clauses are available. We have
* two ways to generate the inner path for a mergejoin: sort the cheapest
@@ -369,6 +371,8 @@ match_unsorted_outer(PlannerInfo *root,
Path *matpath = NULL;
Path *index_cheapest_startup = NULL;
Path *index_cheapest_total = NULL;
+ Path *sub_cheapest_startup = NULL;
+ Path *sub_cheapest_total = NULL;
ListCell *l;
/*
@@ -430,8 +434,8 @@ match_unsorted_outer(PlannerInfo *root,
create_material_path(innerrel, inner_cheapest_total);
/*
- * Get the best innerjoin indexpaths (if any) for this outer rel.
- * They're the same for all outer paths.
+ * Get the best innerjoin indexpaths or subquerypaths (if any)
+ * for this outer rel. They're the same for all outer paths.
*/
if (innerrel->reloptkind != RELOPT_JOINREL)
{
@@ -444,6 +448,10 @@ match_unsorted_outer(PlannerInfo *root,
best_inner_indexscan(root, innerrel, outerrel, jointype,
&index_cheapest_startup,
&index_cheapest_total);
+ else if (innerrel->rtekind == RTE_SUBQUERY)
+ best_inner_subqueryscan(root, innerrel, outerrel, restrictlist,
+ &sub_cheapest_startup,
+ &sub_cheapest_total);
}
}
@@ -487,7 +495,7 @@ match_unsorted_outer(PlannerInfo *root,
* cheapest-total-cost inner. When appropriate, also consider
* using the materialized form of the cheapest inner, the
* cheapest-startup-cost inner path, and the cheapest innerjoin
- * indexpaths.
+ * indexpaths or subquerypaths.
*/
add_path(joinrel, (Path *)
create_nestloop_path(root,
@@ -539,6 +547,16 @@ match_unsorted_outer(PlannerInfo *root,
index_cheapest_startup,
restrictlist,
merge_pathkeys));
+ if (sub_cheapest_total != NULL)
+ add_path(joinrel, (Path *)
+ create_nestloop_path(root,
+ joinrel,
+ jointype,
+ sjinfo,
+ outerpath,
+ sub_cheapest_startup,
+ restrictlist,
+ merge_pathkeys));
}
/* Can't do anything else if outer path needs to be unique'd */
diff --git a/src/backend/optimizer/path/joinrels.c b/src/backend/optimizer/path/joinrels.c
index 24e4e59..c3f7ca0 100644
--- a/src/backend/optimizer/path/joinrels.c
+++ b/src/backend/optimizer/path/joinrels.c
@@ -745,7 +745,6 @@ make_join_rel(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2)
return joinrel;
}
-
/*
* have_join_order_restriction
* Detect whether the two relations should be joined to satisfy
diff --git a/src/backend/optimizer/path/pathkeys.c b/src/backend/optimizer/path/pathkeys.c
index e5228a8..cee5c71 100644
--- a/src/backend/optimizer/path/pathkeys.c
+++ b/src/backend/optimizer/path/pathkeys.c
@@ -623,12 +623,12 @@ find_indexkey_var(PlannerInfo *root, RelOptInfo *rel, AttrNumber varattno)
*/
List *
convert_subquery_pathkeys(PlannerInfo *root, RelOptInfo *rel,
- List *subquery_pathkeys)
+ List *subquery_pathkeys, Plan *subplan)
{
List *retval = NIL;
int retvallen = 0;
int outer_query_keys = list_length(root->query_pathkeys);
- List *sub_tlist = rel->subplan->targetlist;
+ List *sub_tlist = subplan->targetlist;
ListCell *i;
foreach(i, subquery_pathkeys)
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index e4ccf5c..417c9e3 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -62,8 +62,8 @@ static Plan *create_bitmap_subplan(PlannerInfo *root, Path *bitmapqual,
List **qual, List **indexqual);
static TidScan *create_tidscan_plan(PlannerInfo *root, TidPath *best_path,
List *tlist, List *scan_clauses);
-static SubqueryScan *create_subqueryscan_plan(PlannerInfo *root, Path *best_path,
- List *tlist, List *scan_clauses);
+static SubqueryScan *create_subqueryscan_plan(PlannerInfo *root,
+ SubqueryPath *best_path, List *tlist, List *scan_clauses);
static FunctionScan *create_functionscan_plan(PlannerInfo *root, Path *best_path,
List *tlist, List *scan_clauses);
static ValuesScan *create_valuesscan_plan(PlannerInfo *root, Path *best_path,
@@ -321,7 +321,7 @@ create_scan_plan(PlannerInfo *root, Path *best_path)
case T_SubqueryScan:
plan = (Plan *) create_subqueryscan_plan(root,
- best_path,
+ (SubqueryPath *) best_path,
tlist,
scan_clauses);
break;
@@ -1538,15 +1538,15 @@ create_tidscan_plan(PlannerInfo *root, TidPath *best_path,
* with restriction clauses 'scan_clauses' and targetlist 'tlist'.
*/
static SubqueryScan *
-create_subqueryscan_plan(PlannerInfo *root, Path *best_path,
+create_subqueryscan_plan(PlannerInfo *root, SubqueryPath *best_path,
List *tlist, List *scan_clauses)
{
SubqueryScan *scan_plan;
- Index scan_relid = best_path->parent->relid;
+ Index scan_relid = best_path->path.parent->relid;
/* it should be a subquery base rel... */
Assert(scan_relid > 0);
- Assert(best_path->parent->rtekind == RTE_SUBQUERY);
+ Assert(best_path->path.parent->rtekind == RTE_SUBQUERY);
/* Sort clauses into best execution order */
scan_clauses = order_qual_clauses(root, scan_clauses);
@@ -1557,11 +1557,63 @@ create_subqueryscan_plan(PlannerInfo *root, Path *best_path,
scan_plan = make_subqueryscan(tlist,
scan_clauses,
scan_relid,
- best_path->parent->subplan,
- best_path->parent->subrtable,
- best_path->parent->subrowmark);
+ best_path->subplan,
+ best_path->subrtable,
+ best_path->subrowmark);
- copy_path_costsize(&scan_plan->scan.plan, best_path);
+ copy_path_costsize(&scan_plan->scan.plan, &best_path->path);
+
+ /*
+ * If this plan is inner of nestloop and push-down-qual case,
+ * params are extracted and registered for the upper nestloop.
+ * As Plan has only bitmap extParam field, we cannot take them
+ * directly; instead take them from PlannerGlobal.
+ */
+ if (scan_plan->subplan->extParam)
+ {
+ int i;
+ ListCell *ppl;
+ PlannerParamItem *pitem;
+
+ i = 0;
+ foreach(ppl, root->glob->paramlist)
+ {
+ pitem = (PlannerParamItem *) lfirst(ppl);
+ /* take the ones only in this query level */
+ if (pitem->abslevel == root->query_level &&
+ IsA(pitem->item, Var) &&
+ ((Var *) pitem->item)->varlevelsup == 0)
+ {
+ Var *var = (Var *) pitem->item;
+ NestLoopParam *nlp;
+ ListCell *lc;
+ bool found = false;
+
+ /* Is the param already listed in root->curOuterParams? */
+ foreach(lc, root->curOuterParams)
+ {
+ nlp = (NestLoopParam *) lfirst(lc);
+ if (equal(nlp->paramval, var))
+ {
+ /* Present, so we can just skip */
+ found = true;
+ break;
+ }
+ }
+ if (found)
+ {
+ i++;
+ continue;
+ }
+ /* No, so add it */
+ nlp = makeNode(NestLoopParam);
+ nlp->paramno = i;
+ nlp->paramval = var;
+ root->curOuterParams = lappend(root->curOuterParams, nlp);
+ }
+ i++;
+ }
+ }
return scan_plan;
}
diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c
index 161d5ab..2e711d8 100644
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -1331,17 +1331,21 @@ distinct_col_search(int colno, List *colnos, List *opids)
* returning the pathnode.
*/
Path *
-create_subqueryscan_path(RelOptInfo *rel, List *pathkeys)
+create_subqueryscan_path(RelOptInfo *rel, List *pathkeys, Plan *subplan,
+ List *subrtable, List *subrowmark)
{
- Path *pathnode = makeNode(Path);
+ SubqueryPath *pathnode = makeNode(SubqueryPath);
- pathnode->pathtype = T_SubqueryScan;
- pathnode->parent = rel;
- pathnode->pathkeys = pathkeys;
+ pathnode->path.pathtype = T_SubqueryScan;
+ pathnode->path.parent = rel;
+ pathnode->path.pathkeys = pathkeys;
+ pathnode->subplan = subplan;
+ pathnode->subrtable = subrtable;
+ pathnode->subrowmark = subrowmark;
cost_subqueryscan(pathnode, rel);
- return pathnode;
+ return (Path *) pathnode;
}
/*
diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c
index b7a5845..ab2338f 100644
--- a/src/backend/optimizer/util/relnode.c
+++ b/src/backend/optimizer/util/relnode.c
@@ -82,9 +82,7 @@ build_simple_rel(PlannerInfo *root, int relid, RelOptKind reloptkind)
rel->indexlist = NIL;
rel->pages = 0;
rel->tuples = 0;
- rel->subplan = NULL;
- rel->subrtable = NIL;
- rel->subrowmark = NIL;
+ rel->preprocessed_subquery = NULL;
rel->baserestrictinfo = NIL;
rel->baserestrictcost.startup = 0;
rel->baserestrictcost.per_tuple = 0;
@@ -336,9 +334,7 @@ build_join_rel(PlannerInfo *root,
joinrel->indexlist = NIL;
joinrel->pages = 0;
joinrel->tuples = 0;
- joinrel->subplan = NULL;
- joinrel->subrtable = NIL;
- joinrel->subrowmark = NIL;
+ joinrel->preprocessed_subquery = NULL;
joinrel->baserestrictinfo = NIL;
joinrel->baserestrictcost.startup = 0;
joinrel->baserestrictcost.per_tuple = 0;
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
index d8bc6b8..e951fd1 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -220,6 +220,7 @@ typedef enum NodeTag
T_MergePath,
T_HashPath,
T_TidPath,
+ T_SubqueryPath,
T_ForeignPath,
T_AppendPath,
T_MergeAppendPath,
diff --git a/src/include/nodes/relation.h b/src/include/nodes/relation.h
index f659269..98e2744 100644
--- a/src/include/nodes/relation.h
+++ b/src/include/nodes/relation.h
@@ -322,13 +322,6 @@ typedef struct PlannerInfo
* (always NIL if it's not a table)
* pages - number of disk pages in relation (zero if not a table)
* tuples - number of tuples in relation (not considering restrictions)
- * subplan - plan for subquery (NULL if it's not a subquery)
- * subrtable - rangetable for subquery (NIL if it's not a subquery)
- * subrowmark - rowmarks for subquery (NIL if it's not a subquery)
- *
- * Note: for a subquery, tuples and subplan are not set immediately
- * upon creation of the RelOptInfo object; they are filled in when
- * set_base_rel_pathlist processes the object.
*
* For otherrels that are appendrel members, these fields are filled
* in just as for a baserel.
@@ -408,9 +401,8 @@ typedef struct RelOptInfo
List *indexlist; /* list of IndexOptInfo */
BlockNumber pages;
double tuples;
- struct Plan *subplan; /* if subquery */
- List *subrtable; /* if subquery */
- List *subrowmark; /* if subquery */
+
+ Query *preprocessed_subquery; /* if subquery */
/* used by various scans and joins: */
List *baserestrictinfo; /* RestrictInfo structures (if base
@@ -770,6 +762,23 @@ typedef struct TidPath
} TidPath;
/*
+ * SubqueryPath represents a scan of a subquery scan
+ *
+ * The struct holds subplan, subrtable and subrowmark, which are
+ * the temporary spaces to transport to the final stage of planner.
+ * Note each SubqueryPath may have different plan, depending on
+ * the pushed down qual clauses (as parameterized inner subquery).
+ */
+typedef struct SubqueryPath
+{
+ Path path;
+ struct Plan *subplan;
+ List *subrtable;
+ List *subrowmark;
+ double rows;
+} SubqueryPath;
+
+/*
* ForeignPath represents a scan of a foreign table
*/
typedef struct ForeignPath
diff --git a/src/include/optimizer/cost.h b/src/include/optimizer/cost.h
index 2763863..7ec2c7b 100644
--- a/src/include/optimizer/cost.h
+++ b/src/include/optimizer/cost.h
@@ -75,7 +75,7 @@ extern void cost_bitmap_or_node(BitmapOrPath *path, PlannerInfo *root);
extern void cost_bitmap_tree_node(Path *path, Cost *cost, Selectivity *selec);
extern void cost_tidscan(Path *path, PlannerInfo *root,
RelOptInfo *baserel, List *tidquals);
-extern void cost_subqueryscan(Path *path, RelOptInfo *baserel);
+extern void cost_subqueryscan(SubqueryPath *path, RelOptInfo *baserel);
extern void cost_functionscan(Path *path, PlannerInfo *root,
RelOptInfo *baserel);
extern void cost_valuesscan(Path *path, PlannerInfo *root,
@@ -122,7 +122,7 @@ extern void set_joinrel_size_estimates(PlannerInfo *root, RelOptInfo *rel,
SpecialJoinInfo *sjinfo,
List *restrictlist);
extern void set_subquery_size_estimates(PlannerInfo *root, RelOptInfo *rel,
- PlannerInfo *subroot);
+ PlannerInfo *subroot, Plan *subplan);
extern void set_function_size_estimates(PlannerInfo *root, RelOptInfo *rel);
extern void set_values_size_estimates(PlannerInfo *root, RelOptInfo *rel);
extern void set_cte_size_estimates(PlannerInfo *root, RelOptInfo *rel,
diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.h
index 1da2131..6de2816 100644
--- a/src/include/optimizer/pathnode.h
+++ b/src/include/optimizer/pathnode.h
@@ -14,6 +14,7 @@
#ifndef PATHNODE_H
#define PATHNODE_H
+#include "nodes/plannodes.h"
#include "nodes/relation.h"
@@ -56,7 +57,8 @@ extern ResultPath *create_result_path(List *quals);
extern MaterialPath *create_material_path(RelOptInfo *rel, Path *subpath);
extern UniquePath *create_unique_path(PlannerInfo *root, RelOptInfo *rel,
Path *subpath, SpecialJoinInfo *sjinfo);
-extern Path *create_subqueryscan_path(RelOptInfo *rel, List *pathkeys);
+extern Path *create_subqueryscan_path(RelOptInfo *rel, List *pathkeys,
+ Plan *subplan, List *subrtable, List *subrowmark);
extern Path *create_functionscan_path(PlannerInfo *root, RelOptInfo *rel);
extern Path *create_valuesscan_path(PlannerInfo *root, RelOptInfo *rel);
extern Path *create_ctescan_path(PlannerInfo *root, RelOptInfo *rel);
diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h
index 7f1353a..54d0b01 100644
--- a/src/include/optimizer/paths.h
+++ b/src/include/optimizer/paths.h
@@ -33,6 +33,9 @@ extern PGDLLIMPORT join_search_hook_type join_search_hook;
extern RelOptInfo *make_one_rel(PlannerInfo *root, List *joinlist);
extern RelOptInfo *standard_join_search(PlannerInfo *root, int levels_needed,
List *initial_rels);
+extern void best_inner_subqueryscan(PlannerInfo *root, RelOptInfo *rel,
+ RelOptInfo *outer_rel, List *join_clause,
+ Path **cheapest_start_path, Path **cheapest_total_path);
#ifdef OPTIMIZER_DEBUG
extern void debug_print_rel(PlannerInfo *root, RelOptInfo *rel);
@@ -154,7 +157,7 @@ extern Path *get_cheapest_fractional_path_for_pathkeys(List *paths,
extern List *build_index_pathkeys(PlannerInfo *root, IndexOptInfo *index,
ScanDirection scandir);
extern List *convert_subquery_pathkeys(PlannerInfo *root, RelOptInfo *rel,
- List *subquery_pathkeys);
+ List *subquery_pathkeys, Plan *subplan);
extern List *build_join_pathkeys(PlannerInfo *root,
RelOptInfo *joinrel,
JoinType jointype,
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
