Ashutosh Bapat писал 2021-04-14 16:01:
On Tue, Apr 13, 2021 at 6:58 PM Alexander Pyhalov
<a.pyha...@postgrespro.ru> wrote:
I believe step2 is needed to avoid materializing rows which will never
be selected. That would be a good improvement. However, care needs to
be taken for volatile quals. I think, the quals on CTE will be
evaluated twice, once when materializing the CTE result and second
time when scanning the materialized result. volatile quals may produce
different results when run multiple times.
Is there something else I miss?
Does somebody work on alternative solution or see issues in such
approach?
IMO, a POC patch will help understand your idea.
Hi.
I have a POC patch, which allows to distribute restrictinfos inside
CTEs.
However, I found I can't efficiently do partition pruning.
When CTE replan stage happens, plans are already done. I can create
alternative paths for relations,
for example, like in Try-prune-partitions patch.
However, new paths are not propagated to finalrel (UPPER_REL).
I'm not sure how to achieve this and need some advice.
Should we redo part of work, done by grouping_planner(), in the end of
SS_replan_ctes()?
Should we rely on executor partition pruning (with current patches it
doesn't work)?
Should we create init plans for ctes after grouping_planner(), not
before?
--
Best regards,
Alexander Pyhalov,
Postgres Professional
From df32be065c8c91cd132ccf7cbd1edc1862c7ac93 Mon Sep 17 00:00:00 2001
From: Alexander Pyhalov <a.pyha...@postgrespro.ru>
Date: Fri, 23 Apr 2021 15:18:21 +0300
Subject: [PATCH] Push down restrictinfos to CTE
---
src/backend/executor/functions.c | 1 +
src/backend/nodes/copyfuncs.c | 20 ++
src/backend/nodes/equalfuncs.c | 14 +
src/backend/nodes/outfuncs.c | 21 ++
src/backend/nodes/readfuncs.c | 1 +
src/backend/optimizer/plan/initsplan.c | 340 ++++++++++++++++++++++
src/backend/optimizer/plan/planner.c | 6 +
src/backend/optimizer/plan/subselect.c | 252 +++++++++++++++-
src/backend/optimizer/prep/prepjointree.c | 9 +
src/backend/parser/parse_relation.c | 1 +
src/backend/rewrite/rewriteHandler.c | 1 +
src/include/nodes/nodes.h | 1 +
src/include/nodes/parsenodes.h | 1 +
src/include/nodes/pathnodes.h | 21 ++
src/include/optimizer/planmain.h | 2 +
src/include/optimizer/subselect.h | 2 +
16 files changed, 691 insertions(+), 2 deletions(-)
diff --git a/src/backend/executor/functions.c b/src/backend/executor/functions.c
index 39580f7d577..7e8af62a954 100644
--- a/src/backend/executor/functions.c
+++ b/src/backend/executor/functions.c
@@ -1964,6 +1964,7 @@ tlist_coercion_finished:
rte = makeNode(RangeTblEntry);
rte->rtekind = RTE_SUBQUERY;
rte->subquery = parse;
+ rte->rtoffset = -1;
rte->eref = rte->alias = makeAlias("*SELECT*", colnames);
rte->lateral = false;
rte->inh = false;
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 632cc31a045..e46db5f0ce9 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -2373,6 +2373,22 @@ _copyRestrictInfo(const RestrictInfo *from)
return newnode;
}
+/*
+ * _copyRelRestrictInfos
+ */
+static RelRestrictInfos *
+_copyRelRestrictInfos(const RelRestrictInfos * from)
+{
+ RelRestrictInfos *newnode = makeNode(RelRestrictInfos);
+
+ /* root is used only as address */
+ COPY_SCALAR_FIELD(root);
+ COPY_SCALAR_FIELD(relid);
+ COPY_NODE_FIELD(restrictinfos);
+
+ return newnode;
+}
+
/*
* _copyPlaceHolderVar
*/
@@ -2467,6 +2483,7 @@ _copyRangeTblEntry(const RangeTblEntry *from)
COPY_NODE_FIELD(tablesample);
COPY_NODE_FIELD(subquery);
COPY_SCALAR_FIELD(security_barrier);
+ COPY_SCALAR_FIELD(rtoffset);
COPY_SCALAR_FIELD(jointype);
COPY_SCALAR_FIELD(joinmergedcols);
COPY_NODE_FIELD(joinaliasvars);
@@ -5286,6 +5303,9 @@ copyObjectImpl(const void *from)
case T_RestrictInfo:
retval = _copyRestrictInfo(from);
break;
+ case T_RelRestrictInfos:
+ retval = _copyRelRestrictInfos(from);
+ break;
case T_PlaceHolderVar:
retval = _copyPlaceHolderVar(from);
break;
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index a410a29a178..fea72ebed8f 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -863,6 +863,16 @@ _equalRestrictInfo(const RestrictInfo *a, const RestrictInfo *b)
return true;
}
+static bool
+_equalRelRestrictInfos(const RelRestrictInfos * a, const RelRestrictInfos * b)
+{
+ COMPARE_SCALAR_FIELD(root);
+ COMPARE_SCALAR_FIELD(relid);
+ COMPARE_NODE_FIELD(restrictinfos);
+
+ return true;
+}
+
static bool
_equalPlaceHolderVar(const PlaceHolderVar *a, const PlaceHolderVar *b)
{
@@ -2715,6 +2725,7 @@ _equalRangeTblEntry(const RangeTblEntry *a, const RangeTblEntry *b)
COMPARE_NODE_FIELD(tablesample);
COMPARE_NODE_FIELD(subquery);
COMPARE_SCALAR_FIELD(security_barrier);
+ COMPARE_SCALAR_FIELD(rtoffset);
COMPARE_SCALAR_FIELD(jointype);
COMPARE_SCALAR_FIELD(joinmergedcols);
COMPARE_NODE_FIELD(joinaliasvars);
@@ -3292,6 +3303,9 @@ equal(const void *a, const void *b)
case T_RestrictInfo:
retval = _equalRestrictInfo(a, b);
break;
+ case T_RelRestrictInfos:
+ retval = _equalRelRestrictInfos(a, b);
+ break;
case T_PlaceHolderVar:
retval = _equalPlaceHolderVar(a, b);
break;
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index c723f6d635f..96221581ad6 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -100,6 +100,11 @@ static void outChar(StringInfo str, char c);
#define WRITE_LOCATION_FIELD(fldname) \
appendStringInfo(str, " :" CppAsString(fldname) " %d", node->fldname)
+/* Write a field as an address */
+#define WRITE_ADDR_FIELD(fldname) \
+ appendStringInfo(str, " :" CppAsString(fldname) " %p", \
+ (void*) node->fldname)
+
/* Write a Node field */
#define WRITE_NODE_FIELD(fldname) \
(appendStringInfoString(str, " :" CppAsString(fldname) " "), \
@@ -2284,6 +2289,8 @@ _outPlannerInfo(StringInfo str, const PlannerInfo *node)
WRITE_INT_FIELD(join_cur_level);
WRITE_NODE_FIELD(init_plans);
WRITE_NODE_FIELD(cte_plan_ids);
+ WRITE_NODE_FIELD(cte_rel_restrictinfos);
+ WRITE_NODE_FIELD(use_cte_rel_restrictinfos);
WRITE_NODE_FIELD(multiexpr_params);
WRITE_NODE_FIELD(eq_classes);
WRITE_BOOL_FIELD(ec_merging_done);
@@ -2555,6 +2562,16 @@ _outRestrictInfo(StringInfo str, const RestrictInfo *node)
WRITE_OID_FIELD(hasheqoperator);
}
+static void
+_outRelRestrictInfos(StringInfo str, const RelRestrictInfos * node)
+{
+ WRITE_NODE_TYPE("RELRESTRICTINFOS");
+
+ WRITE_ADDR_FIELD(root);
+ WRITE_INT_FIELD(relid);
+ WRITE_NODE_FIELD(restrictinfos);
+}
+
static void
_outIndexClause(StringInfo str, const IndexClause *node)
{
@@ -3242,6 +3259,7 @@ _outRangeTblEntry(StringInfo str, const RangeTblEntry *node)
case RTE_SUBQUERY:
WRITE_NODE_FIELD(subquery);
WRITE_BOOL_FIELD(security_barrier);
+ WRITE_INT_FIELD(rtoffset);
break;
case RTE_JOIN:
WRITE_ENUM_FIELD(jointype, JoinType);
@@ -4284,6 +4302,9 @@ outNode(StringInfo str, const void *obj)
case T_RestrictInfo:
_outRestrictInfo(str, obj);
break;
+ case T_RelRestrictInfos:
+ _outRelRestrictInfos(str, obj);
+ break;
case T_IndexClause:
_outIndexClause(str, obj);
break;
diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c
index 3746668f526..c2b53a87b1d 100644
--- a/src/backend/nodes/readfuncs.c
+++ b/src/backend/nodes/readfuncs.c
@@ -1445,6 +1445,7 @@ _readRangeTblEntry(void)
case RTE_SUBQUERY:
READ_NODE_FIELD(subquery);
READ_BOOL_FIELD(security_barrier);
+ READ_INT_FIELD(rtoffset);
break;
case RTE_JOIN:
READ_ENUM_FIELD(jointype, JoinType);
diff --git a/src/backend/optimizer/plan/initsplan.c b/src/backend/optimizer/plan/initsplan.c
index 3ac853d9efc..3a172ae0f70 100644
--- a/src/backend/optimizer/plan/initsplan.c
+++ b/src/backend/optimizer/plan/initsplan.c
@@ -30,7 +30,9 @@
#include "optimizer/planner.h"
#include "optimizer/prep.h"
#include "optimizer/restrictinfo.h"
+#include "optimizer/subselect.h"
#include "parser/analyze.h"
+#include "parser/parsetree.h"
#include "rewrite/rewriteManip.h"
#include "utils/lsyscache.h"
#include "utils/typcache.h"
@@ -47,6 +49,17 @@ typedef struct PostponedQual
Relids relids; /* the set of baserels it references */
} PostponedQual;
+typedef struct
+{
+ bool safe;
+} restrictinfo_safe_context;
+
+typedef struct
+{
+ int old_varno;
+ int new_varno;
+} replace_varno_context;
+
static void extract_lateral_references(PlannerInfo *root, RelOptInfo *brel,
Index rtindex);
@@ -80,6 +93,10 @@ static void check_mergejoinable(RestrictInfo *restrictinfo);
static void check_hashjoinable(RestrictInfo *restrictinfo);
static void check_resultcacheable(RestrictInfo *restrictinfo);
+static void add_cte_restrictinfo(PlannerInfo *root, RestrictInfo *restrictinfo, RelOptInfo *rel);
+static void distribute_baserestrictinfo_to_childs(PlannerInfo *root, RelOptInfo *rel);
+
+
/*****************************************************************************
*
@@ -2164,6 +2181,234 @@ check_redundant_nullability_qual(PlannerInfo *root, Node *clause)
return false;
}
+/*
+ * find_cte
+ * Find cte, corresponding to rel at root or upper level
+ */
+static PlannerInfo *
+find_cte(PlannerInfo *root, RelOptInfo *rel, int *cteindex)
+{
+ RangeTblEntry *rte;
+ Index levelsup;
+ PlannerInfo *cteroot;
+ ListCell *lc;
+ int ind;
+
+ cteroot = NULL;
+ *cteindex = -1;
+ if (IS_SIMPLE_REL(rel))
+ {
+ rte = planner_rt_fetch(rel->relid, root);
+
+ Assert(rte->rtekind == RTE_CTE);
+ if (rte->self_reference)
+ /* Can't pushdown quals to recursive CTE */
+ return NULL;
+
+ levelsup = rte->ctelevelsup;
+ cteroot = root;
+
+ while (levelsup-- > 0)
+ {
+ cteroot = cteroot->parent_root;
+ if (!cteroot) /* shouldn't happen */
+ elog(ERROR, "bad levelsup for CTE \"%s\"", rte->ctename);
+ }
+
+ ind = 0;
+ foreach(lc, cteroot->parse->cteList)
+ {
+ CommonTableExpr *cte = (CommonTableExpr *) lfirst(lc);
+
+ if (strcmp(cte->ctename, rte->ctename) == 0)
+ break;
+ ind++;
+ }
+ if (lc == NULL) /* shouldn't happen */
+ elog(ERROR, "could not find CTE \"%s\"", rte->ctename);
+ *cteindex = ind;
+ }
+ return cteroot;
+}
+
+static bool
+is_safe_restrictinfo_walker(Node *node, restrictinfo_safe_context * context)
+{
+ if (node == NULL)
+ return false;
+ if (IsA(node, Var))
+ {
+ Var *var = (Var *) node;
+
+ if (IS_SPECIAL_VARNO(var->varno))
+ context->safe = false;
+ /* Don't support whole-tuple reference yet */
+ if (var->varattno == InvalidAttrNumber)
+ context->safe = false;
+
+ return false;
+ }
+ if (IsA(node, PlaceHolderVar))
+ {
+ context->safe = false;
+ return false;
+ }
+ if (IsA(node, Query))
+ {
+ /* is it possible ? */
+ context->safe = false;
+ return false;
+ }
+ return expression_tree_walker(node, is_safe_restrictinfo_walker, context);
+}
+
+/*
+ * is_safe_restrictinfo
+ * RestrictInfo is safe if it contains non-volatile functions,
+ * non-special vars or constants
+ */
+static bool
+is_safe_restrictinfo(RestrictInfo *restrictinfo)
+{
+ Node *clause;
+ restrictinfo_safe_context context;
+
+ clause = (Node *) restrictinfo->clause;
+ if (contain_volatile_functions(clause))
+ return false;
+
+ context.safe = true;
+
+ is_safe_restrictinfo_walker(clause, &context);
+
+ return context.safe;
+}
+
+/*
+ * replace_varno_walker
+ * Replaces varnos based on given value
+ */
+static bool
+replace_varno_walker(Node *node, replace_varno_context * context)
+{
+ if (node == NULL)
+ return false;
+ if (IsA(node, Var))
+ {
+ Var *var = (Var *) node;
+
+ if (var->varno == context->old_varno)
+ var->varno = context->new_varno;
+
+ return false;
+ }
+ if (IsA(node, Query))
+ {
+ return query_tree_walker((Query *) node, replace_varno_walker, context, 0);
+ }
+ return expression_tree_walker(node, replace_varno_walker, context);
+}
+
+/*
+ * add_restrictinfo_to_rel_restrictinfos
+ * Find RelRestrictInfo for root and rel, add restrictinfo there
+ */
+static void
+add_restrictinfo_to_rel_restrictinfos(List **relrestrictinfos, PlannerInfo *root, int relid, RestrictInfo *restrictinfo)
+{
+ ListCell *lc;
+ RelRestrictInfos *rri = NULL;
+
+ foreach(lc, *relrestrictinfos)
+ {
+ RelRestrictInfos *cur_ri = (RelRestrictInfos *) lfirst(lc);
+
+ if (cur_ri->root == root && cur_ri->relid == relid)
+ {
+ rri = cur_ri;
+ break;
+ }
+ }
+
+ if (rri == NULL)
+ {
+ rri = makeNode(RelRestrictInfos);
+ rri->root = root;
+ rri->relid = relid;
+ *relrestrictinfos = lappend(*relrestrictinfos, rri);
+ }
+
+ rri->restrictinfos = lappend(rri->restrictinfos, restrictinfo);
+
+}
+
+/*
+ * add_cte_restrictinfo
+ * Find cte, corresponding to rel and add restrict info to its root's cte_rel_restrictinfos
+ */
+static void
+add_cte_restrictinfo(PlannerInfo *root, RestrictInfo *restrictinfo, RelOptInfo *rel)
+{
+ int cteindex;
+ PlannerInfo *cteroot;
+
+ cteroot = find_cte(root, rel, &cteindex);
+
+ if (cteroot != NULL && cteindex >= 0)
+ {
+ CommonTableExpr *cte;
+ ListCell *lc;
+
+ bool restrictinfo_usage_safe = false;
+
+ restrictinfo_usage_safe = list_nth_int(cteroot->use_cte_rel_restrictinfos, cteindex);
+
+ if (!restrictinfo_usage_safe)
+ return;
+
+ restrictinfo_usage_safe = is_safe_restrictinfo(restrictinfo);
+
+ if (!restrictinfo_usage_safe)
+ {
+ lc = list_nth_cell(cteroot->use_cte_rel_restrictinfos, cteindex);
+ lc->int_value = false;
+ return;
+ }
+
+ cte = list_nth(cteroot->parse->cteList, cteindex);
+
+ /*
+ * Shouldn't depend on restrictinfo here to avoid different decisions
+ * for different clauses
+ */
+ if (cte->ctematerialized == CTEMaterializeDefault &&
+ cte->cterefcount > 1 &&
+ !cte->cterecursive &&
+ ((Query *) cte->ctequery)->commandType == CMD_SELECT &&
+ !contain_dml(cte->ctequery))
+ {
+
+ RestrictInfo *restrictinfo_copy;
+ replace_varno_context rvcontext;
+
+ restrictinfo_copy = copyObject(restrictinfo);
+
+ /*
+ * All varnos in restrictinfo should reference CTE targetlist. We
+ * never use varno later, so just change varno corresponding to
+ * rel to 0.
+ */
+ rvcontext.old_varno = rel->relid;
+ rvcontext.new_varno = 0;
+ replace_varno_walker((Node *) (restrictinfo_copy->clause), &rvcontext);
+
+ lc = list_nth_cell(cteroot->cte_rel_restrictinfos, cteindex);
+ add_restrictinfo_to_rel_restrictinfos((List **) &(lc->ptr_value), root, rel->relid, restrictinfo_copy);
+ }
+
+ }
+}
+
/*
* distribute_restrictinfo_to_rels
* Push a completed RestrictInfo into the proper restriction or join
@@ -2196,6 +2441,9 @@ distribute_restrictinfo_to_rels(PlannerInfo *root,
/* Update security level info */
rel->baserestrict_min_security = Min(rel->baserestrict_min_security,
restrictinfo->security_level);
+
+ if (rel->rtekind == RTE_CTE)
+ add_cte_restrictinfo(root, restrictinfo, rel);
break;
case BMS_MULTIPLE:
@@ -2233,6 +2481,98 @@ distribute_restrictinfo_to_rels(PlannerInfo *root,
}
}
+/*
+ * replan_distribute_restrictinfo_to_rels
+ * Push a completed RestrictInfo into the proper restriction or join
+ * clause list(s).
+ *
+ * This function is used on cte replan stage.
+ * It doesn't extract CTE restrictions and also distributes RestrictInfo
+ * to childs.
+ */
+void
+replan_distribute_restrictinfo_to_rels(PlannerInfo *root,
+ RestrictInfo *restrictinfo)
+{
+ Relids relids = restrictinfo->required_relids;
+ RelOptInfo *rel;
+
+ switch (bms_membership(relids))
+ {
+ case BMS_SINGLETON:
+
+ /*
+ * There is only one relation participating in the clause, so it
+ * is a restriction clause for that relation.
+ */
+ rel = find_base_rel(root, bms_singleton_member(relids));
+
+ /* Add clause to rel's restriction list */
+ rel->baserestrictinfo = lappend(rel->baserestrictinfo,
+ restrictinfo);
+ /* Update security level info */
+ rel->baserestrict_min_security = Min(rel->baserestrict_min_security,
+ restrictinfo->security_level);
+
+ if ((rel->rtekind == RTE_RELATION) && (rel->nparts > 0))
+ distribute_baserestrictinfo_to_childs(root, rel);
+ break;
+ case BMS_MULTIPLE:
+ /* How should we handle partitions in this case? */
+
+ /*
+ * The clause is a join clause, since there is more than one rel
+ * in its relid set.
+ */
+
+ /*
+ * Check for hashjoinable operators. (We don't bother setting the
+ * hashjoin info except in true join clauses.)
+ */
+ check_hashjoinable(restrictinfo);
+
+ /*
+ * Add clause to the join lists of all the relevant relations.
+ */
+ add_join_clause_to_rels(root, restrictinfo, relids);
+ break;
+ default: /* BMS_EMPTY_SET */
+
+ /*
+ * Can get here, if after targetlist substitution get no
+ * references to rels. Just do nothing in such case for now.
+ */
+ elog(DEBUG1, "got rel-free clause on CTE replan stage");
+ break;
+ }
+}
+
+static void
+distribute_baserestrictinfo_to_childs(PlannerInfo *root, RelOptInfo *rel)
+{
+ int part;
+ RangeTblEntry *rte;
+
+ for (part = 0; part < rel->nparts; part++)
+ {
+ RelOptInfo *child_rel = rel->part_rels[part];
+
+ if (child_rel->rtekind == RTE_RELATION)
+ {
+ int childRTindex = child_rel->relid;
+ AppendRelInfo *appinfo = root->append_rel_array[childRTindex];
+
+ Assert(appinfo != NULL);
+
+ rte = root->simple_rte_array[childRTindex];
+
+ apply_child_basequals(root, rel, child_rel, rte, appinfo);
+ /* How can we process dummy relations */
+ }
+
+ }
+}
+
/*
* process_implied_equality
* Create a restrictinfo item that says "item1 op item2", and push it
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 1868c4eff47..34e0c4344f1 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -610,6 +610,8 @@ subquery_planner(PlannerGlobal *glob, Query *parse,
root->planner_cxt = CurrentMemoryContext;
root->init_plans = NIL;
root->cte_plan_ids = NIL;
+ root->cte_rel_restrictinfos = NIL;
+ root->use_cte_rel_restrictinfos = NIL;
root->multiexpr_params = NIL;
root->eq_classes = NIL;
root->ec_merging_done = false;
@@ -1018,6 +1020,10 @@ subquery_planner(PlannerGlobal *glob, Query *parse,
*/
grouping_planner(root, tuple_fraction);
+ if (parse->cteList)
+ SS_replan_ctes(root);
+
+
/*
* Capture the set of outer-level param IDs we have access to, for use in
* extParam/allParam calculations later.
diff --git a/src/backend/optimizer/plan/subselect.c b/src/backend/optimizer/plan/subselect.c
index 0881a208acf..b3895f69be5 100644
--- a/src/backend/optimizer/plan/subselect.c
+++ b/src/backend/optimizer/plan/subselect.c
@@ -31,8 +31,10 @@
#include "optimizer/planmain.h"
#include "optimizer/planner.h"
#include "optimizer/prep.h"
+#include "optimizer/restrictinfo.h"
#include "optimizer/subselect.h"
#include "parser/parse_relation.h"
+#include "parser/parsetree.h"
#include "rewrite/rewriteManip.h"
#include "utils/builtins.h"
#include "utils/lsyscache.h"
@@ -85,7 +87,6 @@ static bool subpath_is_hashable(Path *path);
static bool testexpr_is_hashable(Node *testexpr, List *param_ids);
static bool test_opexpr_is_hashable(OpExpr *testexpr, List *param_ids);
static bool hash_ok_operator(OpExpr *expr);
-static bool contain_dml(Node *node);
static bool contain_dml_walker(Node *node, void *context);
static bool contain_outer_selfref(Node *node);
static bool contain_outer_selfref_walker(Node *node, Index *depth);
@@ -104,6 +105,7 @@ static Bitmapset *finalize_plan(PlannerInfo *root,
Bitmapset *scan_params);
static bool finalize_primnode(Node *node, finalize_primnode_context *context);
static bool finalize_agg_primnode(Node *node, finalize_primnode_context *context);
+static Node *replan_ctes_replace_variables_callback(Var *var, replace_rte_variables_context *context);
/*
@@ -896,6 +898,8 @@ SS_process_ctes(PlannerInfo *root)
ListCell *lc;
Assert(root->cte_plan_ids == NIL);
+ Assert(root->cte_rel_restrictinfos == NIL);
+ Assert(root->use_cte_rel_restrictinfos == NIL);
foreach(lc, root->parse->cteList)
{
@@ -909,6 +913,11 @@ SS_process_ctes(PlannerInfo *root)
SubPlan *splan;
int paramid;
+ /* Add empty list of RelRestrictInfos lists for this cte */
+ root->cte_rel_restrictinfos = lappend(root->cte_rel_restrictinfos, NIL);
+ /* Mark cte_rel_restrictinfos safe to use if present */
+ root->use_cte_rel_restrictinfos = lappend_int(root->use_cte_rel_restrictinfos, true);
+
/*
* Ignore SELECT CTEs that are not actually referenced anywhere.
*/
@@ -1062,12 +1071,250 @@ SS_process_ctes(PlannerInfo *root)
}
}
+/*
+ * SS_replan_ctes: replan ctes if cte_rel_restrictinfos has additional quals
+ */
+void
+SS_replan_ctes(PlannerInfo *root)
+{
+ ListCell *lc1,
+ *lc2;
+ int ind;
+ RestrictInfo *restrictinfo;
+ CommonTableExpr *cte;
+ Query *query;
+ int planid;
+ Plan *plan;
+ PlannerInfo *subroot;
+ RelOptInfo *final_rel;
+ Path *best_path;
+
+ ind = -1;
+ foreach(lc1, root->use_cte_rel_restrictinfos)
+ {
+ ind++;
+
+ if (lfirst_int(lc1))
+ {
+ List *rel_restrictinfos;
+
+ rel_restrictinfos = list_nth(root->cte_rel_restrictinfos, ind);
+
+ cte = (CommonTableExpr *) list_nth(root->parse->cteList, ind);
+
+ Assert(cte != NULL);
+
+ /*
+ * We can restrict cte only when all queries, referencing it,
+ * restrict it in some way.
+ */
+ if (rel_restrictinfos != NULL && cte->cterefcount == list_length(rel_restrictinfos))
+ {
+ List *exprs,
+ *orlist = NIL;
+ ListCell *lc_rris;
+ ListCell *lc_restrictinfo;
+ bool replan_needed = false;
+ Expr *resexp = NULL;
+ Index security_level = UINT_MAX;
+
+ planid = list_nth_int(root->cte_plan_ids, ind);
+ Assert(planid != -1);
+ subroot = (PlannerInfo *) list_nth(root->glob->subroots, ind);
+ Assert(subroot != NULL);
+
+ query = (Query *) cte->ctequery;
+
+ foreach(lc_rris, rel_restrictinfos)
+ {
+ Expr *andexp = NULL;
+ List *andlist = NIL;
+
+ RelRestrictInfos *rri = (RelRestrictInfos *) lfirst(lc_rris);
+
+ foreach(lc_restrictinfo, rri->restrictinfos)
+ {
+ RestrictInfo *ri = (RestrictInfo *) lfirst(lc_restrictinfo);
+
+ andlist = lappend(andlist, ri->clause);
+
+ if (ri->security_level < security_level)
+ security_level = ri->security_level;
+ }
+
+ Assert(list_length(andlist) > 0);
+
+ if (list_length(andlist) > 1)
+ andexp = makeBoolExpr(AND_EXPR, andlist, -1);
+ else
+ andexp = linitial(andlist);
+
+ orlist = lappend(orlist, andexp);
+ }
+
+ Assert(list_length(orlist) > 0);
+
+ if (list_length(orlist) > 1)
+ resexp = makeBoolExpr(OR_EXPR, orlist, -1);
+ else
+ resexp = linitial(orlist);
+
+ resexp = canonicalize_qual(resexp, false);
+ /*
+ * We already know that all expressions refrence only one
+ * table. We can't just create restrictinfo from the clause
+ * and push it down, as it can be an and expression. So we
+ * decompose it first and check that we can push down every
+ * clause.
+ */
+ exprs = make_ands_implicit(resexp);
+
+ if (exprs == NIL)
+ {
+ /* Got explicit true, avoid pushing anything */
+ elog(DEBUG1, "clause seems to be always true, refusing to push down qual to CTE \"%s\"", cte->ctename);
+ continue;
+ }
+
+ foreach(lc2, exprs)
+ {
+ Expr *expr = (Expr *) lfirst(lc2);
+ Relids relids;
+ int relid;
+
+ relids = pull_varnos(NULL, (Node *) expr);
+
+ if (bms_membership(relids) != BMS_SINGLETON)
+ {
+ elog(DEBUG1, "The clause seems to be unsafe, refusing to push down qual to CTE \"%s\"", cte->ctename);
+ continue;
+ }
+
+ relid = bms_singleton_member(relids);
+
+ /* usable expression doesn't have sublinks */
+ expr = (Expr *) replace_rte_variables((Node *) expr, relid, 0, replan_ctes_replace_variables_callback,
+ query->targetList, false);
+
+ /*
+ * After replacing relids we should again check that
+ * expression refer only to one rel
+ */
+ relids = pull_varnos(NULL, (Node *) expr);
+ if (bms_membership(relids) != BMS_SINGLETON)
+ {
+ elog(DEBUG1, "The clause seems to be unsafe, refusing to push down qual to CTE \"%s\"", cte->ctename);
+ continue;
+ }
+
+ if (contain_volatile_functions((Node *) expr))
+ {
+ elog(DEBUG1, "The clause seems to be unsafe, refusing to push down qual to CTE \"%s\"", cte->ctename);
+ continue;
+ }
+
+ relid = bms_singleton_member(relids);
+ if (relid < 0 || relid > subroot->simple_rel_array_size)
+ {
+ elog(DEBUG1, "The clause seems to be unsafe, refusing to push down qual to CTE \"%s\"", cte->ctename);
+ continue;
+ }
+
+ /*
+ * Try to push down qual after subquery pull up
+ */
+ if (subroot->simple_rel_array[relid] == NULL)
+ {
+ RangeTblEntry *rte = subroot->simple_rte_array[relid];
+
+ if ((rte != NULL) && rte->rtekind == RTE_SUBQUERY && rte->rtoffset >= 0 && rte->subquery != NULL && rte->subquery->targetList != NULL)
+ {
+ Query *subquery;
+
+ subquery = copyObject(rte->subquery);
+
+ OffsetVarNodes((Node *) subquery->targetList, rte->rtoffset, 0);
+
+ expr = (Expr *) replace_rte_variables((Node *) expr, relid, 0, replan_ctes_replace_variables_callback,
+ subquery->targetList, false);
+
+ relids = pull_varnos(NULL, (Node *) expr);
+ if (bms_membership(relids) != BMS_SINGLETON)
+ {
+ elog(DEBUG1, "The clause seems to be unsafe, refusing to push down qual to CTE \"%s\"", cte->ctename);
+ continue;
+ }
+
+ if (contain_volatile_functions((Node *) expr))
+ {
+ elog(DEBUG1, "The clause seems to be unsafe, refusing to push down qual to CTE \"%s\"", cte->ctename);
+ continue;
+ }
+ }
+ else
+ {
+ elog(DEBUG1, "The clause seems to be unsafe, refusing to push down qual to CTE \"%s\"", cte->ctename);
+ continue;
+ }
+ }
+
+ /* If we are here, we have to rebuild plan */
+ replan_needed = true;
+
+ restrictinfo = make_restrictinfo(subroot, expr, true, false, false, security_level, NULL, NULL, NULL);
+
+ /* Push restrictinfo */
+ replan_distribute_restrictinfo_to_rels(subroot, restrictinfo);
+
+ }
+
+ /* replan */
+ if (replan_needed)
+ {
+ final_rel = fetch_upper_rel(subroot, UPPERREL_FINAL, NULL);
+ best_path = final_rel->cheapest_total_path;
+ plan = create_plan(subroot, best_path);
+
+ /* Save plan instead of old one */
+ lc2 = list_nth_cell(root->glob->subplans, ind);
+ lfirst(lc2) = plan;
+ /* Should we somehow clean up old plan ? */
+ }
+ }
+ }
+ }
+}
+
+static Node *
+replan_ctes_replace_variables_callback(Var *var,
+ replace_rte_variables_context *context)
+{
+ Node *newnode;
+ List *tlist = (List *) context->callback_arg;
+ int varattno = var->varattno;
+ TargetEntry *tle;
+
+ tle = get_tle_by_resno(tlist, varattno);
+
+ if (tle == NULL) /* shouldn't happen */
+ elog(ERROR, "could not find attribute %d in CTE subquery targetlist",
+ varattno);
+
+ newnode = (Node *) copyObject(tle->expr);
+
+ /*
+ * Should we do something with var->varlevelsup or call
+ * IncrementVarSublevelsUp(node)?
+ */
+ return newnode;
+}
+
/*
* contain_dml: is any subquery not a plain SELECT?
*
* We reject SELECT FOR UPDATE/SHARE as well as INSERT etc.
*/
-static bool
+bool
contain_dml(Node *node)
{
return contain_dml_walker(node, NULL);
@@ -1218,6 +1465,7 @@ inline_cte_walker(Node *node, inline_cte_walker_context *context)
rte->rtekind = RTE_SUBQUERY;
rte->subquery = newquery;
rte->security_barrier = false;
+ rte->rtoffset = -1;
/* Zero out CTE-specific fields */
rte->ctename = NULL;
diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c
index 62a16687963..4be4190b963 100644
--- a/src/backend/optimizer/prep/prepjointree.c
+++ b/src/backend/optimizer/prep/prepjointree.c
@@ -660,6 +660,7 @@ preprocess_function_rtes(PlannerInfo *root)
rte->rtekind = RTE_SUBQUERY;
rte->subquery = funcquery;
rte->security_barrier = false;
+ rte->rtoffset = -1;
/* Clear fields that should not be set in a subquery RTE */
rte->functions = NIL;
rte->funcordinality = false;
@@ -917,6 +918,8 @@ pull_up_simple_subquery(PlannerInfo *root, Node *jtnode, RangeTblEntry *rte,
subroot->planner_cxt = CurrentMemoryContext;
subroot->init_plans = NIL;
subroot->cte_plan_ids = NIL;
+ subroot->cte_rel_restrictinfos = NIL;
+ subroot->use_cte_rel_restrictinfos = NIL;
subroot->multiexpr_params = NIL;
subroot->eq_classes = NIL;
subroot->ec_merging_done = false;
@@ -1014,6 +1017,12 @@ pull_up_simple_subquery(PlannerInfo *root, Node *jtnode, RangeTblEntry *rte,
* well.
*/
rtoffset = list_length(parse->rtable);
+
+ /*
+ * save rtoffset in RTE to be able to map rte->subquery varnos back to
+ * root varnos
+ */
+ rte->rtoffset = rtoffset;
OffsetVarNodes((Node *) subquery, rtoffset, 0);
OffsetVarNodes((Node *) subroot->append_rel_list, rtoffset, 0);
diff --git a/src/backend/parser/parse_relation.c b/src/backend/parser/parse_relation.c
index d451f055f72..b8508867b17 100644
--- a/src/backend/parser/parse_relation.c
+++ b/src/backend/parser/parse_relation.c
@@ -1590,6 +1590,7 @@ addRangeTableEntryForSubquery(ParseState *pstate,
rte->rtekind = RTE_SUBQUERY;
rte->subquery = subquery;
rte->alias = alias;
+ rte->rtoffset = -1;
eref = copyObject(alias);
numaliases = list_length(eref->colnames);
diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c
index 497d30d8a93..568104f8282 100644
--- a/src/backend/rewrite/rewriteHandler.c
+++ b/src/backend/rewrite/rewriteHandler.c
@@ -1853,6 +1853,7 @@ ApplyRetrieveRule(Query *parsetree,
rte->rtekind = RTE_SUBQUERY;
rte->subquery = rule_action;
rte->security_barrier = RelationIsSecurityView(relation);
+ rte->rtoffset = -1;
/* Clear fields that should not be set in a subquery RTE */
rte->relid = InvalidOid;
rte->relkind = 0;
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
index d9e417bcd70..5e7254c9f72 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -269,6 +269,7 @@ typedef enum NodeTag
T_PathKey,
T_PathTarget,
T_RestrictInfo,
+ T_RelRestrictInfos,
T_IndexClause,
T_PlaceHolderVar,
T_SpecialJoinInfo,
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 615dfa26aa2..5cc8cd46e6d 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1029,6 +1029,7 @@ typedef struct RangeTblEntry
*/
Query *subquery; /* the sub-query */
bool security_barrier; /* is from security_barrier view? */
+ int rtoffset; /* rtoffset when subquery is pulled up */
/*
* Fields valid for a join RTE (else NULL/zero):
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index a65bda7e3c6..d578cc003ba 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -242,6 +242,13 @@ struct PlannerInfo
List *cte_plan_ids; /* per-CTE-item list of subplan IDs */
+ List *cte_rel_restrictinfos; /* per-CTE list of RelRestrictInfos to
+ * push down, NIL by default */
+
+ List *use_cte_rel_restrictinfos; /* per-CTE flag to allow pushing
+ * corresponding RestrictInfo,
+ * true by default */
+
List *multiexpr_params; /* List of Lists of Params for MULTIEXPR
* subquery outputs */
@@ -2115,6 +2122,20 @@ typedef struct RestrictInfo
Oid hasheqoperator;
} RestrictInfo;
+/*
+ * Extracted clauses for one RTE, mentioning CTE
+ * (root, relid) pair is a key in a list of RelRestrictInfos
+ */
+typedef struct RelRestrictInfos
+{
+ NodeTag type;
+
+ PlannerInfo *root;
+ int relid;
+
+ List *restrictinfos;
+} RelRestrictInfos;
+
/*
* This macro embodies the correct way to test whether a RestrictInfo is
* "pushed down" to a given outer join, that is, should be treated as a filter
diff --git a/src/include/optimizer/planmain.h b/src/include/optimizer/planmain.h
index bf1adfc52ac..668d7e26a10 100644
--- a/src/include/optimizer/planmain.h
+++ b/src/include/optimizer/planmain.h
@@ -77,6 +77,8 @@ extern void create_lateral_join_info(PlannerInfo *root);
extern List *deconstruct_jointree(PlannerInfo *root);
extern void distribute_restrictinfo_to_rels(PlannerInfo *root,
RestrictInfo *restrictinfo);
+extern void replan_distribute_restrictinfo_to_rels(PlannerInfo *root,
+ RestrictInfo *restrictinfo);
extern RestrictInfo *process_implied_equality(PlannerInfo *root,
Oid opno,
Oid collation,
diff --git a/src/include/optimizer/subselect.h b/src/include/optimizer/subselect.h
index 059bdf941ef..47bafbcdab3 100644
--- a/src/include/optimizer/subselect.h
+++ b/src/include/optimizer/subselect.h
@@ -16,7 +16,9 @@
#include "nodes/pathnodes.h"
#include "nodes/plannodes.h"
+extern bool contain_dml(Node *node);
extern void SS_process_ctes(PlannerInfo *root);
+extern void SS_replan_ctes(PlannerInfo *root);
extern JoinExpr *convert_ANY_sublink_to_join(PlannerInfo *root,
SubLink *sublink,
Relids available_rels);
--
2.25.1
From 408ba4affd8660f28d874e9c0e89b2020a9753bd Mon Sep 17 00:00:00 2001
From: Alexander Pyhalov <a.pyha...@postgrespro.ru>
Date: Fri, 23 Apr 2021 16:16:53 +0300
Subject: [PATCH 2/2] Try prune partitions
---
src/backend/optimizer/plan/initsplan.c | 43 ++++++++++++++++++++++++--
src/backend/optimizer/plan/subselect.c | 10 ++++++
2 files changed, 50 insertions(+), 3 deletions(-)
diff --git a/src/backend/optimizer/plan/initsplan.c b/src/backend/optimizer/plan/initsplan.c
index 3a172ae0f70..7421be1cfef 100644
--- a/src/backend/optimizer/plan/initsplan.c
+++ b/src/backend/optimizer/plan/initsplan.c
@@ -33,6 +33,7 @@
#include "optimizer/subselect.h"
#include "parser/analyze.h"
#include "parser/parsetree.h"
+#include "partitioning/partprune.h"
#include "rewrite/rewriteManip.h"
#include "utils/lsyscache.h"
#include "utils/typcache.h"
@@ -2551,11 +2552,25 @@ static void
distribute_baserestrictinfo_to_childs(PlannerInfo *root, RelOptInfo *rel)
{
int part;
+ bool set_dummy = false;
RangeTblEntry *rte;
+ Bitmapset *live_parts;
+ List *live_childrels = NIL;
+
+ live_parts = prune_append_rel_partitions(rel);
for (part = 0; part < rel->nparts; part++)
{
- RelOptInfo *child_rel = rel->part_rels[part];
+ RelOptInfo *child_rel;
+
+ child_rel = rel->part_rels[part];
+
+ if (!bms_is_member(part ,live_parts))
+ {
+ mark_dummy_rel(child_rel);
+ set_dummy = true;
+ continue;
+ }
if (child_rel->rtekind == RTE_RELATION)
{
@@ -2566,10 +2581,32 @@ distribute_baserestrictinfo_to_childs(PlannerInfo *root, RelOptInfo *rel)
rte = root->simple_rte_array[childRTindex];
- apply_child_basequals(root, rel, child_rel, rte, appinfo);
- /* How can we process dummy relations */
+ if (!apply_child_basequals(root, rel, child_rel, rte, appinfo))
+ {
+ mark_dummy_rel(child_rel);
+ set_dummy = true;
+ continue;
+ }
}
+ child_rel->partial_pathlist = NIL;
+ child_rel->consider_parallel = false;
+
+ /* Relation is not dummy */
+ live_childrels = lappend(live_childrels, child_rel);
+
+ }
+
+ if (set_dummy)
+ {
+ /* Partial paths are harmful for initplans */
+ rel->consider_parallel = false;
+
+ add_paths_to_append_rel(root, rel, live_childrels);
+
+ rel->partial_pathlist = NIL;
+
+ set_cheapest(rel);
}
}
diff --git a/src/backend/optimizer/plan/subselect.c b/src/backend/optimizer/plan/subselect.c
index b3895f69be5..e10ffa5756e 100644
--- a/src/backend/optimizer/plan/subselect.c
+++ b/src/backend/optimizer/plan/subselect.c
@@ -1272,6 +1272,16 @@ SS_replan_ctes(PlannerInfo *root)
if (replan_needed)
{
final_rel = fetch_upper_rel(subroot, UPPERREL_FINAL, NULL);
+
+ /*
+ * Forget about any partial paths and clear consider_parallel, too;
+ * they're not usable if we attached an initPlan (all CTEs are).
+ */
+ final_rel->partial_pathlist = NIL;
+ final_rel->consider_parallel = false;
+
+ set_cheapest(final_rel);
+
best_path = final_rel->cheapest_total_path;
plan = create_plan(subroot, best_path);
--
2.25.1
