Trying to pull up EXPR SubLinks
Hi All,
Currently we will not consider EXPR_SUBLINK when pulling up sublinks and
this would cause performance issues for some queries with the form of:
'a > (SELECT agg(b) from ...)' as described in [1]/messages/by-id/CAKU4AWodctmbU+Zj6U83y_RniQk0UeXBvKH1ZaJ=LR_iC90GOw@mail.gmail.com.
So here is a patch as an attempt to pull up EXPR SubLinks. The idea,
which is based on Greenplum's implementation, is to perform the
following transformation.
For query:
select * from foo where foo.a >
(select avg(bar.a) from bar where foo.b = bar.b);
we transform it to:
select * from foo inner join
(select bar.b, avg(bar.a) as avg from bar group by bar.b) sub
on foo.b = sub.b and foo.a > sub.avg;
To do that, we recurse through the quals in sub-select and extract quals
of form 'foo(outervar) = bar(innervar)' and then according to innervars
we make new SortGroupClause items and TargetEntry items for sub-select.
And at last we pull up the sub-select into upper range table.
As a result, the plan would change as:
FROM
QUERY PLAN
----------------------------------------
Seq Scan on foo
Filter: ((a)::numeric > (SubPlan 1))
SubPlan 1
-> Aggregate
-> Seq Scan on bar
Filter: (foo.b = b)
(6 rows)
TO
QUERY PLAN
--------------------------------------------------
Hash Join
Hash Cond: (foo.b = bar.b)
Join Filter: ((foo.a)::numeric > (avg(bar.a)))
-> Seq Scan on foo
-> Hash
-> HashAggregate
Group Key: bar.b
-> Seq Scan on bar
(8 rows)
The patch works but still in draft stage. Post it here to see if it is
the right thing we want.
[1]: /messages/by-id/CAKU4AWodctmbU+Zj6U83y_RniQk0UeXBvKH1ZaJ=LR_iC90GOw@mail.gmail.com
/messages/by-id/CAKU4AWodctmbU+Zj6U83y_RniQk0UeXBvKH1ZaJ=LR_iC90GOw@mail.gmail.com
Thanks
Richard
Attachments:
v1-0001-Draft-PR-for-pulling-up-EXPR_SUBLINK.patchapplication/octet-stream; name=v1-0001-Draft-PR-for-pulling-up-EXPR_SUBLINK.patchDownload
From c59338409c16cf9354f967d2f25f730c5ea3dda6 Mon Sep 17 00:00:00 2001
From: Richard Guo <riguo@pivotal.io>
Date: Mon, 25 Nov 2019 10:23:10 +0000
Subject: [PATCH] Draft PR for pulling up EXPR_SUBLINK
---
src/backend/optimizer/plan/subselect.c | 412 ++++++++++++++++++++++++++++++
src/backend/optimizer/prep/prepjointree.c | 70 ++++-
src/include/optimizer/subselect.h | 3 +
src/test/regress/expected/subselect.out | 80 ++++++
src/test/regress/sql/subselect.sql | 15 ++
5 files changed, 579 insertions(+), 1 deletion(-)
diff --git a/src/backend/optimizer/plan/subselect.c b/src/backend/optimizer/plan/subselect.c
index 3650e83..1bd3714 100644
--- a/src/backend/optimizer/plan/subselect.c
+++ b/src/backend/optimizer/plan/subselect.c
@@ -32,6 +32,7 @@
#include "optimizer/planner.h"
#include "optimizer/prep.h"
#include "optimizer/subselect.h"
+#include "optimizer/tlist.h"
#include "parser/parse_relation.h"
#include "rewrite/rewriteManip.h"
#include "utils/builtins.h"
@@ -65,6 +66,14 @@ typedef struct inline_cte_walker_context
Query *ctequery; /* query to substitute */
} inline_cte_walker_context;
+typedef struct convert_expr_sublink_context
+{
+ bool safe;
+ Node *joinQual;
+ Node *innerQual;
+ List *targetList;
+ List *groupClause;
+} convert_expr_sublink_context;
static Node *build_subplan(PlannerInfo *root, Plan *plan, PlannerInfo *subroot,
List *plan_params,
@@ -103,6 +112,14 @@ static Bitmapset *finalize_plan(PlannerInfo *root,
static bool finalize_primnode(Node *node, finalize_primnode_context *context);
static bool finalize_agg_primnode(Node *node, finalize_primnode_context *context);
+static void process_EXPR_sublink(Query *subselect,
+ convert_expr_sublink_context *context);
+static void process_EXPR_sublink_recurse(Node *node,
+ convert_expr_sublink_context *context);
+static bool check_correlated_equality(OpExpr *opexpr, Expr **outerExpr,
+ Expr **innerExpr, Oid *eqOp,
+ Oid *sortOp, bool *hashable);
+static void initConvertExprSublinkContext(convert_expr_sublink_context *ctx);
/*
* Get the datatype/typmod/collation of the first column of the plan's output.
@@ -1462,6 +1479,401 @@ convert_EXISTS_sublink_to_join(PlannerInfo *root, SubLink *sublink,
return result;
}
+static void
+initConvertExprSublinkContext(convert_expr_sublink_context *ctx)
+{
+ ctx->safe = true;
+ ctx->joinQual = NULL;
+ ctx->innerQual = NULL;
+ ctx->targetList = NIL;
+ ctx->groupClause = NIL;
+}
+
+/*
+ * Perform a more fine grained check to see if the sub-select can be pulled
+ * up and extract necessary information for the pull-up.
+ */
+static void
+process_EXPR_sublink(Query *subselect, convert_expr_sublink_context *context)
+{
+ Node *jtnode;
+ FromExpr *f = (FromExpr *) subselect->jointree;
+
+ Assert(list_length(f->fromlist) == 1);
+
+ /*
+ * For now we limit the jointree to be one element of type RangeTblRef.
+ * We can relax this limit in the future.
+ */
+ jtnode = (Node *) linitial(f->fromlist);
+ if (IsA(jtnode, JoinExpr))
+ {
+ context->safe = false;
+ return;
+ }
+
+ process_EXPR_sublink_recurse(f->quals, context);
+
+ if (!context->joinQual)
+ context->safe = false;
+}
+
+/*
+ * Recurse through nodes for process_EXPR_sublink()
+ */
+static void
+process_EXPR_sublink_recurse(Node *node, convert_expr_sublink_context *context)
+{
+ if (node == NULL)
+ return;
+
+ if (IsA(node, BoolExpr))
+ {
+ BoolExpr *blexpr = (BoolExpr *) node;
+ ListCell *lc = NULL;
+
+ /*
+ * Don't bother if there are any outer vars under an NOT or OR
+ * clause.
+ */
+ if (is_notclause(node) || is_orclause(node))
+ {
+ if (contain_vars_of_level(node, 1))
+ {
+ context->safe = false;
+ return;
+ }
+ context->innerQual = make_and_qual(context->innerQual, node);
+ return;
+ }
+
+ Assert(is_andclause(node));
+
+ foreach(lc, blexpr->args)
+ {
+ Node *arg = (Node *) lfirst(lc);
+
+ if (contain_vars_of_level(arg, 1))
+ {
+ process_EXPR_sublink_recurse(arg, context);
+
+ if (!context->safe)
+ return;
+ }
+ else
+ {
+ context->innerQual = make_and_qual(context->innerQual, arg);
+ }
+ }
+ }
+ else if (IsA(node, OpExpr))
+ {
+
+ OpExpr *opexpr = (OpExpr *) node;
+ Oid eqOp = InvalidOid;
+ Oid sortOp = InvalidOid;
+ bool hashable = false;
+ Expr *outerExpr = NULL;
+ Expr *innerExpr = NULL;
+
+ Assert(contain_vars_of_level(node, 1));
+
+ /*
+ * Check if the OpExpr is of form 'foo(outervar) = bar(innervar)'.
+ */
+ if (check_correlated_equality(opexpr, &outerExpr, &innerExpr,
+ &eqOp, &sortOp, &hashable))
+ {
+ SortGroupClause *gc;
+ TargetEntry *tle;
+ Expr *newInnerVar;
+ Expr *newOpExpr = NULL;
+
+ tle = makeTargetEntry(innerExpr,
+ list_length(context->targetList) + 1,
+ NULL,
+ false);
+ tle->ressortgroupref = list_length(context->targetList) + 1;
+ context->targetList = lappend(context->targetList, tle);
+
+ gc = makeNode(SortGroupClause);
+ gc->tleSortGroupRef = list_length(context->groupClause) + 1;
+ gc->eqop = eqOp;
+ gc->sortop = sortOp;
+ gc->hashable = hashable;
+ context->groupClause = lappend(context->groupClause, gc);
+
+ Assert(list_length(context->groupClause) ==
+ list_length(context->targetList));
+
+ /*
+ * Set the varno to 0 temporarily and adjust it later.
+ */
+ newInnerVar = (Expr *)makeVarFromTargetEntry(0, tle);
+ newOpExpr = make_opclause(opexpr->opno,
+ opexpr->opresulttype,
+ opexpr->opretset,
+ outerExpr,
+ newInnerVar,
+ opexpr->opcollid,
+ opexpr->inputcollid);
+
+ context->joinQual = make_and_qual(context->joinQual,
+ (Node *)newOpExpr);
+ }
+ else
+ {
+ context->safe = false;
+ }
+ }
+ else
+ {
+ context->safe = false;
+ }
+}
+
+/*
+ * Check if the OpExpr is of form 'foo(outervar) = bar(innervar)'.
+ */
+static bool
+check_correlated_equality(OpExpr *opexpr, Expr **outerExpr, Expr **innerExpr,
+ Oid *eqOp, Oid *sortOp, bool *hashable)
+{
+ Expr *left;
+ Expr *right;
+ Oid lefttype;
+ Oid righttype;
+ List *opfamilies;
+ Oid opfamily;
+
+ if (list_length(opexpr->args) != 2)
+ return false;
+
+ left = linitial(opexpr->args);
+ lefttype = exprType((Node *)left);
+
+ right = lsecond(opexpr->args);
+ righttype = exprType((Node *)right);
+
+ if (!op_mergejoinable(opexpr->opno, lefttype))
+ return false;
+
+ opfamilies = get_mergejoin_opfamilies(opexpr->opno);
+ if (!opfamilies)
+ return false;
+
+ opfamily = linitial_oid(opfamilies);
+
+ *eqOp = get_opfamily_member(opfamily,
+ lefttype,
+ righttype,
+ BTEqualStrategyNumber);
+ if (!OidIsValid(*eqOp))
+ elog(ERROR, "missing operator %d(%u,%u) in opfamily %u",
+ BTEqualStrategyNumber, lefttype, righttype, opfamily);
+
+ *sortOp = get_opfamily_member(opfamily,
+ lefttype,
+ righttype,
+ BTLessStrategyNumber);
+ if (!OidIsValid(*sortOp))
+ elog(ERROR, "missing operator %d(%u,%u) in opfamily %u",
+ BTLessStrategyNumber, lefttype, righttype, opfamily);
+
+ *hashable = op_hashjoinable(*eqOp, lefttype);
+
+ if (contain_vars_of_level((Node *)left, 1) &&
+ !contain_vars_of_level((Node *)left, 0) &&
+ contain_vars_of_level((Node *)right, 0) &&
+ !contain_vars_of_level((Node *)right, 1))
+ {
+ *outerExpr = (Expr *) copyObject(left);
+ *innerExpr = (Expr *) copyObject(right);
+
+ return true;
+ }
+
+ if (contain_vars_of_level((Node *)left, 0) &&
+ !contain_vars_of_level((Node *)left, 1) &&
+ contain_vars_of_level((Node *)right, 1) &&
+ !contain_vars_of_level((Node *)right, 0))
+ {
+ *outerExpr = (Expr *) copyObject(right);
+ *innerExpr = (Expr *) copyObject(left);
+
+ return true;
+ }
+
+ return false;
+}
+
+/*
+ * convert_EXPR_sublink_to_join: try to convert an EXPR SubLink to a join
+ *
+ * The API of this function is identical to convert_ANY_sublink_to_join's,
+ * except that the second input parameter is OpExpr rather than SubLink,
+ * becuase we need its first argument.
+ */
+JoinExpr *
+convert_EXPR_sublink_to_join(PlannerInfo *root, OpExpr *opexpr,
+ Relids available_rels)
+{
+ JoinExpr *result;
+ Expr *leftarg = (Expr *) linitial(opexpr->args);
+ SubLink *sublink = (SubLink *) lsecond(opexpr->args);
+ Query *subselect = (Query *) sublink->subselect;
+ Relids upper_varnos;
+ ParseNamespaceItem *nsitem;
+ ParseState *pstate;
+ int rtindex;
+ RangeTblRef *rtr;
+ Var *aggVar;
+ TargetEntry *origSubqueryTle;
+ Expr *newOpExpr;
+ convert_expr_sublink_context ctx;
+
+ ListCell *lc = NULL;
+ int teNum = 0;
+
+ /*
+ * The sub-select must contain some Vars of the parent query, else it's
+ * not gonna be a join.
+ */
+ if (!contain_vars_of_level((Node *) subselect, 1))
+ return NULL;
+
+ /*
+ * The OpExpr must not refer to anything outside available_rels.
+ */
+ upper_varnos = pull_varnos((Node *)opexpr);
+ if (bms_is_empty(upper_varnos))
+ return NULL;
+
+ if (!bms_is_subset(upper_varnos, available_rels))
+ return NULL;
+
+ /*
+ * To start with, we only handle one element targetList.
+ */
+ if (list_length(subselect->targetList) != 1)
+ return NULL;
+
+ /*
+ * To start with, we only handle one element jointree.
+ */
+ if (list_length(subselect->jointree->fromlist) != 1)
+ return NULL;
+
+ /*
+ * Don't bother if the sub-select does not have aggs.
+ */
+ if (!subselect->hasAggs)
+ return NULL;
+
+ /*
+ * Don't bother if the sub-select contains SRF in its targetList.
+ */
+ if (expression_returns_set((Node *) subselect->targetList))
+ return NULL;
+
+ /*
+ * Currently we cannot handle grouping clause, having qual, limit clause
+ * or set operations.
+ */
+ if (subselect->groupClause ||
+ subselect->havingQual ||
+ subselect->limitOffset ||
+ subselect->limitCount ||
+ subselect->setOperations)
+ return NULL;
+
+ initConvertExprSublinkContext(&ctx);
+
+ process_EXPR_sublink(subselect, &ctx);
+
+ if(!ctx.safe)
+ return NULL;
+
+ /*
+ * Construct the new targetList and groupClause for sub-select.
+ */
+ origSubqueryTle = (TargetEntry *) linitial(subselect->targetList);
+
+ subselect->targetList = add_to_flat_tlist(copyObject(ctx.targetList),
+ list_make1(origSubqueryTle->expr));
+ subselect->groupClause = ctx.groupClause;
+ subselect->jointree->quals = ctx.innerQual;
+
+ // TODO how to keep the old column names?
+ foreach(lc, subselect->targetList)
+ {
+ TargetEntry *te = (TargetEntry *) lfirst(lc);
+ char resname[32];
+
+ snprintf(resname, sizeof(resname), "c%d", teNum++);
+ te->resname = pstrdup(resname);
+ }
+
+ /*
+ * Pull up the sub-select into upper range table.
+ */
+ pstate = make_parsestate(NULL);
+ nsitem = addRangeTableEntryForSubquery(pstate,
+ subselect,
+ makeAlias("EXPR_subquery", NIL),
+ false,
+ false);
+ root->parse->rtable = lappend(root->parse->rtable, nsitem->p_rte);
+ rtindex = list_length(root->parse->rtable);
+
+ /*
+ * Form a RangeTblRef for the pulled-up sub-select.
+ */
+ rtr = makeNode(RangeTblRef);
+ rtr->rtindex = rtindex;
+
+ /*
+ * Adjust the varno for Vars of levelsup 0 in the new join qual.
+ */
+ OffsetVarNodes((Node *)ctx.joinQual, rtindex, 0);
+
+ /*
+ * Adjust the sublevelsup in the new join qual.
+ */
+ IncrementVarSublevelsUp(ctx.joinQual, -1, 1);
+
+ /*
+ * Construct a new OpExpr with Var referring to the pulled up sub-select
+ * and use it to replace the origin OpExpr.
+ */
+ aggVar = makeVarFromTargetEntry(rtindex,
+ (TargetEntry *) llast(subselect->targetList));
+
+ newOpExpr = make_opclause(opexpr->opno,
+ opexpr->opresulttype,
+ opexpr->opretset,
+ leftarg,
+ (Expr *)aggVar,
+ opexpr->opcollid,
+ opexpr->inputcollid);
+ ctx.joinQual = make_and_qual(ctx.joinQual, (Node *)newOpExpr);
+
+ /*
+ * And finally, build the JoinExpr node.
+ */
+ result = makeNode(JoinExpr);
+ result->jointype = JOIN_INNER;
+ result->isNatural = false;
+ result->larg = NULL; /* caller must fill this in */
+ result->rarg = (Node *) rtr;
+ result->usingClause = NIL;
+ result->quals = ctx.joinQual;
+ result->alias = NULL;
+ result->rtindex = 0; /* we don't need an RTE for it */
+
+ return result;
+}
+
/*
* simplify_EXISTS_query: remove any useless stuff in an EXISTS's subquery
*
diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c
index 1452172..b61194c 100644
--- a/src/backend/optimizer/prep/prepjointree.c
+++ b/src/backend/optimizer/prep/prepjointree.c
@@ -603,7 +603,75 @@ pull_up_sublinks_qual_recurse(PlannerInfo *root, Node *node,
else
return (Node *) make_andclause(newclauses);
}
- /* Stop if not an AND */
+ if (IsA(node, OpExpr))
+ {
+ OpExpr *expr = (OpExpr *) node;
+
+ if (list_length(expr->args) == 2)
+ {
+ Node *rarg = lsecond(expr->args);
+ JoinExpr *j;
+ Relids child_rels;
+
+ if (IsA(rarg, SubLink))
+ {
+ if ((j = convert_EXPR_sublink_to_join(root, expr,
+ available_rels1)) != NULL)
+ {
+ /* Yes; insert the new join node into the join tree */
+ j->larg = *jtlink1;
+ *jtlink1 = (Node *) j;
+ /* Recursively process pulled-up jointree nodes */
+ j->rarg = pull_up_sublinks_jointree_recurse(root,
+ j->rarg,
+ &child_rels);
+
+ /*
+ * Now recursively process the pulled-up quals. Any inserted
+ * joins can get stacked onto either j->larg or j->rarg,
+ * depending on which rels they reference.
+ */
+ j->quals = pull_up_sublinks_qual_recurse(root,
+ j->quals,
+ &j->larg,
+ available_rels1,
+ &j->rarg,
+ child_rels);
+ /* Return NULL representing constant TRUE */
+ return NULL;
+ }
+ if (available_rels2 != NULL &&
+ (j = convert_EXPR_sublink_to_join(root, expr,
+ available_rels2)) != NULL)
+ {
+ /* Yes; insert the new join node into the join tree */
+ j->larg = *jtlink2;
+ *jtlink2 = (Node *) j;
+ /* Recursively process pulled-up jointree nodes */
+ j->rarg = pull_up_sublinks_jointree_recurse(root,
+ j->rarg,
+ &child_rels);
+
+ /*
+ * Now recursively process the pulled-up quals. Any inserted
+ * joins can get stacked onto either j->larg or j->rarg,
+ * depending on which rels they reference.
+ */
+ j->quals = pull_up_sublinks_qual_recurse(root,
+ j->quals,
+ &j->larg,
+ available_rels2,
+ &j->rarg,
+ child_rels);
+ /* Return NULL representing constant TRUE */
+ return NULL;
+ }
+ }
+ }
+ /* Else return it unmodified */
+ return node;
+ }
+ /* Stop if not an OpExpr */
return node;
}
diff --git a/src/include/optimizer/subselect.h b/src/include/optimizer/subselect.h
index d6a872b..eefb6cd 100644
--- a/src/include/optimizer/subselect.h
+++ b/src/include/optimizer/subselect.h
@@ -24,6 +24,9 @@ extern JoinExpr *convert_EXISTS_sublink_to_join(PlannerInfo *root,
SubLink *sublink,
bool under_not,
Relids available_rels);
+extern JoinExpr *convert_EXPR_sublink_to_join(PlannerInfo *root,
+ OpExpr *opexpr,
+ Relids available_rels);
extern Node *SS_replace_correlation_vars(PlannerInfo *root, Node *expr);
extern Node *SS_process_sublinks(PlannerInfo *root, Node *expr, bool isQual);
extern void SS_identify_outer_params(PlannerInfo *root);
diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out
index 71a677b..a336730 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -1491,3 +1491,83 @@ select * from x for update;
Output: subselect_tbl.f1, subselect_tbl.f2, subselect_tbl.f3
(2 rows)
+--
+-- Test case for EXPR_SUBLINK pullup
+--
+explain (verbose, costs off)
+select * from subselect_tbl t1 where t1.f3 >
+ (select avg(t2.f3) from subselect_tbl t2 where t1.f1 = t2.f1 and t2.f2 = 3);
+ QUERY PLAN
+-------------------------------------------------------------
+ Hash Join
+ Output: t1.f1, t1.f2, t1.f3
+ Inner Unique: true
+ Hash Cond: (t1.f1 = t2.f1)
+ Join Filter: (t1.f3 > (avg(t2.f3)))
+ -> Seq Scan on public.subselect_tbl t1
+ Output: t1.f1, t1.f2, t1.f3
+ -> Hash
+ Output: t2.f1, (avg(t2.f3))
+ -> GroupAggregate
+ Output: t2.f1, avg(t2.f3)
+ Group Key: t2.f1
+ -> Sort
+ Output: t2.f1, t2.f3
+ Sort Key: t2.f1
+ -> Seq Scan on public.subselect_tbl t2
+ Output: t2.f1, t2.f3
+ Filter: (t2.f2 = 3)
+(18 rows)
+
+select * from subselect_tbl t1 where t1.f3 >
+ (select avg(t2.f3) from subselect_tbl t2 where t1.f1 = t2.f1 and t2.f2 = 3);
+ f1 | f2 | f3
+----+----+----
+ 3 | 4 | 5
+(1 row)
+
+explain (verbose, costs off)
+select * from subselect_tbl t1 left join subselect_tbl t2 on t2.f3 >
+ (select avg(t3.f3) from subselect_tbl t3 where t2.f1 = t3.f1 and t3.f2 = 3);
+ QUERY PLAN
+-------------------------------------------------------------------------
+ Nested Loop Left Join
+ Output: t1.f1, t1.f2, t1.f3, t2.f1, t2.f2, t2.f3
+ -> Seq Scan on public.subselect_tbl t1
+ Output: t1.f1, t1.f2, t1.f3
+ -> Materialize
+ Output: t2.f1, t2.f2, t2.f3
+ -> Hash Join
+ Output: t2.f1, t2.f2, t2.f3
+ Inner Unique: true
+ Hash Cond: (t2.f1 = t3.f1)
+ Join Filter: (t2.f3 > (avg(t3.f3)))
+ -> Seq Scan on public.subselect_tbl t2
+ Output: t2.f1, t2.f2, t2.f3
+ -> Hash
+ Output: t3.f1, (avg(t3.f3))
+ -> GroupAggregate
+ Output: t3.f1, avg(t3.f3)
+ Group Key: t3.f1
+ -> Sort
+ Output: t3.f1, t3.f3
+ Sort Key: t3.f1
+ -> Seq Scan on public.subselect_tbl t3
+ Output: t3.f1, t3.f3
+ Filter: (t3.f2 = 3)
+(24 rows)
+
+select * from subselect_tbl t1 left join subselect_tbl t2 on t2.f3 >
+ (select avg(t3.f3) from subselect_tbl t3 where t2.f1 = t3.f1 and t3.f2 = 3);
+ f1 | f2 | f3 | f1 | f2 | f3
+----+----+----+----+----+----
+ 1 | 2 | 3 | 3 | 4 | 5
+ 2 | 3 | 4 | 3 | 4 | 5
+ 3 | 4 | 5 | 3 | 4 | 5
+ 1 | 1 | 1 | 3 | 4 | 5
+ 2 | 2 | 2 | 3 | 4 | 5
+ 3 | 3 | 3 | 3 | 4 | 5
+ 6 | 7 | 8 | 3 | 4 | 5
+ 8 | 9 | | 3 | 4 | 5
+(8 rows)
+
diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sql
index bd8d2f6..3ddda39 100644
--- a/src/test/regress/sql/subselect.sql
+++ b/src/test/regress/sql/subselect.sql
@@ -766,3 +766,18 @@ select * from (with x as (select 2 as y) select * from x) ss;
explain (verbose, costs off)
with x as (select * from subselect_tbl)
select * from x for update;
+
+--
+-- Test case for EXPR_SUBLINK pullup
+--
+explain (verbose, costs off)
+select * from subselect_tbl t1 where t1.f3 >
+ (select avg(t2.f3) from subselect_tbl t2 where t1.f1 = t2.f1 and t2.f2 = 3);
+select * from subselect_tbl t1 where t1.f3 >
+ (select avg(t2.f3) from subselect_tbl t2 where t1.f1 = t2.f1 and t2.f2 = 3);
+
+explain (verbose, costs off)
+select * from subselect_tbl t1 left join subselect_tbl t2 on t2.f3 >
+ (select avg(t3.f3) from subselect_tbl t3 where t2.f1 = t3.f1 and t3.f2 = 3);
+select * from subselect_tbl t1 left join subselect_tbl t2 on t2.f3 >
+ (select avg(t3.f3) from subselect_tbl t3 where t2.f1 = t3.f1 and t3.f2 = 3);
--
2.7.4
On Fri, Feb 28, 2020 at 2:35 PM Richard Guo <guofenglinux@gmail.com> wrote:
Hi All,
Currently we will not consider EXPR_SUBLINK when pulling up sublinks and
this would cause performance issues for some queries with the form of:
'a > (SELECT agg(b) from ...)' as described in [1].So here is a patch as an attempt to pull up EXPR SubLinks. The idea,
which is based on Greenplum's implementation, is to perform the
following transformation.For query:
select * from foo where foo.a >
(select avg(bar.a) from bar where foo.b = bar.b);we transform it to:
select * from foo inner join
(select bar.b, avg(bar.a) as avg from bar group by bar.b) sub
on foo.b = sub.b and foo.a > sub.avg;
Glad to see this. I think the hard part is this transform is not *always*
good. for example foo.a only has 1 rows, but bar has a lot of rows, if so
the original would be the better one. doss this patch consider this
problem?
Show quoted text
Thanks
Richard
On Fri, Feb 28, 2020 at 3:02 PM Andy Fan <zhihui.fan1213@gmail.com> wrote:
On Fri, Feb 28, 2020 at 2:35 PM Richard Guo <guofenglinux@gmail.com>
wrote:Hi All,
Currently we will not consider EXPR_SUBLINK when pulling up sublinks and
this would cause performance issues for some queries with the form of:
'a > (SELECT agg(b) from ...)' as described in [1].So here is a patch as an attempt to pull up EXPR SubLinks. The idea,
which is based on Greenplum's implementation, is to perform the
following transformation.For query:
select * from foo where foo.a >
(select avg(bar.a) from bar where foo.b = bar.b);we transform it to:
select * from foo inner join
(select bar.b, avg(bar.a) as avg from bar group by bar.b) sub
on foo.b = sub.b and foo.a > sub.avg;Glad to see this. I think the hard part is this transform is not *always*
good. for example foo.a only has 1 rows, but bar has a lot of rows, if
so
the original would be the better one.
Yes exactly. TBH I'm not sure how to achieve that. Currently in the
patch this transformation happens in the stage of preprocessing the
jointree. We do not have enough information at this time to tell which
is better between the transformed one and untransformed one.
If we want to choose the better one by cost comparison, then we need to
plan the query twice, one for the transformed query and one for the
untransformed query. But this seems infeasible in current optimizer's
architecture.
Any ideas on this part?
Thanks
Richard
Richard Guo <guofenglinux@gmail.com> writes:
On Fri, Feb 28, 2020 at 3:02 PM Andy Fan <zhihui.fan1213@gmail.com> wrote:
Glad to see this. I think the hard part is this transform is not *always*
good. for example foo.a only has 1 rows, but bar has a lot of rows, if
so the original would be the better one.
Yes exactly. TBH I'm not sure how to achieve that.
Yeah, I was about to make the same objection when I saw Andy already had.
Without some moderately-reliable way of estimating whether the change
is actually a win, I think we're better off leaving it out. The user
can always rewrite the query for themselves if the grouped implementation
would be better -- but if the planner just does it blindly, there's no
recourse when it's worse.
Any ideas on this part?
I wonder whether it'd be possible to rewrite the query, but then
consider two implementations, one where the equality clause is
pushed down into the aggregating subquery as though it were LATERAL.
You'd want to be able to figure out that the presence of that clause
made it unnecessary to do the GROUP BY ... but having done so, a
plan treating the aggregating subquery as LATERAL ought to be pretty
nearly performance-equivalent to the current way. So this could be
mechanized in the current planner structure by treating that as a
parameterized path for the subquery, and comparing it to unparameterized
paths that calculate the full grouped output.
Obviously it'd be a long slog from here to there, but it seems like
maybe that could be made to work. There's a separate question about
whether it's really worth the trouble, seeing that the optimization
is available today to people who rewrite their queries.
regards, tom lane
On Fri, Feb 28, 2020 at 11:35 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Richard Guo <guofenglinux@gmail.com> writes:
On Fri, Feb 28, 2020 at 3:02 PM Andy Fan <zhihui.fan1213@gmail.com>
wrote:
Glad to see this. I think the hard part is this transform is not
*always*
good. for example foo.a only has 1 rows, but bar has a lot of rows, if
so the original would be the better one.Yes exactly. TBH I'm not sure how to achieve that.
Yeah, I was about to make the same objection when I saw Andy already had.
Without some moderately-reliable way of estimating whether the change
is actually a win, I think we're better off leaving it out. The user
can always rewrite the query for themselves if the grouped implementation
would be better -- but if the planner just does it blindly, there's no
recourse when it's worse.
Yes, that makes sense.
Any ideas on this part?
I wonder whether it'd be possible to rewrite the query, but then
consider two implementations, one where the equality clause is
pushed down into the aggregating subquery as though it were LATERAL.
You'd want to be able to figure out that the presence of that clause
made it unnecessary to do the GROUP BY ... but having done so, a
plan treating the aggregating subquery as LATERAL ought to be pretty
nearly performance-equivalent to the current way. So this could be
mechanized in the current planner structure by treating that as a
parameterized path for the subquery, and comparing it to unparameterized
paths that calculate the full grouped output.
I suppose this would happen in/around function set_subquery_pathlist.
When we generate access paths for the subquery, we try to push down the
equality clause into subquery, remove the unnecessary GROUP BY, etc.
and then perform another run of subquery_planner to generate the
parameterized path, and add it to the RelOptInfo for the subquery. So
that we can do comparison to unparameterized paths.
Am I understanding it correctly?
Obviously it'd be a long slog from here to there, but it seems like
maybe that could be made to work. There's a separate question about
whether it's really worth the trouble, seeing that the optimization
is available today to people who rewrite their queries.
If I understand correctly as above, yes, this would take quite a lot of
effort. Not sure if it's still worth doing.
Thanks
Richard
Actually I have a different opinion to handle this issue, to execute the
a > (select avg(a) from tinner where x = touer.x); The drawback of current
path is because it may calculates the same touer.x value multi-times. So
if we cache the values we have calculated before, we can avoid the cost.
Material path may be the one we can reference but it assumes all the tuples
in the tuplestore matches the input params, which is not the fact here.
But what if the input params doesn't change? If so we can use Material path
to optimize this case. But since we don't know if the if the input params
changed
or not during plan time, we just add the path (let's assume we can add it
with some
rules or cost calculation). If the input params is not changed, we use the
cached
values, if the input params changed, we can ReScan the Material node. To
optimize
the the cache invalidation frequent issue like (1, 2, 1, 2, 1, 2) case, we
may consider
a sort path to change the input values to (1, 1, 1, 2, 2, 2). But overall
it is a big effort.
As a independent small optimization maybe if the input params doesn't
change, we
can use the tuples in the Material node again. Suppose it will not
demage our current
framework if we can add the material path by either rules based or cost
based.
Suppose we have the following data:
demo=# select * from j1 limit 10;
i | im5 | im100 | im1000
----+-----+-------+--------
1 | 1 | 1 | 1
2 | 2 | 2 | 2
3 | 3 | 3 | 3
4 | 4 | 4 | 4
5 | 0 | 5 | 5
6 | 1 | 6 | 6
7 | 2 | 7 | 7
8 | 3 | 8 | 8
9 | 4 | 9 | 9
10 | 0 | 10 | 10
(10 rows)
totally we have j1 = 10,000,002 rows, the extra 2 rows because we have 3
rows for i=1
demo=# select * from j1 where i = 1;
i | im5 | im100 | im1000
---+-----+-------+--------
1 | 1 | 1 | 1
1 | 1 | 1 | 1
1 | 1 | 1 | 1
(3 rows)
Then select * from j1 j1o where im5 = (select avg(im5) from j1 where im5 =
j1o.im5) and i = 1;
will hit our above optimizations. The plan is
QUERY PLAN
-----------------------------------------------
Index Scan using j1_idx1 on j1 j1o
Index Cond: (i = 1)
Filter: ((im5)::numeric < (SubPlan 1))
SubPlan 1
-> Materialize
-> Aggregate
-> Seq Scan on j1
Filter: (im5 = j1o.im5)
(8 rows)
and the Aggregate is just executed once (execution time dropped from 8.x s
to 2.6s).
----
The attached is a very PoC patch, but it can represent my idea for
current discuss, Some notes about the implementation.
1. We need to check if the input params is really not changed. Currently
I just
comment it out for quick test.
- planstate->chgParam = bms_add_member(planstate->chgParam,
paramid);
+ // planstate->chgParam =
bms_add_member(planstate->chgParam, paramid);Looks we have a lot of places to add a params
to chgParam without checking the actual value. The place I found this case
is
during ExecNestLoop. So we may need a handy and efficient way to do the
check for all the places. However it is not a must for current case
2. I probably misunderstand the the usage of MaterialState->eflags.
since I don't
know why the eflag need to be checked ExecMaterial. and I have to remove
it to
let my PoC work.
- if (tuplestorestate == NULL && node->eflags != 0)
+ if (tuplestorestate == NULL)3. I added the material path in a very hacked way, the if check just to
make
sure it take effect on my test statement only. If you want to test this
patch locally,
you need to change the oid for your case.
+ if (linitial_node(RangeTblEntry, root->parse->rtable)->relid ==
25634)
+ best_path = (Path *) create_material_path(final_rel,
best_path);But when we take this action to production case, how to cost this strategy
is
challenge since it can neither reduce the total_cost nor result in a new
PathKey.
I will check other place to see how this kind can be added.
Best Regards
Andy Fan
Attachments:
v1-0001-Add-a-Material-Path-for-subplan-and-reused-the-pr.patchapplication/octet-stream; name=v1-0001-Add-a-Material-Path-for-subplan-and-reused-the-pr.patchDownload
From a9998cf54fe3e4277ef7431213179d1b811132a2 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?=E4=B8=80=E6=8C=83?= <yizhi.fzh@alibaba-inc.com>
Date: Fri, 24 Apr 2020 10:48:24 +0800
Subject: [PATCH v1] Add a Material Path for subplan and reused the previous
result if the inputed params is not changed. PoC only
---
src/backend/executor/nodeMaterial.c | 10 +++++++++-
src/backend/executor/nodeSubplan.c | 2 +-
src/backend/optimizer/plan/subselect.c | 4 ++++
src/backend/optimizer/util/pathnode.c | 2 +-
src/backend/utils/sort/tuplestore.c | 2 +-
5 files changed, 16 insertions(+), 4 deletions(-)
diff --git a/src/backend/executor/nodeMaterial.c b/src/backend/executor/nodeMaterial.c
index dd077f4323..67066686b8 100644
--- a/src/backend/executor/nodeMaterial.c
+++ b/src/backend/executor/nodeMaterial.c
@@ -59,7 +59,7 @@ ExecMaterial(PlanState *pstate)
/*
* If first time through, and we need a tuplestore, initialize it.
*/
- if (tuplestorestate == NULL && node->eflags != 0)
+ if (tuplestorestate == NULL)
{
tuplestorestate = tuplestore_begin_heap(true, false, work_mem);
tuplestore_set_eflags(tuplestorestate, node->eflags);
@@ -321,6 +321,14 @@ ExecReScanMaterial(MaterialState *node)
ExecClearTuple(node->ss.ps.ps_ResultTupleSlot);
+ /* Param is not changed, and all the underlying rows is fetched,
+ * We reuses current data */
+ if (outerPlan->chgParam == NULL && node->eof_underlying)
+ {
+ tuplestore_rescan(node->tuplestorestate);
+ return;
+ }
+
if (node->eflags != 0)
{
/*
diff --git a/src/backend/executor/nodeSubplan.c b/src/backend/executor/nodeSubplan.c
index 298b7757f5..35b9a3b2ce 100644
--- a/src/backend/executor/nodeSubplan.c
+++ b/src/backend/executor/nodeSubplan.c
@@ -288,7 +288,7 @@ ExecScanSubPlan(SubPlanState *node,
prm->value = ExecEvalExprSwitchContext((ExprState *) lfirst(pvar),
econtext,
&(prm->isnull));
- planstate->chgParam = bms_add_member(planstate->chgParam, paramid);
+ // planstate->chgParam = bms_add_member(planstate->chgParam, paramid);
}
/*
diff --git a/src/backend/optimizer/plan/subselect.c b/src/backend/optimizer/plan/subselect.c
index b02fcb9bfe..0b00de175e 100644
--- a/src/backend/optimizer/plan/subselect.c
+++ b/src/backend/optimizer/plan/subselect.c
@@ -23,6 +23,7 @@
#include "miscadmin.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
+#include "nodes/bitmapset.h"
#include "optimizer/clauses.h"
#include "optimizer/cost.h"
#include "optimizer/optimizer.h"
@@ -232,6 +233,9 @@ make_subplan(PlannerInfo *root, Query *orig_subquery,
final_rel = fetch_upper_rel(subroot, UPPERREL_FINAL, NULL);
best_path = get_cheapest_fractional_path(final_rel, tuple_fraction);
+ if (linitial_node(RangeTblEntry, root->parse->rtable)->relid == 25634)
+ best_path = (Path *) create_material_path(final_rel, best_path);
+
plan = create_plan(subroot, best_path);
/* And convert to SubPlan or InitPlan format. */
diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c
index e991385059..8d4946c45c 100644
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -1496,7 +1496,7 @@ create_material_path(RelOptInfo *rel, Path *subpath)
{
MaterialPath *pathnode = makeNode(MaterialPath);
- Assert(subpath->parent == rel);
+ // Assert(subpath->parent == rel);
pathnode->path.pathtype = T_Material;
pathnode->path.parent = rel;
diff --git a/src/backend/utils/sort/tuplestore.c b/src/backend/utils/sort/tuplestore.c
index ebb1da0746..df00b15aee 100644
--- a/src/backend/utils/sort/tuplestore.c
+++ b/src/backend/utils/sort/tuplestore.c
@@ -1234,7 +1234,7 @@ tuplestore_rescan(Tuplestorestate *state)
{
TSReadPointer *readptr = &state->readptrs[state->activeptr];
- Assert(readptr->eflags & EXEC_FLAG_REWIND);
+ // Assert(readptr->eflags & EXEC_FLAG_REWIND);
Assert(!state->truncated);
switch (state->status)
--
2.21.0
On Fri, Apr 24, 2020 at 8:56 AM Andy Fan <zhihui.fan1213@gmail.com> wrote:
Actually I have a different opinion to handle this issue, to execute the
a > (select avg(a) from tinner where x = touer.x); The drawback of current
path is because it may calculates the same touer.x value multi-times. So
if we cache the values we have calculated before, we can avoid the cost.
Material path may be the one we can reference but it assumes all the tuples
in the tuplestore matches the input params, which is not the fact here.But what if the input params doesn't change? If so we can use Material path
to optimize this case. But since we don't know if the if the input params changed
or not during plan time, we just add the path (let's assume we can add it with some
rules or cost calculation). If the input params is not changed, we use the cached
values, if the input params changed, we can ReScan the Material node. To optimize
the the cache invalidation frequent issue like (1, 2, 1, 2, 1, 2) case, we may consider
a sort path to change the input values to (1, 1, 1, 2, 2, 2). But overall it is a big effort.As a independent small optimization maybe if the input params doesn't change, we
can use the tuples in the Material node again. Suppose it will not demage our current
framework if we can add the material path by either rules based or cost based.Suppose we have the following data:
demo=# select * from j1 limit 10;
i | im5 | im100 | im1000
----+-----+-------+--------
1 | 1 | 1 | 1
2 | 2 | 2 | 2
3 | 3 | 3 | 3
4 | 4 | 4 | 4
5 | 0 | 5 | 5
6 | 1 | 6 | 6
7 | 2 | 7 | 7
8 | 3 | 8 | 8
9 | 4 | 9 | 9
10 | 0 | 10 | 10
(10 rows)totally we have j1 = 10,000,002 rows, the extra 2 rows because we have 3 rows for i=1
demo=# select * from j1 where i = 1;
i | im5 | im100 | im1000
---+-----+-------+--------
1 | 1 | 1 | 1
1 | 1 | 1 | 1
1 | 1 | 1 | 1
(3 rows)Then select * from j1 j1o where im5 = (select avg(im5) from j1 where im5 = j1o.im5) and i = 1;
will hit our above optimizations. The plan isQUERY PLAN
-----------------------------------------------
Index Scan using j1_idx1 on j1 j1o
Index Cond: (i = 1)
Filter: ((im5)::numeric < (SubPlan 1))
SubPlan 1
-> Materialize
-> Aggregate
-> Seq Scan on j1
Filter: (im5 = j1o.im5)
(8 rows)and the Aggregate is just executed once (execution time dropped from 8.x s
to 2.6s).----
The attached is a very PoC patch, but it can represent my idea for
current discuss, Some notes about the implementation.1. We need to check if the input params is really not changed. Currently I just
comment it out for quick test.- planstate->chgParam = bms_add_member(planstate->chgParam, paramid); + // planstate->chgParam = bms_add_member(planstate->chgParam, paramid);Looks we have a lot of places to add a params
to chgParam without checking the actual value. The place I found this case is
during ExecNestLoop. So we may need a handy and efficient way to do the
check for all the places. However it is not a must for current case2. I probably misunderstand the the usage of MaterialState->eflags. since I don't
know why the eflag need to be checked ExecMaterial. and I have to remove it to
let my PoC work.- if (tuplestorestate == NULL && node->eflags != 0) + if (tuplestorestate == NULL)3. I added the material path in a very hacked way, the if check just to make
sure it take effect on my test statement only. If you want to test this patch locally,
you need to change the oid for your case.+ if (linitial_node(RangeTblEntry, root->parse->rtable)->relid == 25634) + best_path = (Path *) create_material_path(final_rel, best_path);
Can we just directly add the material path on top of the best path? I
mean there are possibilities that we might not get any benefit of the
material because there is no duplicate from the outer node but we are
paying the cost of materialization right? The correct idea would be
that we should select this based on the cost comparison. Basically,
we can consider how many duplicates we have from the outer table
variable no?
--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com
3. I added the material path in a very hacked way, the if check just
to make
sure it take effect on my test statement only. If you want to test this
patch locally,
you need to change the oid for your case.
+ if (linitial_node(RangeTblEntry, root->parse->rtable)->relid ==
25634)
+ best_path = (Path *) create_material_path(final_rel,
best_path);
Can we just directly add the material path on top of the best path? I
mean there are possibilities that we might not get any benefit of the
material because there is no duplicate from the outer node but we are
paying the cost of materialization right? The correct idea would be
that we should select this based on the cost comparison. Basically,
we can consider how many duplicates we have from the outer table
variable no?
Thanks for interesting of it. Of course we can't add the material path on
best path,
that's why I say it is a very hacked way. and say "how to cost this
strategy is
challenge " (the part you striped when you reply the email). But we have
to
test a path first (it must be helpful on some case at least) and the
result is correct,
then we think about how to cost it. The purpose of my writing is about the
first step
and see what people think about it.
As for how to cost it, I'm agreed with your suggestion, but we may need
more
than that, like. (1, 2, 1) and (1, 1, 2) is same for your suggestion, but
they
are not different in this path. and we also may be think about if we can
get a lower cost if we add a new sort path.
Best Regards
Andy Fan
On Fri, Apr 24, 2020 at 2:42 PM Andy Fan <zhihui.fan1213@gmail.com> wrote:
3. I added the material path in a very hacked way, the if check just to make
sure it take effect on my test statement only. If you want to test this patch locally,
you need to change the oid for your case.+ if (linitial_node(RangeTblEntry, root->parse->rtable)->relid == 25634) + best_path = (Path *) create_material_path(final_rel, best_path);Can we just directly add the material path on top of the best path? I
mean there are possibilities that we might not get any benefit of the
material because there is no duplicate from the outer node but we are
paying the cost of materialization right? The correct idea would be
that we should select this based on the cost comparison. Basically,
we can consider how many duplicates we have from the outer table
variable no?Thanks for interesting of it. Of course we can't add the material path on best path,
that's why I say it is a very hacked way. and say "how to cost this strategy is
challenge " (the part you striped when you reply the email).
Right, I see that now. Thanks for pointing it out.
But we have to
test a path first (it must be helpful on some case at least) and the result is correct,
then we think about how to cost it. The purpose of my writing is about the first step
and see what people think about it.
Ok
As for how to cost it, I'm agreed with your suggestion, but we may need more
than that, like. (1, 2, 1) and (1, 1, 2) is same for your suggestion, but they
are not different in this path.
Valid point.
--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com
On Fri, 24 Apr 2020 at 15:26, Andy Fan <zhihui.fan1213@gmail.com> wrote:
Actually I have a different opinion to handle this issue, to execute the
a > (select avg(a) from tinner where x = touer.x); The drawback of current
path is because it may calculates the same touer.x value multi-times. So
if we cache the values we have calculated before, we can avoid the cost.
Material path may be the one we can reference but it assumes all the tuples
in the tuplestore matches the input params, which is not the fact here.But what if the input params doesn't change? If so we can use Material path
to optimize this case. But since we don't know if the if the input params changed
or not during plan time, we just add the path (let's assume we can add it with some
rules or cost calculation). If the input params is not changed, we use the cached
values, if the input params changed, we can ReScan the Material node. To optimize
the the cache invalidation frequent issue like (1, 2, 1, 2, 1, 2) case, we may consider
a sort path to change the input values to (1, 1, 1, 2, 2, 2). But overall it is a big effort.
This does not seem quite right to me. What you need is some sort of
parameterized materialize. Materialize just reads its subnode and
stores the entire thing input and reuses it any time that it
rescanned.
You likely need something more like what is mentioned in [1]/messages/by-id/CAKJS1f-kAk1cGVvzg9TXCLhPsxx_oFVOrTGSR5yTRXKWntTVFA@mail.gmail.com. There's
also a bunch of code from Heikki in the initial email in that thread.
Heikki put it in nodeSubplan.c. I think it should be a node of its
own.
David
[1]: /messages/by-id/CAKJS1f-kAk1cGVvzg9TXCLhPsxx_oFVOrTGSR5yTRXKWntTVFA@mail.gmail.com
On Fri, Apr 24, 2020 at 5:24 PM David Rowley <dgrowleyml@gmail.com> wrote:
On Fri, 24 Apr 2020 at 15:26, Andy Fan <zhihui.fan1213@gmail.com> wrote:
Actually I have a different opinion to handle this issue, to execute the
a > (select avg(a) from tinner where x = touer.x); The drawback ofcurrent
path is because it may calculates the same touer.x value multi-times. So
if we cache the values we have calculated before, we can avoid the cost.
Material path may be the one we can reference but it assumes all thetuples
in the tuplestore matches the input params, which is not the fact here.
But what if the input params doesn't change? If so we can use Material
path
to optimize this case. But since we don't know if the if the input
params changed
or not during plan time, we just add the path (let's assume we can add
it with some
rules or cost calculation). If the input params is not changed, we use
the cached
values, if the input params changed, we can ReScan the Material node.
To optimize
the the cache invalidation frequent issue like (1, 2, 1, 2, 1, 2) case,
we may consider
a sort path to change the input values to (1, 1, 1, 2, 2, 2). But
overall it is a big effort.
This does not seem quite right to me. What you need is some sort of
parameterized materialize. Materialize just reads its subnode and
stores the entire thing input and reuses it any time that it
rescanned.You likely need something more like what is mentioned in [1]. There's
also a bunch of code from Heikki in the initial email in that thread.
Heikki put it in nodeSubplan.c. I think it should be a node of its
own.
Glad to see your feedback, David:). Actually I thought about this idea
some
time ago, but since we have to implement a new path and handle
the cached data is too huge case, I gave it up later. When I am working
on some other stuff, I found Material path with some chgParam change may
get a no harmful improvement with less effort, based on we know how to
add the material path and we can always get a correct result.
I will check the link you provide when I get time, It's a nice feature and
it will be a
good place to continue working on that feature.
Best Regards
Andy Fan