runtime_prune_drowley_v6.patch
application/octet-stream
Filename: runtime_prune_drowley_v6.patch
Type: application/octet-stream
Part: 0
Patch
Format: unified
Series: patch v6
| File | + | − |
|---|---|---|
| src/backend/catalog/partition.c | 222 | 26 |
| src/backend/executor/nodeAppend.c | 225 | 25 |
| src/backend/nodes/bitmapset.c | 95 | 0 |
| src/backend/nodes/copyfuncs.c | 31 | 0 |
| src/backend/optimizer/path/allpaths.c | 50 | 20 |
| src/backend/optimizer/path/joinrels.c | 1 | 1 |
| src/backend/optimizer/plan/createplan.c | 63 | 5 |
| src/backend/optimizer/plan/planner.c | 2 | 1 |
| src/backend/optimizer/plan/setrefs.c | 2 | 2 |
| src/backend/optimizer/prep/prepunion.c | 2 | 2 |
| src/backend/optimizer/util/clauses.c | 19 | 0 |
| src/backend/optimizer/util/pathnode.c | 34 | 3 |
| src/backend/optimizer/util/relnode.c | 3 | 0 |
| src/include/catalog/partition.h | 6 | 0 |
| src/include/nodes/bitmapset.h | 1 | 0 |
| src/include/nodes/execnodes.h | 6 | 0 |
| src/include/nodes/nodes.h | 1 | 0 |
| src/include/nodes/plannodes.h | 8 | 0 |
| src/include/nodes/primnodes.h | 25 | 0 |
| src/include/nodes/relation.h | 5 | 0 |
| src/include/optimizer/clauses.h | 3 | 0 |
| src/include/optimizer/pathnode.h | 1 | 1 |
| src/include/optimizer/paths.h | 6 | 0 |
| src/test/regress/expected/partition_prune.out | 956 | 0 |
| src/test/regress/sql/partition_prune.sql | 252 | 0 |
diff --git a/src/backend/catalog/partition.c b/src/backend/catalog/partition.c
index 8066237..36de96e 100644
--- a/src/backend/catalog/partition.c
+++ b/src/backend/catalog/partition.c
@@ -34,11 +34,13 @@
#include "catalog/pg_type.h"
#include "commands/tablecmds.h"
#include "executor/executor.h"
+#include "executor/nodeSubplan.h"
#include "miscadmin.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
#include "nodes/parsenodes.h"
#include "optimizer/clauses.h"
+#include "optimizer/pathnode.h"
#include "optimizer/planmain.h"
#include "optimizer/planner.h"
#include "optimizer/predtest.h"
@@ -290,25 +292,33 @@ static uint64 compute_hash_value(PartitionKey key, Datum *values, bool *isnull);
PG_FUNCTION_INFO_V1(satisfies_hash_partition);
static Bitmapset *get_partitions_from_clauses_recurse(Relation relation,
- int rt_index, List *clauses);
+ int rt_index, ParamListInfo prmlist,
+ ExprContext *econtext, List *clauses);
static Bitmapset *get_partitions_from_ne_clauses(Relation relation,
- List *ne_clauses);
+ ParamListInfo prmlist,
+ ExprContext *econtext,
+ List *ne_clauses);
static Bitmapset *get_partitions_from_or_clause_args(Relation relation,
- int rt_index, List *or_clause_args);
+ int rt_index, ParamListInfo prmlist,
+ ExprContext *econtext, List *or_clause_args);
static int classify_partition_bounding_keys(Relation relation, List *clauses,
int rt_index,
- PartScanKeyInfo *keys, bool *constfalse,
+ PartScanKeyInfo *keys, ParamListInfo prmlist,
+ ExprContext *econtext, bool *constfalse,
List **or_clauses, List **ne_clauses);
static void remove_redundant_clauses(PartitionKey partkey,
int partattoff, List *all_clauses,
- List **result, bool *constfalse);
+ List **result, ParamListInfo prmlist,
+ ExprContext *econtext,bool *constfalse);
static bool partition_cmp_args(PartitionKey key, int partattoff,
PartClause *op, PartClause *leftarg, PartClause *rightarg,
+ ParamListInfo prmlist, ExprContext *econtext,
bool *result);
static PartOpStrategy partition_op_strategy(PartitionKey key, PartClause *op,
bool *incl);
static bool partkey_datum_from_expr(PartitionKey key, int partattoff,
- Expr *expr, Datum *value);
+ Expr *expr, ParamListInfo prmlist,
+ ExprContext *econtext, Datum *value);
static Bitmapset *get_partitions_for_keys(Relation rel,
PartScanKeyInfo *keys);
static Bitmapset *get_partitions_for_keys_hash(Relation rel,
@@ -1698,6 +1708,7 @@ get_partition_qual_relid(Oid relid)
*/
Bitmapset *
get_partitions_from_clauses(Relation relation, int rt_index,
+ ParamListInfo prmlist, ExprContext *econtext,
List *partclauses)
{
Bitmapset *result;
@@ -1727,16 +1738,137 @@ get_partitions_from_clauses(Relation relation, int rt_index,
if (partition_bound_has_default(boundinfo))
{
partconstr = (List *) expression_planner((Expr *) partconstr);
- partclauses = list_concat(partclauses, partconstr);
+ partclauses = list_concat(list_copy(partclauses), partconstr);
}
}
- result = get_partitions_from_clauses_recurse(relation, rt_index,
- partclauses);
+ result = get_partitions_from_clauses_recurse(relation, rt_index, prmlist,
+ econtext, partclauses);
return result;
}
+/*
+ * make_partition_pruneinfo
+ * Build PartitionPruneInfo tree to allow the output of
+ * get_partitions_from_clauses to be translated into
+ * 'subpaths' indexes
+ */
+PartitionPruneInfo *
+make_partition_pruneinfo(PlannerInfo *root, RelOptInfo *rel,
+ List *partition_rels, List *subpaths,
+ AppendRelInfo *subpartappinfo)
+{
+ PartitionPruneInfo *pinfo;
+ AppendRelInfo *appinfo;
+ RangeTblEntry *rte;
+ ListCell *lc;
+ int i;
+ int partidx;
+ int nparts = rel->nparts;
+
+ rte = root->simple_rte_array[rel->relid];
+
+ pinfo = makeNode(PartitionPruneInfo);
+ pinfo->parentoid = rte->relid;
+ pinfo->appinfo = subpartappinfo;
+ pinfo->subnodeindex = (int *) palloc(sizeof(int) * nparts);
+ pinfo->subpartindex = (PartitionPruneInfo **)
+ palloc0(sizeof(PartitionPruneInfo *) * nparts);
+ pinfo->nparts = nparts;
+
+ /*
+ * -1 represents a partition that has been pruned. Set them all to this
+ * initially. We'll determine the subpath index for the non-pruned
+ * ones below.
+ */
+ for (i = 0; i < nparts; i++)
+ pinfo->subnodeindex[i] = -1;
+
+ i = -1;
+ foreach(lc, subpaths)
+ {
+ Path *path = (Path *) lfirst(lc);
+
+ i++; /* track subnode index */
+
+ /* Find the AppendRelInfo for the Append child */
+ appinfo = find_childrel_appendrelinfo(root, path->parent);
+
+ /*
+ * Skip subpaths which belong to relations not directly parented by
+ * rel. We'll process any we skip here below when looping through
+ * partition_rels
+ */
+ if (appinfo->parent_relid != rel->relid)
+ continue;
+
+ /*
+ * Determine the element in part_appinfo which belongs to this
+ * subpath.
+ */
+ for (partidx = 0; partidx < nparts; partidx++)
+ {
+
+ if (rel->part_appinfos[partidx]->child_relid !=
+ appinfo->child_relid)
+ continue;
+
+ /* found it! Save the subnode index */
+ pinfo->subnodeindex[partidx] = i;
+ break;
+ }
+ }
+
+ /*
+ * Some of the relations returned by get_partitions_from_clauses may be
+ * other partitioned tables. Unlike the case above, these won't be
+ * subpaths of the Append. To handle these we must create a
+ * sub-PartitionPruneInfo to allow us to determine if subnodes which
+ * belong to sub-partitioned tables are required during partition pruning.
+ */
+ foreach(lc, partition_rels)
+ {
+ Index rti = lfirst_int(lc);
+ RelOptInfo *subpart = find_base_rel(root, rti);
+
+ /*
+ * partition_rels contains the rti of the base relation being queried.
+ * We only care about sub-partition parents here, so skip this.
+ */
+ if (subpart->reloptkind == RELOPT_BASEREL)
+ continue;
+
+ appinfo = find_childrel_appendrelinfo(root, subpart);
+
+ /*
+ * We only want to deal with sub-partition parents that are directly
+ * below rel. We'll deal with any we skip here later in a recursive
+ * call which is made below.
+ */
+ if (appinfo->parent_relid != rel->relid)
+ continue;
+
+ /*
+ * Handle sub-partition parents by building a sub-PartitionPruneInfo.
+ */
+ for (partidx = 0; partidx < nparts; partidx++)
+ {
+ if (rel->part_appinfos[partidx]->child_relid != appinfo->child_relid)
+ continue;
+
+ pinfo->subpartindex[partidx] = make_partition_pruneinfo(root,
+ subpart,
+ partition_rels,
+ subpaths,
+ appinfo);
+ break;
+ }
+ }
+
+ return pinfo;
+}
+
/* Module-local functions */
/*
@@ -1748,6 +1880,8 @@ get_partitions_from_clauses(Relation relation, int rt_index,
*/
static Bitmapset *
get_partitions_from_clauses_recurse(Relation relation, int rt_index,
+ ParamListInfo prmlist,
+ ExprContext *econtext,
List *clauses)
{
PartitionDesc partdesc = RelationGetPartitionDesc(relation);
@@ -1764,8 +1898,9 @@ get_partitions_from_clauses_recurse(Relation relation, int rt_index,
* can work with.
*/
nkeys = classify_partition_bounding_keys(relation, clauses, rt_index,
- &keys, &constfalse,
- &or_clauses, &ne_clauses);
+ &keys, prmlist, econtext,
+ &constfalse, &or_clauses,
+ &ne_clauses);
/*
* classify_partition_bounding_keys() may have found clauses marked
@@ -1799,7 +1934,10 @@ get_partitions_from_clauses_recurse(Relation relation, int rt_index,
{
Bitmapset *ne_clause_parts;
- ne_clause_parts = get_partitions_from_ne_clauses(relation, ne_clauses);
+ ne_clause_parts = get_partitions_from_ne_clauses(relation,
+ prmlist,
+ econtext,
+ ne_clauses);
/*
* Clauses in ne_clauses are in conjunction with the clauses that gave
@@ -1819,6 +1957,8 @@ get_partitions_from_clauses_recurse(Relation relation, int rt_index,
Bitmapset *or_parts;
or_parts = get_partitions_from_or_clause_args(relation, rt_index,
+ prmlist,
+ econtext,
or->args);
/*
* Clauses in or_clauses are mutually conjunctive and also in
@@ -1889,7 +2029,8 @@ count_partition_datums(Relation rel, int index)
* ne_clauses. Only ever called if relation is a list partitioned table.
*/
static Bitmapset *
-get_partitions_from_ne_clauses(Relation relation, List *ne_clauses)
+get_partitions_from_ne_clauses(Relation relation, ParamListInfo prmlist,
+ ExprContext *econtext, List *ne_clauses)
{
ListCell *lc;
Bitmapset *result,
@@ -1924,7 +2065,8 @@ get_partitions_from_ne_clauses(Relation relation, List *ne_clauses)
PartClause *pc = lfirst(lc);
Datum datum;
- if (partkey_datum_from_expr(partkey, 0, pc->constarg, &datum) &&
+ if (partkey_datum_from_expr(partkey, 0, pc->constarg, prmlist,
+ econtext, &datum) &&
!datum_in_array(partkey, datum, exclude_datums, n_exclude_datums))
exclude_datums[n_exclude_datums++] = datum;
}
@@ -1992,6 +2134,8 @@ get_partitions_from_ne_clauses(Relation relation, List *ne_clauses)
*/
static Bitmapset *
get_partitions_from_or_clause_args(Relation relation, int rt_index,
+ ParamListInfo prmlist,
+ ExprContext *econtext,
List *or_clause_args)
{
ListCell *lc;
@@ -2024,6 +2168,8 @@ get_partitions_from_or_clause_args(Relation relation, int rt_index,
}
arg_partset = get_partitions_from_clauses_recurse(relation, rt_index,
+ prmlist,
+ econtext,
arg_clauses);
/*
@@ -2079,7 +2225,10 @@ get_partitions_from_or_clause_args(Relation relation, int rt_index,
static int
classify_partition_bounding_keys(Relation relation, List *clauses,
int rt_index,
- PartScanKeyInfo *keys, bool *constfalse,
+ PartScanKeyInfo *keys,
+ ParamListInfo prmlist,
+ ExprContext *econtext,
+ bool *constfalse,
List **or_clauses,
List **ne_clauses)
{
@@ -2384,10 +2533,10 @@ classify_partition_bounding_keys(Relation relation, List *clauses,
elem_clauses = NIL;
foreach(lc1, elem_exprs)
{
- Const *rightop = castNode(Const, lfirst(lc1));
+ Expr *rightop = (Expr *) lfirst(lc1);
Expr *elem_clause;
- if (rightop->constisnull)
+ if (IsA(rightop, Const) && ((Const *)rightop)->constisnull)
{
NullTest *nulltest = makeNode(NullTest);
@@ -2507,7 +2656,7 @@ classify_partition_bounding_keys(Relation relation, List *clauses,
{
remove_redundant_clauses(partkey, i,
keyclauses_all[i], &keyclauses[i],
- constfalse);
+ prmlist, econtext, constfalse);
if (*constfalse)
return 0;
}
@@ -2570,11 +2719,13 @@ classify_partition_bounding_keys(Relation relation, List *clauses,
Assert(incl);
if (need_next_eq &&
partkey_datum_from_expr(partkey, i, constarg,
+ prmlist, econtext,
&keys->eqkeys[i]))
keys->n_eqkeys++;
if (need_next_max &&
partkey_datum_from_expr(partkey, i, constarg,
+ prmlist, econtext,
&keys->maxkeys[i]))
{
keys->n_maxkeys++;
@@ -2583,6 +2734,7 @@ classify_partition_bounding_keys(Relation relation, List *clauses,
if (need_next_min &&
partkey_datum_from_expr(partkey, i, constarg,
+ prmlist, econtext,
&keys->minkeys[i]))
{
keys->n_minkeys++;
@@ -2593,6 +2745,7 @@ classify_partition_bounding_keys(Relation relation, List *clauses,
case PART_OP_LESS:
if (need_next_max &&
partkey_datum_from_expr(partkey, i, constarg,
+ prmlist, econtext,
&keys->maxkeys[i]))
{
keys->n_maxkeys++;
@@ -2605,6 +2758,7 @@ classify_partition_bounding_keys(Relation relation, List *clauses,
case PART_OP_GREATER:
if (need_next_min &&
partkey_datum_from_expr(partkey, i, constarg,
+ prmlist, econtext,
&keys->minkeys[i]))
{
keys->n_minkeys++;
@@ -2698,7 +2852,8 @@ partition_op_strategy(PartitionKey key, PartClause *op, bool *incl)
*/
static bool
partkey_datum_from_expr(PartitionKey key, int partattoff,
- Expr *expr, Datum *value)
+ Expr *expr, ParamListInfo prmlist,
+ ExprContext *econtext, Datum *value)
{
Oid exprtype = exprType((Node *) expr);
@@ -2740,6 +2895,43 @@ partkey_datum_from_expr(PartitionKey key, int partattoff,
case T_Const:
*value = ((Const *) expr)->constvalue;
return true;
+ case T_Param:
+
+ switch (((Param *) expr)->paramkind)
+ {
+ case PARAM_EXTERN:
+ if (prmlist)
+ {
+ Node *node;
+ Param *param = (Param *) expr;
+ node = eval_const_expressions_from_list(prmlist,
+ (Node *) param);
+ if (IsA(node, Const))
+ {
+ *value = ((Const *) node)->constvalue;
+ return true;
+ }
+ }
+
+ case PARAM_EXEC:
+ if (econtext)
+ {
+ Param *param = (Param *) expr;
+ ParamExecData *prm;
+
+ prm = &(econtext->ecxt_param_exec_vals[param->paramid]);
+ if (unlikely(prm->execPlan != NULL))
+ {
+ ExecSetParamPlan(prm->execPlan, econtext);
+ Assert(prm->execPlan == NULL);
+ }
+ *value = prm->value;
+ return true;
+ }
+
+ default:
+ return false;
+ }
default:
return false;
@@ -2758,6 +2950,7 @@ partkey_datum_from_expr(PartitionKey key, int partattoff,
static void
remove_redundant_clauses(PartitionKey partkey, int partattoff,
List *all_clauses, List **result,
+ ParamListInfo prmlist, ExprContext *econtext,
bool *constfalse)
{
PartClause *hash_clause,
@@ -2801,7 +2994,7 @@ remove_redundant_clauses(PartitionKey partkey, int partattoff,
/* check if another clause would contradict the one we have */
else if (partition_cmp_args(partkey, partattoff,
cur, cur, hash_clause,
- &test_result))
+ prmlist, econtext, &test_result))
{
if (!test_result)
{
@@ -2856,7 +3049,7 @@ remove_redundant_clauses(PartitionKey partkey, int partattoff,
*/
if (partition_cmp_args(partkey, partattoff,
cur, cur, btree_clauses[s],
- &test_result))
+ prmlist, econtext, &test_result))
{
/* cur is more restrictive, replace old key. */
if (test_result)
@@ -2912,7 +3105,7 @@ remove_redundant_clauses(PartitionKey partkey, int partattoff,
*/
if (partition_cmp_args(partkey, partattoff,
chk, eq, chk,
- &test_result))
+ prmlist, econtext, &test_result))
{
if (!test_result)
{
@@ -2942,7 +3135,7 @@ remove_redundant_clauses(PartitionKey partkey, int partattoff,
if (partition_cmp_args(partkey, partattoff,
le, lt, le,
- &test_result))
+ prmlist, econtext, &test_result))
{
if (test_result)
btree_clauses[BTLessEqualStrategyNumber - 1] = NULL;
@@ -2960,7 +3153,7 @@ remove_redundant_clauses(PartitionKey partkey, int partattoff,
if (partition_cmp_args(partkey, partattoff,
ge, gt, ge,
- &test_result))
+ prmlist, econtext, &test_result))
{
if (test_result)
btree_clauses[BTGreaterEqualStrategyNumber - 1] = NULL;
@@ -2994,6 +3187,7 @@ remove_redundant_clauses(PartitionKey partkey, int partattoff,
static bool
partition_cmp_args(PartitionKey key, int partattoff,
PartClause *op, PartClause *leftarg, PartClause *rightarg,
+ ParamListInfo prmlist, ExprContext *econtext,
bool *result)
{
Oid partopfamily = key->partopfamily[partattoff];
@@ -3003,10 +3197,12 @@ partition_cmp_args(PartitionKey key, int partattoff,
Assert(op->valid_cache && leftarg->valid_cache && rightarg->valid_cache);
/* Get the constant values from the operands */
if (!partkey_datum_from_expr(key, partattoff,
- leftarg->constarg, &leftarg_const))
+ leftarg->constarg, prmlist, econtext,
+ &leftarg_const))
return false;
if (!partkey_datum_from_expr(key, partattoff,
- rightarg->constarg, &rightarg_const))
+ rightarg->constarg, prmlist, econtext,
+ &rightarg_const))
return false;
/*
diff --git a/src/backend/executor/nodeAppend.c b/src/backend/executor/nodeAppend.c
index 64a17fb..57da038 100644
--- a/src/backend/executor/nodeAppend.c
+++ b/src/backend/executor/nodeAppend.c
@@ -57,9 +57,12 @@
#include "postgres.h"
+#include "parser/parsetree.h"
#include "executor/execdebug.h"
#include "executor/nodeAppend.h"
#include "miscadmin.h"
+#include "optimizer/prep.h"
+#include "utils/memutils.h"
/* Shared state for parallel-aware Append. */
struct ParallelAppendState
@@ -82,6 +85,13 @@ static TupleTableSlot *ExecAppend(PlanState *pstate);
static bool choose_next_subplan_locally(AppendState *node);
static bool choose_next_subplan_for_leader(AppendState *node);
static bool choose_next_subplan_for_worker(AppendState *node);
+static void set_valid_runtime_subplans(AppendState *node);
+static void set_valid_runtime_subplans_recurse(AppendState *node,
+ PartitionPruneInfo *pinfo,
+ List *clauses,
+ Bitmapset **validsubplans);
+static void mark_invalid_subplans_as_finished(AppendState *node);
+
/* ----------------------------------------------------------------
* ExecInitAppend
@@ -127,20 +137,47 @@ ExecInitAppend(Append *node, EState *estate, int eflags)
appendstate->ps.ExecProcNode = ExecAppend;
appendstate->appendplans = appendplanstates;
appendstate->as_nplans = nplans;
+ appendstate->as_valid_subplans = NULL;
+ appendstate->prune_qual = node->plan.qual;
+ appendstate->part_prune_params = node->part_prune_params;
+ appendstate->part_prune_info = node->part_prune_info;
/*
* Miscellaneous initialization
*
- * Append plans don't have expression contexts because they never call
- * ExecQual or ExecProject.
+ * create expression context for node
*/
+ ExecAssignExprContext(estate, &appendstate->ps);
/*
- * append nodes still have Result slots, which hold pointers to tuples, so
- * we have to initialize them.
+ * tuple table initialization
*/
ExecInitResultTupleSlot(estate, &appendstate->ps);
+ if (node->part_prune_info)
+ {
+ /*
+ * When run-time partition pruning is enabled we make calls to a query
+ * planner function to determine which partitions will match. The
+ * planner is not too careful about freeing memory, so we'll ensure we
+ * call the function in a temporary memory context to avoid any memory
+ * leaking in the executor's memory context.
+ */
+ appendstate->prune_context =
+ AllocSetContextCreate(CurrentMemoryContext,
+ "Partition Prune",
+ ALLOCSET_DEFAULT_SIZES);
+ }
+ else
+ {
+ /*
+ * When run-time partition pruning is not enabled we can just mark
+ * all subplans as valid now.
+ */
+ appendstate->as_valid_subplans = bms_add_range(NULL, 0, nplans - 1);
+ appendstate->prune_context = NULL;
+ }
+
/*
* call ExecInitNode on each of the plans to be executed and save the
* results into the array "appendplans".
@@ -160,13 +197,8 @@ ExecInitAppend(Append *node, EState *estate, int eflags)
ExecAssignResultTypeFromTL(&appendstate->ps);
appendstate->ps.ps_ProjInfo = NULL;
- /*
- * Parallel-aware append plans must choose the first subplan to execute by
- * looking at shared memory, but non-parallel-aware append plans can
- * always start with the first subplan.
- */
- appendstate->as_whichplan =
- appendstate->ps.plan->parallel_aware ? INVALID_SUBPLAN_INDEX : 0;
+ /* Let choose_next_subplan_* function handle setting the first subplan */
+ appendstate->as_whichplan = INVALID_SUBPLAN_INDEX;
/* If parallel-aware, this will be overridden later. */
appendstate->choose_next_subplan = choose_next_subplan_locally;
@@ -257,6 +289,18 @@ ExecReScanAppend(AppendState *node)
{
int i;
+ /*
+ * If any of the parameters being used for partition pruning have changed,
+ * then we'd better unset the valid subplans so that they can reselected
+ * for the new parameter values.
+ */
+ if (node->part_prune_info &&
+ bms_overlap(node->ps.chgParam, node->part_prune_params))
+ {
+ bms_free(node->as_valid_subplans);
+ node->as_valid_subplans = NULL;
+ }
+
for (i = 0; i < node->as_nplans; i++)
{
PlanState *subnode = node->appendplans[i];
@@ -276,8 +320,8 @@ ExecReScanAppend(AppendState *node)
ExecReScan(subnode);
}
- node->as_whichplan =
- node->ps.plan->parallel_aware ? INVALID_SUBPLAN_INDEX : 0;
+ /* Let choose_next_subplan_* function handle setting the first subplan */
+ node->as_whichplan = INVALID_SUBPLAN_INDEX;
}
/* ----------------------------------------------------------------
@@ -366,22 +410,35 @@ static bool
choose_next_subplan_locally(AppendState *node)
{
int whichplan = node->as_whichplan;
+ int nextplan;
- /* We should never see INVALID_SUBPLAN_INDEX in this case. */
- Assert(whichplan >= 0 && whichplan <= node->as_nplans);
-
- if (ScanDirectionIsForward(node->ps.state->es_direction))
+ /*
+ * If first call then have the bms member function choose the first valid
+ * subplan by initializing whichplan to -1. If there happen to be no
+ * valid subplans then the bms member function will handle that by
+ * returning a negative number which will allow us to exit returning a
+ * false value.
+ */
+ if (whichplan == INVALID_SUBPLAN_INDEX)
{
- if (whichplan >= node->as_nplans - 1)
- return false;
- node->as_whichplan++;
+ if (node->as_valid_subplans == NULL)
+ set_valid_runtime_subplans(node);
+
+ whichplan = -1;
}
+
+ /* Ensure whichplan is within the expected range */
+ Assert(whichplan >= -1 && whichplan <= node->as_nplans);
+
+ if (ScanDirectionIsForward(node->ps.state->es_direction))
+ nextplan = bms_next_member(node->as_valid_subplans, whichplan);
else
- {
- if (whichplan <= 0)
- return false;
- node->as_whichplan--;
- }
+ nextplan = bms_prev_member(node->as_valid_subplans, whichplan);
+
+ if (nextplan < 0)
+ return false;
+
+ node->as_whichplan = nextplan;
return true;
}
@@ -414,6 +471,17 @@ choose_next_subplan_for_leader(AppendState *node)
{
/* Start with last subplan. */
node->as_whichplan = node->as_nplans - 1;
+
+ /*
+ * If we've yet to determine the valid subplans for these parameters
+ * then do so now. If run-time pruning is disabled then the valid
+ * subplans will always be set to all subplans.
+ */
+ if (node->as_valid_subplans == NULL)
+ {
+ set_valid_runtime_subplans(node);
+ mark_invalid_subplans_as_finished(node);
+ }
}
/* Loop until we find a subplan to execute. */
@@ -466,6 +534,17 @@ choose_next_subplan_for_worker(AppendState *node)
if (node->as_whichplan != INVALID_SUBPLAN_INDEX)
node->as_pstate->pa_finished[node->as_whichplan] = true;
+ /*
+ * If we've yet to determine the valid subplans for these parameters then
+ * do so now. If run-time pruning is disabled then the valid subplans
+ * will always be set to all subplans.
+ */
+ else if (node->as_valid_subplans == NULL)
+ {
+ set_valid_runtime_subplans(node);
+ mark_invalid_subplans_as_finished(node);
+ }
+
/* If all the plans are already done, we have nothing to do */
if (pstate->pa_next_plan == INVALID_SUBPLAN_INDEX)
{
@@ -525,3 +604,124 @@ choose_next_subplan_for_worker(AppendState *node)
return true;
}
+
+/*
+ * set_valid_runtime_subplans
+ * Determine which subset of subplan nodes we need to scan based on
+ * AppendState's 'prune_qual'. All subplans which provably cannot
+ * possibly have matching records are eliminated and the remainder are
+ * set in the AppendState's 'as_valid_subplans' variable.
+ */
+static void
+set_valid_runtime_subplans(AppendState *node)
+{
+ MemoryContext oldcontext;
+ Bitmapset *validsubplans = NULL;
+
+ /* Should never be called when already set */
+ Assert(node->as_valid_subplans == NULL);
+
+ /*
+ * Switch to a temp context to avoid leaking memory in the
+ * executor's memory context.
+ */
+ oldcontext = MemoryContextSwitchTo(node->prune_context);
+
+ set_valid_runtime_subplans_recurse(node, node->part_prune_info,
+ node->prune_qual,
+ &validsubplans);
+
+ MemoryContextSwitchTo(oldcontext);
+
+ /* Move to the correct memory context */
+ node->as_valid_subplans = bms_copy(validsubplans);
+
+ MemoryContextReset(node->prune_context);
+}
+
+static void
+set_valid_runtime_subplans_recurse(AppendState *node,
+ PartitionPruneInfo *pinfo,
+ List *clauses,
+ Bitmapset **validsubplans)
+{
+ Bitmapset *partset;
+ Relation rel;
+ int i;
+
+ /*
+ * The presence of an AppendRelInfo here means we're dealing with a
+ * sub-partitioned table. This table may have columns defined in a
+ * different order to its parent. We'll need to translate the attribute
+ * numbers of Vars in the clause list so that it's compatible with the
+ * sub-partition.
+ */
+ if (pinfo->appinfo)
+ clauses = (List *) adjust_appendrel_attrs(NULL, (Node *) clauses,
+ 1, &pinfo->appinfo);
+
+ rel = relation_open(pinfo->parentoid, NoLock);
+
+ /* Determine which partition indexes we need to scan */
+ partset = get_partitions_from_clauses(rel, 1,
+ node->ps.state->es_param_list_info,
+ node->ps.ps_ExprContext, clauses);
+
+ /* Translate partset into subnode indexes */
+ i = -1;
+ while ((i = bms_next_member(partset, i)) >= 0)
+ {
+ if (pinfo->subnodeindex[i] >= 0)
+ *validsubplans = bms_add_member(*validsubplans,
+ pinfo->subnodeindex[i]);
+ else if (pinfo->subpartindex[i] != NULL)
+ set_valid_runtime_subplans_recurse(node, pinfo->subpartindex[i],
+ clauses,
+ validsubplans);
+ else
+ {
+ /*
+ * If this happens then we're somehow missing an Append subnode.
+ * This shouldn't happen and could only happen if a more
+ * restrictive clause list was used for partition elimination
+ * during planning than was used here.
+ */
+ elog(ERROR, "partition missing from Append subplans");
+ }
+ }
+
+ bms_free(partset);
+
+ relation_close(rel, NoLock);
+}
+
+/*
+ * mark_invalid_subplans_as_finished
+ * Marks the ParallelAppendState's pa_finished as true for each invalid
+ * subplan.
+ *
+ * This function should only be called for parallel Append with run-time
+ * pruning enabled.
+ */
+static void
+mark_invalid_subplans_as_finished(AppendState *node)
+{
+ int i;
+
+ /* Only valid to call this while in parallel Append mode */
+ Assert(node->as_pstate);
+
+ /* Shouldn't have been called when run-time pruning is not enabled */
+ Assert(node->part_prune_info != NULL);
+
+ /* Nothing to do if all plans are valid */
+ if (bms_num_members(node->as_valid_subplans) == node->as_nplans)
+ return;
+
+ /* Mark all non-valid plans as finished */
+ for (i = 0; i < node->as_nplans; i++)
+ {
+ if (!bms_is_member(i, node->as_valid_subplans))
+ node->as_pstate->pa_finished[i] = true;
+ }
+}
diff --git a/src/backend/nodes/bitmapset.c b/src/backend/nodes/bitmapset.c
index 733fe3c..6afc90e 100644
--- a/src/backend/nodes/bitmapset.c
+++ b/src/backend/nodes/bitmapset.c
@@ -58,6 +58,9 @@
* rightmost_one_pos[x] gives the bit number (0-7) of the rightmost one bit
* in a nonzero byte value x. The entry for x=0 is never used.
*
+ * leftmost_ons_pos[x] gives the bit number (0-7) of the leftmost one bit in a
+ * nonzero byte value x. The entry for x=0 is never used.
+ *
* number_of_ones[x] gives the number of one-bits (0-8) in a byte value x.
*
* We could make these tables larger and reduce the number of iterations
@@ -84,6 +87,25 @@ static const uint8 rightmost_one_pos[256] = {
4, 0, 1, 0, 2, 0, 1, 0, 3, 0, 1, 0, 2, 0, 1, 0
};
+static const uint8 leftmost_one_pos[256] = {
+ 0, 0, 1, 1, 2, 2, 2, 2, 3, 3, 3, 3, 3, 3, 3, 3,
+ 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4,
+ 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5,
+ 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5,
+ 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6,
+ 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6,
+ 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6,
+ 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6,
+ 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7,
+ 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7,
+ 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7,
+ 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7,
+ 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7,
+ 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7,
+ 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7,
+ 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7
+};
+
static const uint8 number_of_ones[256] = {
0, 1, 1, 2, 1, 2, 2, 3, 1, 2, 2, 3, 2, 3, 3, 4,
1, 2, 2, 3, 2, 3, 3, 4, 2, 3, 3, 4, 3, 4, 4, 5,
@@ -1045,6 +1067,79 @@ bms_next_member(const Bitmapset *a, int prevbit)
}
/*
+ * bms_prev_member - find prev member of a set
+ *
+ * Returns largest member less than "prevbit", or -2 if there is none.
+ * "prevbit" must NOT be more than one above the highest possible bit that can
+ * be set at the Bitmapset at its current size.
+ *
+ * To ease finding the highest set bit for the initial loop, the special
+ * prevbit value of -1 can be passed to have the function find the highest
+ * valued member in the set.
+ *
+ * This is intended as support for iterating through the members of a set in
+ * reverse. The typical pattern is
+ *
+ * x = -1;
+ * while ((x = bms_prev_member(inputset, x)) >= 0)
+ * process member x;
+ *
+ * Notice that when there are no more members, we return -2, not -1 as you
+ * might expect. The rationale for that is to allow distinguishing the
+ * loop-not-started state (x == -1) from the loop-completed state (x == -2).
+ * It makes no difference in simple loop usage, but complex iteration logic
+ * might need such an ability.
+ */
+
+int
+bms_prev_member(const Bitmapset *a, int prevbit)
+{
+ int wordnum;
+ int ushiftbits;
+ bitmapword mask;
+
+ /*
+ * If set is NULL or if there are no more bits to the right then we've
+ * nothing to do.
+ */
+ if (a == NULL || prevbit == 0)
+ return -2;
+
+ /* transform -1 to the highest possible bit we could have set */
+ if (prevbit == -1)
+ prevbit = a->nwords * BITS_PER_BITMAPWORD - 1;
+ else
+ prevbit--;
+
+ ushiftbits = BITS_PER_BITMAPWORD - (BITNUM(prevbit) + 1);
+ mask = (~(bitmapword) 0) >> ushiftbits;
+ for (wordnum = WORDNUM(prevbit); wordnum >= 0; wordnum--)
+ {
+ bitmapword w = a->words[wordnum];
+
+ /* mask out bits left of prevbit */
+ w &= mask;
+
+ if (w != 0)
+ {
+ int result;
+ int shift = 24;
+ result = wordnum * BITS_PER_BITMAPWORD;
+
+ while ((w >> shift) == 0)
+ shift -= 8;
+
+ result += shift + leftmost_one_pos[(w >> shift) & 255];
+ return result;
+ }
+
+ /* in subsequent words, consider all bits */
+ mask = (~(bitmapword) 0);
+ }
+ return -2;
+}
+
+/*
* bms_hash_value - compute a hash key for a Bitmapset
*
* Note: we must ensure that any two bitmapsets that are bms_equal() will
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index ddbbc79..4b2f3eb 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -243,6 +243,8 @@ _copyAppend(const Append *from)
COPY_NODE_FIELD(partitioned_rels);
COPY_NODE_FIELD(appendplans);
COPY_SCALAR_FIELD(first_partial_plan);
+ COPY_BITMAPSET_FIELD(part_prune_params);
+ COPY_NODE_FIELD(part_prune_info);
return newnode;
}
@@ -2126,6 +2128,32 @@ _copyOnConflictExpr(const OnConflictExpr *from)
return newnode;
}
+static PartitionPruneInfo *
+_copyPartitionPruneInfo(const PartitionPruneInfo *from)
+{
+ PartitionPruneInfo *newnode = makeNode(PartitionPruneInfo);
+ int i;
+
+ COPY_SCALAR_FIELD(parentoid);
+ COPY_NODE_FIELD(appinfo);
+ COPY_SCALAR_FIELD(nparts);
+ COPY_POINTER_FIELD(subnodeindex, from->nparts * sizeof(int));
+ COPY_POINTER_FIELD(subpartindex, from->nparts * sizeof(PartitionPruneInfo));
+
+ /*
+ * The above copied the entire array, but we still need to create copies
+ * of each PartitionPruneInfo contained in that array.
+ */
+ for (i = 0; i < from->nparts; i++)
+ {
+ if (newnode->subpartindex[i] != NULL)
+ newnode->subpartindex[i] =
+ _copyPartitionPruneInfo(newnode->subpartindex[i]);
+ }
+
+ return newnode;
+}
+
/* ****************************************************************
* relation.h copy functions
*
@@ -5007,6 +5035,9 @@ copyObjectImpl(const void *from)
case T_OnConflictExpr:
retval = _copyOnConflictExpr(from);
break;
+ case T_PartitionPruneInfo:
+ retval = _copyPartitionPruneInfo(from);
+ break;
/*
* RELATION NODES
diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index 6774e08..e52dcb7 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -141,12 +141,6 @@ static void add_paths_to_append_rel(PlannerInfo *root, RelOptInfo *rel,
static List *get_append_rel_partitions(PlannerInfo *root,
RelOptInfo *rel,
RangeTblEntry *rte);
-static List *match_clauses_to_partkey(PlannerInfo *root,
- RelOptInfo *rel,
- List *clauses,
- bool *contains_const,
- bool *constfalse);
-
/*
* make_one_rel
@@ -877,6 +871,7 @@ get_append_rel_partitions(PlannerInfo *root,
Relation parent;
PartitionDesc partdesc;
Bitmapset *partindexes;
+ Bitmapset *paramids = NULL;
/*
* Get the clauses that match the partition key, including information
@@ -885,6 +880,7 @@ get_append_rel_partitions(PlannerInfo *root,
*/
partclauses = match_clauses_to_partkey(root, rel,
list_copy(rel->baserestrictinfo),
+ ¶mids,
&contains_const,
&constfalse);
@@ -892,6 +888,13 @@ get_append_rel_partitions(PlannerInfo *root,
if (constfalse)
return NIL;
+ /*
+ * Record any params found that we could use to further eliminate
+ * partitions during execution.
+ */
+ rel->runtime_prune_params = bms_add_members(rel->runtime_prune_params,
+ paramids);
+
parent = heap_open(rte->relid, NoLock);
partdesc = RelationGetPartitionDesc(parent);
@@ -900,8 +903,8 @@ get_append_rel_partitions(PlannerInfo *root,
* then use these to prune partitions.
*/
if (partclauses != NIL && contains_const)
- partindexes = get_partitions_from_clauses(parent, rel->relid,
- partclauses);
+ partindexes = get_partitions_from_clauses(parent, rel->relid, NULL,
+ NULL, partclauses);
else
{
/*
@@ -963,10 +966,11 @@ get_append_rel_partitions(PlannerInfo *root,
* If the list contains a pseudo-constant RestrictInfo with constant false
* value, *constfalse is set.
*/
-static List *
+List *
match_clauses_to_partkey(PlannerInfo *root,
RelOptInfo *rel,
List *clauses,
+ Bitmapset **paramids,
bool *contains_const,
bool *constfalse)
{
@@ -1030,6 +1034,7 @@ match_clauses_to_partkey(PlannerInfo *root,
constfalse1;
if (match_clauses_to_partkey(root, rel, list_make1(arg),
+ paramids,
&contains_const1,
&constfalse1) != NIL)
{
@@ -1154,8 +1159,11 @@ match_clauses_to_partkey(PlannerInfo *root,
*/
result = lappend(result, clause);
- if (!*contains_const)
- *contains_const = IsA(constexpr, Const);
+ if (IsA(constexpr, Const))
+ *contains_const = true;
+ else if (IsA(constexpr, Param))
+ *paramids = bms_add_member(*paramids,
+ ((Param *) constexpr)->paramid);
}
else if (IsA(clause, ScalarArrayOpExpr))
{
@@ -1187,10 +1195,32 @@ match_clauses_to_partkey(PlannerInfo *root,
/* OK to add to the result. */
result = lappend(result, clause);
- if (IsA(estimate_expression_value(root, rightop), Const))
+
+ if (IsA(rightop, Const))
*contains_const = true;
- else
- *contains_const = false;
+ else if (IsA(rightop, ArrayExpr))
+ {
+ ArrayExpr *arrayexpr = (ArrayExpr *) rightop;
+ ListCell *lc;
+ bool allconsts = true;
+
+ foreach(lc, arrayexpr->elements)
+ {
+ Expr *expr = (Expr *) lfirst(lc);
+
+ if (IsA(expr, Const))
+ continue;
+
+ allconsts = false;
+
+ if (IsA(expr, Param))
+ *paramids = bms_add_member(*paramids,
+ ((Param *) expr)->paramid);
+ }
+
+ if (allconsts)
+ *contains_const = true;
+ }
}
else if (IsA(clause, NullTest))
{
@@ -1907,7 +1937,7 @@ add_paths_to_append_rel(PlannerInfo *root, RelOptInfo *rel,
* if we have zero or one live subpath due to constraint exclusion.)
*/
if (subpaths_valid)
- add_path(rel, (Path *) create_append_path(rel, subpaths, NIL,
+ add_path(rel, (Path *) create_append_path(root, rel, subpaths, NIL,
NULL, 0, false,
partitioned_rels, -1));
@@ -1949,8 +1979,8 @@ add_paths_to_append_rel(PlannerInfo *root, RelOptInfo *rel,
Assert(parallel_workers > 0);
/* Generate a partial append path. */
- appendpath = create_append_path(rel, NIL, partial_subpaths, NULL,
- parallel_workers,
+ appendpath = create_append_path(root, rel, NIL, partial_subpaths,
+ NULL, parallel_workers,
enable_parallel_append,
partitioned_rels, -1);
@@ -1998,7 +2028,7 @@ add_paths_to_append_rel(PlannerInfo *root, RelOptInfo *rel,
max_parallel_workers_per_gather);
Assert(parallel_workers > 0);
- appendpath = create_append_path(rel, pa_nonpartial_subpaths,
+ appendpath = create_append_path(root, rel, pa_nonpartial_subpaths,
pa_partial_subpaths,
NULL, parallel_workers, true,
partitioned_rels, partial_rows);
@@ -2054,7 +2084,7 @@ add_paths_to_append_rel(PlannerInfo *root, RelOptInfo *rel,
if (subpaths_valid)
add_path(rel, (Path *)
- create_append_path(rel, subpaths, NIL,
+ create_append_path(root, rel, subpaths, NIL,
required_outer, 0, false,
partitioned_rels, -1));
}
@@ -2319,7 +2349,7 @@ set_dummy_rel_pathlist(RelOptInfo *rel)
rel->pathlist = NIL;
rel->partial_pathlist = NIL;
- add_path(rel, (Path *) create_append_path(rel, NIL, NIL, NULL,
+ add_path(rel, (Path *) create_append_path(NULL, rel, NIL, NIL, NULL,
0, false, NIL, -1));
/*
diff --git a/src/backend/optimizer/path/joinrels.c b/src/backend/optimizer/path/joinrels.c
index d9249f4..1bd92c1 100644
--- a/src/backend/optimizer/path/joinrels.c
+++ b/src/backend/optimizer/path/joinrels.c
@@ -1232,7 +1232,7 @@ mark_dummy_rel(RelOptInfo *rel)
rel->partial_pathlist = NIL;
/* Set up the dummy path */
- add_path(rel, (Path *) create_append_path(rel, NIL, NIL, NULL,
+ add_path(rel, (Path *) create_append_path(NULL, rel, NIL, NIL, NULL,
0, false, NIL, -1));
/* Set or update cheapest_total_path and related fields */
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index e599283..7f78e22 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -204,7 +204,10 @@ static NamedTuplestoreScan *make_namedtuplestorescan(List *qptlist, List *qpqual
static WorkTableScan *make_worktablescan(List *qptlist, List *qpqual,
Index scanrelid, int wtParam);
static Append *make_append(List *appendplans, int first_partial_plan,
- List *tlist, List *partitioned_rels);
+ List *tlist, List *partitioned_rels,
+ PartitionPruneInfo *partpruneinfo,
+ Bitmapset *partpruneparams,
+ List *qual);
static RecursiveUnion *make_recursive_union(List *tlist,
Plan *lefttree,
Plan *righttree,
@@ -1016,6 +1019,9 @@ create_append_plan(PlannerInfo *root, AppendPath *best_path)
List *tlist = build_path_tlist(root, &best_path->path);
List *subplans = NIL;
ListCell *subpaths;
+ List *qual = NIL;
+ RelOptInfo *rel = best_path->path.parent;
+ PartitionPruneInfo *pinfo = NULL;
/*
* The subpaths list could be empty, if every child was proven empty by
@@ -1053,6 +1059,51 @@ create_append_plan(PlannerInfo *root, AppendPath *best_path)
subplans = lappend(subplans, subplan);
}
+
+ if (best_path->trypartitionprune)
+ {
+ /* Not for join rels */
+ Assert(bms_membership(rel->relids) == BMS_SINGLETON);
+
+ qual =
+ extract_actual_clauses(best_path->path.parent->baserestrictinfo,
+ false);
+
+ if (best_path->path.param_info)
+ {
+ List *prmquals = best_path->path.param_info->ppi_clauses;
+ bool contains_const;
+ bool constfalse;
+
+ prmquals = extract_actual_clauses(prmquals, false);
+ prmquals = (List *) replace_nestloop_params(root,
+ (Node *) prmquals);
+
+ qual = list_concat(qual, prmquals);
+
+ /*
+ * So far, we only know about the pruning params for the base quals
+ * in rel, there may well be params matching partition keys in the
+ * parameterized path clause too, so we'll gather these now. We'll
+ * borrow match_clauses_to_partkey for this, although we only care
+ * about the parameter IDs and not any of the other outputs.
+ */
+ (void) match_clauses_to_partkey(root, rel, prmquals,
+ &rel->runtime_prune_params,
+ &contains_const, &constfalse);
+ }
+
+ /*
+ * If there are parameters matching the partition key then we'll now
+ * enable run-time partition pruning. There's no fancy big switch
+ * to enable it, we'll just make a PartitionPruneInfo and pass that
+ * along to the executor. It'll just make use of it when available.
+ */
+ if (rel->runtime_prune_params)
+ pinfo = make_partition_pruneinfo(root, best_path->path.parent,
+ best_path->partitioned_rels,
+ best_path->subpaths, NULL);
+ }
/*
* XXX ideally, if there's just one child, we'd not bother to generate an
* Append node but just return the single child. At the moment this does
@@ -1061,7 +1112,10 @@ create_append_plan(PlannerInfo *root, AppendPath *best_path)
*/
plan = make_append(subplans, best_path->first_partial_path,
- tlist, best_path->partitioned_rels);
+ tlist, best_path->partitioned_rels,
+ pinfo,
+ rel->runtime_prune_params,
+ qual);
copy_generic_path_info(&plan->plan, (Path *) best_path);
@@ -5308,19 +5362,23 @@ make_foreignscan(List *qptlist,
static Append *
make_append(List *appendplans, int first_partial_plan,
- List *tlist, List *partitioned_rels)
+ List *tlist, List *partitioned_rels,
+ PartitionPruneInfo *partpruneinfo,
+ Bitmapset *partpruneparams,
+ List *qual)
{
Append *node = makeNode(Append);
Plan *plan = &node->plan;
plan->targetlist = tlist;
- plan->qual = NIL;
+ plan->qual = qual;
plan->lefttree = NULL;
plan->righttree = NULL;
node->partitioned_rels = partitioned_rels;
node->appendplans = appendplans;
node->first_partial_plan = first_partial_plan;
-
+ node->part_prune_info = partpruneinfo;
+ node->part_prune_params = partpruneparams;
return node;
}
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index b0f6051..0eff96a 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -3678,7 +3678,8 @@ create_grouping_paths(PlannerInfo *root,
paths = lappend(paths, path);
}
path = (Path *)
- create_append_path(grouped_rel,
+ create_append_path(root,
+ grouped_rel,
paths,
NIL,
NULL,
diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c
index 4617d12..2709ae2 100644
--- a/src/backend/optimizer/plan/setrefs.c
+++ b/src/backend/optimizer/plan/setrefs.c
@@ -915,10 +915,10 @@ set_plan_refs(PlannerInfo *root, Plan *plan, int rtoffset)
/*
* Append, like Sort et al, doesn't actually evaluate its
- * targetlist or check quals.
+ * targetlist or check quals. However, quals may be used
+ * to allow partitions to be pruned at execution time.
*/
set_dummy_tlist_references(plan, rtoffset);
- Assert(splan->plan.qual == NIL);
foreach(l, splan->partitioned_rels)
{
lfirst_int(l) += rtoffset;
diff --git a/src/backend/optimizer/prep/prepunion.c b/src/backend/optimizer/prep/prepunion.c
index 5a08e75..64a981e 100644
--- a/src/backend/optimizer/prep/prepunion.c
+++ b/src/backend/optimizer/prep/prepunion.c
@@ -590,7 +590,7 @@ generate_union_path(SetOperationStmt *op, PlannerInfo *root,
/*
* Append the child results together.
*/
- path = (Path *) create_append_path(result_rel, pathlist, NIL,
+ path = (Path *) create_append_path(root, result_rel, pathlist, NIL,
NULL, 0, false, NIL, -1);
/* We have to manually jam the right tlist into the path; ick */
path->pathtarget = create_pathtarget(root, tlist);
@@ -702,7 +702,7 @@ generate_nonunion_path(SetOperationStmt *op, PlannerInfo *root,
/*
* Append the child results together.
*/
- path = (Path *) create_append_path(result_rel, pathlist, NIL,
+ path = (Path *) create_append_path(root, result_rel, pathlist, NIL,
NULL, 0, false, NIL, -1);
/* We have to manually jam the right tlist into the path; ick */
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index ccfae4f..8322665 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -2473,6 +2473,25 @@ eval_const_expressions(PlannerInfo *root, Node *node)
}
/*--------------------
+ * eval_const_expressions_from_list
+ *
+ * This is similar to eval_const_expression except that it takes ParamListInfo
+ * argument instead of PlannerInfo to create the context.
+ */
+Node *
+eval_const_expressions_from_list(ParamListInfo prmlist, Node *node)
+{
+ eval_const_expressions_context context;
+
+ context.boundParams = prmlist; /* bound Params */
+ context.root = NULL;
+ context.active_fns = NIL; /* nothing being recursively simplified */
+ context.case_val = NULL; /* no CASE being examined */
+ context.estimate = false; /* safe transformations only */
+ return eval_const_expressions_mutator(node, &context);
+}
+
+/*--------------------
* estimate_expression_value
*
* This function attempts to estimate the value of an expression for
diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c
index 7df8761..b524c34 100644
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -17,6 +17,7 @@
#include <math.h>
#include "miscadmin.h"
+#include "catalog/partition.h"
#include "nodes/nodeFuncs.h"
#include "nodes/extensible.h"
#include "optimizer/clauses.h"
@@ -1210,7 +1211,8 @@ create_tidscan_path(PlannerInfo *root, RelOptInfo *rel, List *tidquals,
* Note that we must handle subpaths = NIL, representing a dummy access path.
*/
AppendPath *
-create_append_path(RelOptInfo *rel,
+create_append_path(PlannerInfo *root,
+ RelOptInfo *rel,
List *subpaths, List *partial_subpaths,
Relids required_outer,
int parallel_workers, bool parallel_aware,
@@ -1224,8 +1226,37 @@ create_append_path(RelOptInfo *rel,
pathnode->path.pathtype = T_Append;
pathnode->path.parent = rel;
pathnode->path.pathtarget = rel->reltarget;
- pathnode->path.param_info = get_appendrel_parampathinfo(rel,
- required_outer);
+ pathnode->trypartitionprune = false;
+
+ /*
+ * When generating an Append path for a partitioned table we'll try to
+ * enable additional partition pruning at run-time. Useful pruning quals
+ * may be in parameterized path quals, so we'll go all the way and
+ * generate the qual list for the Append's parameterized paths. We need
+ * only bother trying this for RELOPT_BASEREL rels, as
+ * RELOPT_OTHER_MEMBER_REL's Append paths are merged into the base rel's
+ * Append subpaths, doing anything extra in this case would be wasted
+ * work.
+ */
+ if (rel->reloptkind == RELOPT_BASEREL && root)
+ {
+ RangeTblEntry *rte = planner_rt_fetch(rel->relid, root);
+
+ if (rte->rtekind == RTE_RELATION &&
+ rte->relkind == RELKIND_PARTITIONED_TABLE)
+ {
+ pathnode->path.param_info = get_baserel_parampathinfo(root,
+ rel,
+ required_outer);
+ pathnode->trypartitionprune = true;
+ }
+ else
+ pathnode->path.param_info = get_appendrel_parampathinfo(rel,
+ required_outer);
+ }
+ else
+ pathnode->path.param_info = get_appendrel_parampathinfo(rel,
+ required_outer);
pathnode->path.parallel_aware = parallel_aware;
pathnode->path.parallel_safe = rel->consider_parallel;
pathnode->path.parallel_workers = parallel_workers;
diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c
index 4b5d50e..e98f4d7 100644
--- a/src/backend/optimizer/util/relnode.c
+++ b/src/backend/optimizer/util/relnode.c
@@ -156,6 +156,7 @@ build_simple_rel(PlannerInfo *root, int relid, RelOptInfo *parent)
rel->boundinfo = NULL;
rel->part_appinfos = NULL;
rel->part_rels = NULL;
+ rel->runtime_prune_params = NULL;
rel->partexprs = NULL;
rel->nullable_partexprs = NULL;
rel->live_part_appinfos = NIL;
@@ -577,6 +578,7 @@ build_join_rel(PlannerInfo *root,
joinrel->boundinfo = NULL;
joinrel->part_appinfos = NULL;
joinrel->part_rels = NULL;
+ joinrel->runtime_prune_params = NULL;
joinrel->partexprs = NULL;
joinrel->nullable_partexprs = NULL;
@@ -745,6 +747,7 @@ build_child_join_rel(PlannerInfo *root, RelOptInfo *outer_rel,
joinrel->part_scheme = NULL;
joinrel->part_appinfos = NULL;
joinrel->part_rels = NULL;
+ joinrel->runtime_prune_params = NULL;
joinrel->partexprs = NULL;
joinrel->nullable_partexprs = NULL;
diff --git a/src/include/catalog/partition.h b/src/include/catalog/partition.h
index 8423c6e..56e9883eb 100644
--- a/src/include/catalog/partition.h
+++ b/src/include/catalog/partition.h
@@ -16,6 +16,7 @@
#include "fmgr.h"
#include "executor/tuptable.h"
#include "nodes/execnodes.h"
+#include "nodes/relation.h"
#include "parser/parse_node.h"
#include "utils/rel.h"
@@ -75,5 +76,10 @@ extern int get_partition_for_tuple(Relation relation, Datum *values,
/* For partition-pruning */
extern Bitmapset *get_partitions_from_clauses(Relation relation, int rt_index,
+ ParamListInfo prmlist, ExprContext *econtext,
List *partclauses);
+extern PartitionPruneInfo *make_partition_pruneinfo(PlannerInfo *root,
+ RelOptInfo *rel,
+ List *partition_rels, List *subpaths,
+ AppendRelInfo *subpartappinfo);
#endif /* PARTITION_H */
diff --git a/src/include/nodes/bitmapset.h b/src/include/nodes/bitmapset.h
index 15397e9..951ddb8 100644
--- a/src/include/nodes/bitmapset.h
+++ b/src/include/nodes/bitmapset.h
@@ -98,6 +98,7 @@ extern Bitmapset *bms_join(Bitmapset *a, Bitmapset *b);
/* support for iterating through the integer elements of a set: */
extern int bms_first_member(Bitmapset *a);
extern int bms_next_member(const Bitmapset *a, int prevbit);
+extern int bms_prev_member(const Bitmapset *a, int prevbit);
/* support for hashtables using Bitmapsets as keys: */
extern uint32 bms_hash_value(const Bitmapset *a);
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index 2a4f740..7fbd6e9 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -1000,6 +1000,7 @@ typedef struct ModifyTableState
*
* nplans how many plans are in the array
* whichplan which plan is being executed (0 .. n-1)
+ * valid_subplans for runtime pruning, valid appendplans indexes to scan
* ----------------
*/
@@ -1016,6 +1017,11 @@ struct AppendState
int as_whichplan;
ParallelAppendState *as_pstate; /* parallel coordination info */
Size pstate_len; /* size of parallel coordination info */
+ Bitmapset *as_valid_subplans; /* mask of non-pruned subplans */
+ List *prune_qual; /* quals used for partition pruning */
+ Bitmapset *part_prune_params; /* ParamIds useful for partition pruning */
+ PartitionPruneInfo *part_prune_info; /* details for partition pruning */
+ MemoryContext prune_context; /* used when calling planner pruning code */
bool (*choose_next_subplan) (AppendState *);
};
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
index 2eb3d6d..9788c35 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -191,6 +191,7 @@ typedef enum NodeTag
T_FromExpr,
T_OnConflictExpr,
T_IntoClause,
+ T_PartitionPruneInfo,
/*
* TAGS FOR EXPRESSION STATE NODES (execnodes.h)
diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h
index 74e9fb5..1d57748 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -249,6 +249,14 @@ typedef struct Append
List *partitioned_rels;
List *appendplans;
int first_partial_plan;
+ Bitmapset *part_prune_params; /* ParamIds used for partition pruning */
+
+ /*
+ * Mapping details for run-time subplan pruning. This allows translation
+ * from partition numbers into subplan indexes. This is set to NULL when
+ * run-time subplan pruning is disabled.
+ */
+ PartitionPruneInfo *part_prune_info;
} Append;
/* ----------------
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 1b4b0d7..96f2126 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -1506,4 +1506,29 @@ typedef struct OnConflictExpr
List *exclRelTlist; /* tlist of the EXCLUDED pseudo relation */
} OnConflictExpr;
+/*----------
+ * PartitionPruneInfo - Allows pruning of Append subplans
+ *
+ * Here we store mapping details to allow translation of a partitioned table's
+ * id number into an Append node's subplan index. This structure is used
+ * to recursively search for all subplan nodes when there are sub-partitioned
+ * tables in the Append plan.
+ *----------
+ */
+struct AppendRelInfo; /* XXX Okay? Or move the whole thing here? */
+
+typedef struct PartitionPruneInfo
+{
+ NodeTag type;
+ Oid parentoid; /* Oid of parent partition rel */
+ struct AppendRelInfo *appinfo; /* Used to translate the partition prune qual's
+ * Vars to the sub-partitions Vars, or NULL if no
+ * translation is needed. */
+ int nparts; /* length of the following arrays */
+ int *subnodeindex; /* subnode index indexed by partition id */
+
+ /* sub-PartitionPruneInfo indexed by partition id */
+ struct PartitionPruneInfo **subpartindex;
+} PartitionPruneInfo;
+
#endif /* PRIMNODES_H */
diff --git a/src/include/nodes/relation.h b/src/include/nodes/relation.h
index 855d51e..0f0bc52 100644
--- a/src/include/nodes/relation.h
+++ b/src/include/nodes/relation.h
@@ -669,6 +669,9 @@ typedef struct RelOptInfo
struct RelOptInfo **part_rels; /* Array of RelOptInfos of *all*
* partitions, stored in the same order as
* of bounds */
+ Bitmapset *runtime_prune_params; /* Only valid for base partition rels.
+ * Stores ParamIds used for run-time
+ * pruning of partitions. */
List **partexprs; /* Non-nullable partition key expressions. */
List **nullable_partexprs; /* Nullable partition key expressions. */
@@ -1295,6 +1298,8 @@ typedef struct AppendPath
/* Index of first partial path in subpaths */
int first_partial_path;
+
+ bool trypartitionprune; /* Attempt to enable partition pruning? */
} AppendPath;
#define IS_DUMMY_PATH(p) \
diff --git a/src/include/optimizer/clauses.h b/src/include/optimizer/clauses.h
index 3c2f549..bedffc4 100644
--- a/src/include/optimizer/clauses.h
+++ b/src/include/optimizer/clauses.h
@@ -80,6 +80,9 @@ extern void CommuteRowCompareExpr(RowCompareExpr *clause);
extern Node *eval_const_expressions(PlannerInfo *root, Node *node);
+extern Node *eval_const_expressions_from_list(ParamListInfo prmlist,
+ Node *node);
+
extern Node *estimate_expression_value(PlannerInfo *root, Node *node);
extern Query *inline_set_returning_function(PlannerInfo *root,
diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.h
index 9b4288a..44f34a9 100644
--- a/src/include/optimizer/pathnode.h
+++ b/src/include/optimizer/pathnode.h
@@ -64,7 +64,7 @@ extern BitmapOrPath *create_bitmap_or_path(PlannerInfo *root,
List *bitmapquals);
extern TidPath *create_tidscan_path(PlannerInfo *root, RelOptInfo *rel,
List *tidquals, Relids required_outer);
-extern AppendPath *create_append_path(RelOptInfo *rel,
+extern AppendPath *create_append_path(PlannerInfo *root, RelOptInfo *rel,
List *subpaths, List *partial_subpaths,
Relids required_outer,
int parallel_workers, bool parallel_aware,
diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h
index 0072b7a..8175c3f 100644
--- a/src/include/optimizer/paths.h
+++ b/src/include/optimizer/paths.h
@@ -50,6 +50,12 @@ extern PGDLLIMPORT join_search_hook_type join_search_hook;
extern RelOptInfo *make_one_rel(PlannerInfo *root, List *joinlist);
extern void set_dummy_rel_pathlist(RelOptInfo *rel);
+extern List *match_clauses_to_partkey(PlannerInfo *root,
+ RelOptInfo *rel,
+ List *clauses,
+ Bitmapset **paramids,
+ bool *contains_const,
+ bool *constfalse);
extern RelOptInfo *standard_join_search(PlannerInfo *root, int levels_needed,
List *initial_rels);
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index ad29f0f..edb3168 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -1427,3 +1427,959 @@ explain (costs off) select * from rlp where a = 15 and b <> 'ab' and b <> 'cd' a
(5 rows)
drop table lp, coll_pruning, rlp, mc3p, mc2p, boolpart, hp, rp;
+--
+-- Test runtime partitioning
+--
+create table ab (a int not null, b int not null) partition by list (a);
+create table ab_a2 partition of ab for values in(2) partition by list (b);
+create table ab_a2_b1 partition of ab_a2 for values in (1);
+create table ab_a2_b2 partition of ab_a2 for values in (2);
+create table ab_a2_b3 partition of ab_a2 for values in (3);
+create table ab_a1 partition of ab for values in(1) partition by list (b);
+create table ab_a1_b1 partition of ab_a1 for values in (1);
+create table ab_a1_b2 partition of ab_a1 for values in (2);
+create table ab_a1_b3 partition of ab_a1 for values in (3);
+create table ab_a3 partition of ab for values in(3) partition by list (b);
+create table ab_a3_b1 partition of ab_a3 for values in (1);
+create table ab_a3_b2 partition of ab_a3 for values in (2);
+create table ab_a3_b3 partition of ab_a3 for values in (3);
+prepare ab_q1 (int, int, int) as
+select * from ab where a between $1 and $2 and b <= $3;
+-- Execute query 5 times to allow choose_custom_plan
+-- to start considering a generic plan.
+execute ab_q1 (1, 8, 3);
+ a | b
+---+---
+(0 rows)
+
+execute ab_q1 (1, 8, 3);
+ a | b
+---+---
+(0 rows)
+
+execute ab_q1 (1, 8, 3);
+ a | b
+---+---
+(0 rows)
+
+execute ab_q1 (1, 8, 3);
+ a | b
+---+---
+(0 rows)
+
+execute ab_q1 (1, 8, 3);
+ a | b
+---+---
+(0 rows)
+
+explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2, 3);
+ QUERY PLAN
+---------------------------------------------------------
+ Append (actual rows=0 loops=1)
+ -> Seq Scan on ab_a1_b1 (never executed)
+ Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
+ -> Seq Scan on ab_a1_b2 (never executed)
+ Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
+ -> Seq Scan on ab_a1_b3 (never executed)
+ Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
+ -> Seq Scan on ab_a2_b1 (actual rows=0 loops=1)
+ Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
+ -> Seq Scan on ab_a2_b2 (actual rows=0 loops=1)
+ Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
+ -> Seq Scan on ab_a2_b3 (actual rows=0 loops=1)
+ Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
+ -> Seq Scan on ab_a3_b1 (never executed)
+ Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
+ -> Seq Scan on ab_a3_b2 (never executed)
+ Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
+ -> Seq Scan on ab_a3_b3 (never executed)
+ Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
+(19 rows)
+
+explain (analyze, costs off, summary off, timing off) execute ab_q1 (1, 2, 3);
+ QUERY PLAN
+---------------------------------------------------------
+ Append (actual rows=0 loops=1)
+ -> Seq Scan on ab_a1_b1 (actual rows=0 loops=1)
+ Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
+ -> Seq Scan on ab_a1_b2 (actual rows=0 loops=1)
+ Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
+ -> Seq Scan on ab_a1_b3 (actual rows=0 loops=1)
+ Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
+ -> Seq Scan on ab_a2_b1 (actual rows=0 loops=1)
+ Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
+ -> Seq Scan on ab_a2_b2 (actual rows=0 loops=1)
+ Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
+ -> Seq Scan on ab_a2_b3 (actual rows=0 loops=1)
+ Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
+ -> Seq Scan on ab_a3_b1 (never executed)
+ Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
+ -> Seq Scan on ab_a3_b2 (never executed)
+ Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
+ -> Seq Scan on ab_a3_b3 (never executed)
+ Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
+(19 rows)
+
+deallocate ab_q1;
+-- runtime pruning after optimizer pruning
+prepare ab_q1 (int, int) as
+select a from ab where a between $1 and $2 and b < 3;
+-- Execute query 5 times to allow choose_custom_plan
+-- to start considering a generic plan.
+execute ab_q1 (1, 8);
+ a
+---
+(0 rows)
+
+execute ab_q1 (1, 8);
+ a
+---
+(0 rows)
+
+execute ab_q1 (1, 8);
+ a
+---
+(0 rows)
+
+execute ab_q1 (1, 8);
+ a
+---
+(0 rows)
+
+execute ab_q1 (1, 8);
+ a
+---
+(0 rows)
+
+explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2);
+ QUERY PLAN
+-------------------------------------------------------
+ Append (actual rows=0 loops=1)
+ -> Seq Scan on ab_a1_b1 (never executed)
+ Filter: ((a >= $1) AND (a <= $2) AND (b < 3))
+ -> Seq Scan on ab_a1_b2 (never executed)
+ Filter: ((a >= $1) AND (a <= $2) AND (b < 3))
+ -> Seq Scan on ab_a2_b1 (actual rows=0 loops=1)
+ Filter: ((a >= $1) AND (a <= $2) AND (b < 3))
+ -> Seq Scan on ab_a2_b2 (actual rows=0 loops=1)
+ Filter: ((a >= $1) AND (a <= $2) AND (b < 3))
+ -> Seq Scan on ab_a3_b1 (never executed)
+ Filter: ((a >= $1) AND (a <= $2) AND (b < 3))
+ -> Seq Scan on ab_a3_b2 (never executed)
+ Filter: ((a >= $1) AND (a <= $2) AND (b < 3))
+(13 rows)
+
+explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 4);
+ QUERY PLAN
+-------------------------------------------------------
+ Append (actual rows=0 loops=1)
+ -> Seq Scan on ab_a1_b1 (never executed)
+ Filter: ((a >= $1) AND (a <= $2) AND (b < 3))
+ -> Seq Scan on ab_a1_b2 (never executed)
+ Filter: ((a >= $1) AND (a <= $2) AND (b < 3))
+ -> Seq Scan on ab_a2_b1 (actual rows=0 loops=1)
+ Filter: ((a >= $1) AND (a <= $2) AND (b < 3))
+ -> Seq Scan on ab_a2_b2 (actual rows=0 loops=1)
+ Filter: ((a >= $1) AND (a <= $2) AND (b < 3))
+ -> Seq Scan on ab_a3_b1 (actual rows=0 loops=1)
+ Filter: ((a >= $1) AND (a <= $2) AND (b < 3))
+ -> Seq Scan on ab_a3_b2 (actual rows=0 loops=1)
+ Filter: ((a >= $1) AND (a <= $2) AND (b < 3))
+(13 rows)
+
+-- parallel append
+prepare ab_q2 (int, int) as
+select avg(a) from ab where a between $1 and $2 and b < 4;
+-- encourage use of parallel plans
+set parallel_setup_cost = 0;
+set parallel_tuple_cost = 0;
+set min_parallel_table_scan_size = 0;
+set max_parallel_workers_per_gather = 2;
+-- Execute query 5 times to allow choose_custom_plan
+-- to start considering a generic plan.
+execute ab_q2 (1, 8);
+ avg
+-----
+
+(1 row)
+
+execute ab_q2 (1, 8);
+ avg
+-----
+
+(1 row)
+
+execute ab_q2 (1, 8);
+ avg
+-----
+
+(1 row)
+
+execute ab_q2 (1, 8);
+ avg
+-----
+
+(1 row)
+
+execute ab_q2 (1, 8);
+ avg
+-----
+
+(1 row)
+
+explain (analyze, costs off, summary off, timing off) execute ab_q2 (2, 2);
+ QUERY PLAN
+-------------------------------------------------------------------------------
+ Finalize Aggregate (actual rows=1 loops=1)
+ -> Gather (actual rows=3 loops=1)
+ Workers Planned: 2
+ Workers Launched: 2
+ -> Partial Aggregate (actual rows=1 loops=3)
+ -> Parallel Append (actual rows=0 loops=3)
+ -> Parallel Seq Scan on ab_a1_b1 (never executed)
+ Filter: ((a >= $1) AND (a <= $2) AND (b < 4))
+ -> Parallel Seq Scan on ab_a1_b2 (never executed)
+ Filter: ((a >= $1) AND (a <= $2) AND (b < 4))
+ -> Parallel Seq Scan on ab_a1_b3 (never executed)
+ Filter: ((a >= $1) AND (a <= $2) AND (b < 4))
+ -> Parallel Seq Scan on ab_a2_b1 (actual rows=0 loops=1)
+ Filter: ((a >= $1) AND (a <= $2) AND (b < 4))
+ -> Parallel Seq Scan on ab_a2_b2 (actual rows=0 loops=1)
+ Filter: ((a >= $1) AND (a <= $2) AND (b < 4))
+ -> Parallel Seq Scan on ab_a2_b3 (actual rows=0 loops=1)
+ Filter: ((a >= $1) AND (a <= $2) AND (b < 4))
+ -> Parallel Seq Scan on ab_a3_b1 (never executed)
+ Filter: ((a >= $1) AND (a <= $2) AND (b < 4))
+ -> Parallel Seq Scan on ab_a3_b2 (never executed)
+ Filter: ((a >= $1) AND (a <= $2) AND (b < 4))
+ -> Parallel Seq Scan on ab_a3_b3 (never executed)
+ Filter: ((a >= $1) AND (a <= $2) AND (b < 4))
+(24 rows)
+
+-- Test run-time pruning with IN lists.
+prepare ab_q3 (int, int, int) as
+select avg(a) from ab where a in($1,$2,$3) and b < 4;
+-- Execute query 5 times to allow choose_custom_plan
+-- to start considering a generic plan.
+execute ab_q3 (1, 2, 3);
+ avg
+-----
+
+(1 row)
+
+execute ab_q3 (1, 2, 3);
+ avg
+-----
+
+(1 row)
+
+execute ab_q3 (1, 2, 3);
+ avg
+-----
+
+(1 row)
+
+execute ab_q3 (1, 2, 3);
+ avg
+-----
+
+(1 row)
+
+execute ab_q3 (1, 2, 3);
+ avg
+-----
+
+(1 row)
+
+explain (analyze, costs off, summary off, timing off) execute ab_q3 (1, 1, 1);
+ QUERY PLAN
+-------------------------------------------------------------------------------
+ Finalize Aggregate (actual rows=1 loops=1)
+ -> Gather (actual rows=3 loops=1)
+ Workers Planned: 2
+ Workers Launched: 2
+ -> Partial Aggregate (actual rows=1 loops=3)
+ -> Parallel Append (actual rows=0 loops=3)
+ -> Parallel Seq Scan on ab_a1_b1 (actual rows=0 loops=1)
+ Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
+ -> Parallel Seq Scan on ab_a1_b2 (actual rows=0 loops=1)
+ Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
+ -> Parallel Seq Scan on ab_a1_b3 (actual rows=0 loops=1)
+ Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
+ -> Parallel Seq Scan on ab_a2_b1 (never executed)
+ Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
+ -> Parallel Seq Scan on ab_a2_b2 (never executed)
+ Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
+ -> Parallel Seq Scan on ab_a2_b3 (never executed)
+ Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
+ -> Parallel Seq Scan on ab_a3_b1 (never executed)
+ Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
+ -> Parallel Seq Scan on ab_a3_b2 (never executed)
+ Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
+ -> Parallel Seq Scan on ab_a3_b3 (never executed)
+ Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
+(24 rows)
+
+explain (analyze, costs off, summary off, timing off) execute ab_q3 (2, 3, 3);
+ QUERY PLAN
+-------------------------------------------------------------------------------
+ Finalize Aggregate (actual rows=1 loops=1)
+ -> Gather (actual rows=3 loops=1)
+ Workers Planned: 2
+ Workers Launched: 2
+ -> Partial Aggregate (actual rows=1 loops=3)
+ -> Parallel Append (actual rows=0 loops=3)
+ -> Parallel Seq Scan on ab_a1_b1 (never executed)
+ Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
+ -> Parallel Seq Scan on ab_a1_b2 (never executed)
+ Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
+ -> Parallel Seq Scan on ab_a1_b3 (never executed)
+ Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
+ -> Parallel Seq Scan on ab_a2_b1 (actual rows=0 loops=1)
+ Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
+ -> Parallel Seq Scan on ab_a2_b2 (actual rows=0 loops=1)
+ Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
+ -> Parallel Seq Scan on ab_a2_b3 (actual rows=0 loops=1)
+ Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
+ -> Parallel Seq Scan on ab_a3_b1 (actual rows=0 loops=1)
+ Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
+ -> Parallel Seq Scan on ab_a3_b2 (actual rows=0 loops=1)
+ Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
+ -> Parallel Seq Scan on ab_a3_b3 (actual rows=0 loops=1)
+ Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
+(24 rows)
+
+-- try some params whose values do not belong to any partition
+explain (analyze, costs off, summary off, timing off) execute ab_q3 (33, 44, 55);
+ QUERY PLAN
+-------------------------------------------------------------------------------
+ Finalize Aggregate (actual rows=1 loops=1)
+ -> Gather (actual rows=3 loops=1)
+ Workers Planned: 2
+ Workers Launched: 2
+ -> Partial Aggregate (actual rows=1 loops=3)
+ -> Parallel Append (actual rows=0 loops=3)
+ -> Parallel Seq Scan on ab_a1_b1 (never executed)
+ Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
+ -> Parallel Seq Scan on ab_a1_b2 (never executed)
+ Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
+ -> Parallel Seq Scan on ab_a1_b3 (never executed)
+ Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
+ -> Parallel Seq Scan on ab_a2_b1 (never executed)
+ Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
+ -> Parallel Seq Scan on ab_a2_b2 (never executed)
+ Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
+ -> Parallel Seq Scan on ab_a2_b3 (never executed)
+ Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
+ -> Parallel Seq Scan on ab_a3_b1 (never executed)
+ Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
+ -> Parallel Seq Scan on ab_a3_b2 (never executed)
+ Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
+ -> Parallel Seq Scan on ab_a3_b3 (never executed)
+ Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
+(24 rows)
+
+-- test parallel Append with IN list and parameterized nested loops
+create table lprt_a (a int not null);
+-- insert some values we won't find in ab
+insert into lprt_a select 0 from generate_series(1,100);
+-- and insert some values that we should find.
+insert into lprt_a values(1),(1);
+analyze lprt_a;
+create index ab_a2_b1_a_idx on ab_a2_b1 (a);
+create index ab_a2_b2_a_idx on ab_a2_b2 (a);
+create index ab_a2_b3_a_idx on ab_a2_b3 (a);
+create index ab_a1_b1_a_idx on ab_a1_b1 (a);
+create index ab_a1_b2_a_idx on ab_a1_b2 (a);
+create index ab_a1_b3_a_idx on ab_a1_b3 (a);
+create index ab_a3_b1_a_idx on ab_a3_b1 (a);
+create index ab_a3_b2_a_idx on ab_a3_b2 (a);
+create index ab_a3_b3_a_idx on ab_a3_b3 (a);
+set enable_hashjoin = 0;
+set enable_mergejoin = 0;
+prepare ab_q4 (int, int, int) as
+select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in($1,$2,$3);
+execute ab_q4 (1, 2, 3);
+ avg
+-----
+
+(1 row)
+
+execute ab_q4 (1, 2, 3);
+ avg
+-----
+
+(1 row)
+
+execute ab_q4 (1, 2, 3);
+ avg
+-----
+
+(1 row)
+
+execute ab_q4 (1, 2, 3);
+ avg
+-----
+
+(1 row)
+
+execute ab_q4 (1, 2, 3);
+ avg
+-----
+
+(1 row)
+
+explain (analyze, costs off, summary off, timing off) execute ab_q4 (0, 0, 1);
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------
+ Finalize Aggregate (actual rows=1 loops=1)
+ -> Gather (actual rows=2 loops=1)
+ Workers Planned: 1
+ Workers Launched: 1
+ -> Partial Aggregate (actual rows=1 loops=2)
+ -> Nested Loop (actual rows=0 loops=2)
+ -> Parallel Seq Scan on lprt_a a (actual rows=51 loops=2)
+ Filter: (a = ANY ('{0,0,1}'::integer[]))
+ -> Append (actual rows=0 loops=102)
+ -> Index Only Scan using ab_a1_b1_a_idx on ab_a1_b1 (actual rows=0 loops=2)
+ Index Cond: (a = a.a)
+ Heap Fetches: 0
+ -> Index Only Scan using ab_a1_b2_a_idx on ab_a1_b2 (actual rows=0 loops=2)
+ Index Cond: (a = a.a)
+ Heap Fetches: 0
+ -> Index Only Scan using ab_a1_b3_a_idx on ab_a1_b3 (actual rows=0 loops=2)
+ Index Cond: (a = a.a)
+ Heap Fetches: 0
+ -> Index Only Scan using ab_a2_b1_a_idx on ab_a2_b1 (never executed)
+ Index Cond: (a = a.a)
+ Heap Fetches: 0
+ -> Index Only Scan using ab_a2_b2_a_idx on ab_a2_b2 (never executed)
+ Index Cond: (a = a.a)
+ Heap Fetches: 0
+ -> Index Only Scan using ab_a2_b3_a_idx on ab_a2_b3 (never executed)
+ Index Cond: (a = a.a)
+ Heap Fetches: 0
+ -> Index Only Scan using ab_a3_b1_a_idx on ab_a3_b1 (never executed)
+ Index Cond: (a = a.a)
+ Heap Fetches: 0
+ -> Index Only Scan using ab_a3_b2_a_idx on ab_a3_b2 (never executed)
+ Index Cond: (a = a.a)
+ Heap Fetches: 0
+ -> Index Only Scan using ab_a3_b3_a_idx on ab_a3_b3 (never executed)
+ Index Cond: (a = a.a)
+ Heap Fetches: 0
+(36 rows)
+
+insert into lprt_a values(3),(3);
+explain (analyze, costs off, summary off, timing off) execute ab_q4 (1, 0, 3);
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------
+ Finalize Aggregate (actual rows=1 loops=1)
+ -> Gather (actual rows=2 loops=1)
+ Workers Planned: 1
+ Workers Launched: 1
+ -> Partial Aggregate (actual rows=1 loops=2)
+ -> Nested Loop (actual rows=0 loops=2)
+ -> Parallel Seq Scan on lprt_a a (actual rows=52 loops=2)
+ Filter: (a = ANY ('{1,0,3}'::integer[]))
+ -> Append (actual rows=0 loops=104)
+ -> Index Only Scan using ab_a1_b1_a_idx on ab_a1_b1 (actual rows=0 loops=2)
+ Index Cond: (a = a.a)
+ Heap Fetches: 0
+ -> Index Only Scan using ab_a1_b2_a_idx on ab_a1_b2 (actual rows=0 loops=2)
+ Index Cond: (a = a.a)
+ Heap Fetches: 0
+ -> Index Only Scan using ab_a1_b3_a_idx on ab_a1_b3 (actual rows=0 loops=2)
+ Index Cond: (a = a.a)
+ Heap Fetches: 0
+ -> Index Only Scan using ab_a2_b1_a_idx on ab_a2_b1 (never executed)
+ Index Cond: (a = a.a)
+ Heap Fetches: 0
+ -> Index Only Scan using ab_a2_b2_a_idx on ab_a2_b2 (never executed)
+ Index Cond: (a = a.a)
+ Heap Fetches: 0
+ -> Index Only Scan using ab_a2_b3_a_idx on ab_a2_b3 (never executed)
+ Index Cond: (a = a.a)
+ Heap Fetches: 0
+ -> Index Only Scan using ab_a3_b1_a_idx on ab_a3_b1 (actual rows=0 loops=2)
+ Index Cond: (a = a.a)
+ Heap Fetches: 0
+ -> Index Only Scan using ab_a3_b2_a_idx on ab_a3_b2 (actual rows=0 loops=2)
+ Index Cond: (a = a.a)
+ Heap Fetches: 0
+ -> Index Only Scan using ab_a3_b3_a_idx on ab_a3_b3 (actual rows=0 loops=2)
+ Index Cond: (a = a.a)
+ Heap Fetches: 0
+(36 rows)
+
+explain (analyze, costs off, summary off, timing off) execute ab_q4 (1, 0, 0);
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------
+ Finalize Aggregate (actual rows=1 loops=1)
+ -> Gather (actual rows=2 loops=1)
+ Workers Planned: 1
+ Workers Launched: 1
+ -> Partial Aggregate (actual rows=1 loops=2)
+ -> Nested Loop (actual rows=0 loops=2)
+ -> Parallel Seq Scan on lprt_a a (actual rows=51 loops=2)
+ Filter: (a = ANY ('{1,0,0}'::integer[]))
+ Rows Removed by Filter: 1
+ -> Append (actual rows=0 loops=102)
+ -> Index Only Scan using ab_a1_b1_a_idx on ab_a1_b1 (actual rows=0 loops=2)
+ Index Cond: (a = a.a)
+ Heap Fetches: 0
+ -> Index Only Scan using ab_a1_b2_a_idx on ab_a1_b2 (actual rows=0 loops=2)
+ Index Cond: (a = a.a)
+ Heap Fetches: 0
+ -> Index Only Scan using ab_a1_b3_a_idx on ab_a1_b3 (actual rows=0 loops=2)
+ Index Cond: (a = a.a)
+ Heap Fetches: 0
+ -> Index Only Scan using ab_a2_b1_a_idx on ab_a2_b1 (never executed)
+ Index Cond: (a = a.a)
+ Heap Fetches: 0
+ -> Index Only Scan using ab_a2_b2_a_idx on ab_a2_b2 (never executed)
+ Index Cond: (a = a.a)
+ Heap Fetches: 0
+ -> Index Only Scan using ab_a2_b3_a_idx on ab_a2_b3 (never executed)
+ Index Cond: (a = a.a)
+ Heap Fetches: 0
+ -> Index Only Scan using ab_a3_b1_a_idx on ab_a3_b1 (never executed)
+ Index Cond: (a = a.a)
+ Heap Fetches: 0
+ -> Index Only Scan using ab_a3_b2_a_idx on ab_a3_b2 (never executed)
+ Index Cond: (a = a.a)
+ Heap Fetches: 0
+ -> Index Only Scan using ab_a3_b3_a_idx on ab_a3_b3 (never executed)
+ Index Cond: (a = a.a)
+ Heap Fetches: 0
+(37 rows)
+
+delete from lprt_a where a = 1;
+explain (analyze, costs off, summary off, timing off) execute ab_q4 (1, 0, 0);
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------
+ Finalize Aggregate (actual rows=1 loops=1)
+ -> Gather (actual rows=2 loops=1)
+ Workers Planned: 1
+ Workers Launched: 1
+ -> Partial Aggregate (actual rows=1 loops=2)
+ -> Nested Loop (actual rows=0 loops=2)
+ -> Parallel Seq Scan on lprt_a a (actual rows=50 loops=2)
+ Filter: (a = ANY ('{1,0,0}'::integer[]))
+ Rows Removed by Filter: 1
+ -> Append (actual rows=0 loops=100)
+ -> Index Only Scan using ab_a1_b1_a_idx on ab_a1_b1 (never executed)
+ Index Cond: (a = a.a)
+ Heap Fetches: 0
+ -> Index Only Scan using ab_a1_b2_a_idx on ab_a1_b2 (never executed)
+ Index Cond: (a = a.a)
+ Heap Fetches: 0
+ -> Index Only Scan using ab_a1_b3_a_idx on ab_a1_b3 (never executed)
+ Index Cond: (a = a.a)
+ Heap Fetches: 0
+ -> Index Only Scan using ab_a2_b1_a_idx on ab_a2_b1 (never executed)
+ Index Cond: (a = a.a)
+ Heap Fetches: 0
+ -> Index Only Scan using ab_a2_b2_a_idx on ab_a2_b2 (never executed)
+ Index Cond: (a = a.a)
+ Heap Fetches: 0
+ -> Index Only Scan using ab_a2_b3_a_idx on ab_a2_b3 (never executed)
+ Index Cond: (a = a.a)
+ Heap Fetches: 0
+ -> Index Only Scan using ab_a3_b1_a_idx on ab_a3_b1 (never executed)
+ Index Cond: (a = a.a)
+ Heap Fetches: 0
+ -> Index Only Scan using ab_a3_b2_a_idx on ab_a3_b2 (never executed)
+ Index Cond: (a = a.a)
+ Heap Fetches: 0
+ -> Index Only Scan using ab_a3_b3_a_idx on ab_a3_b3 (never executed)
+ Index Cond: (a = a.a)
+ Heap Fetches: 0
+(37 rows)
+
+reset enable_hashjoin;
+reset enable_mergejoin;
+reset parallel_setup_cost;
+reset parallel_tuple_cost;
+reset min_parallel_table_scan_size;
+reset max_parallel_workers_per_gather;
+-- Test run-time partition pruning with an initplan
+explain (analyze, costs off, summary off, timing off)
+select * from ab where a = (select max(a) from lprt_a) and b = (select max(a)-1 from lprt_a);
+ QUERY PLAN
+-------------------------------------------------------------------------
+ Append (actual rows=0 loops=1)
+ InitPlan 1 (returns $0)
+ -> Aggregate (actual rows=1 loops=1)
+ -> Seq Scan on lprt_a (actual rows=102 loops=1)
+ InitPlan 2 (returns $1)
+ -> Aggregate (actual rows=1 loops=1)
+ -> Seq Scan on lprt_a lprt_a_1 (actual rows=102 loops=1)
+ -> Bitmap Heap Scan on ab_a1_b1 (never executed)
+ Recheck Cond: (a = $0)
+ Filter: (b = $1)
+ -> Bitmap Index Scan on ab_a1_b1_a_idx (never executed)
+ Index Cond: (a = $0)
+ -> Bitmap Heap Scan on ab_a1_b2 (never executed)
+ Recheck Cond: (a = $0)
+ Filter: (b = $1)
+ -> Bitmap Index Scan on ab_a1_b2_a_idx (never executed)
+ Index Cond: (a = $0)
+ -> Bitmap Heap Scan on ab_a1_b3 (never executed)
+ Recheck Cond: (a = $0)
+ Filter: (b = $1)
+ -> Bitmap Index Scan on ab_a1_b3_a_idx (never executed)
+ Index Cond: (a = $0)
+ -> Bitmap Heap Scan on ab_a2_b1 (never executed)
+ Recheck Cond: (a = $0)
+ Filter: (b = $1)
+ -> Bitmap Index Scan on ab_a2_b1_a_idx (never executed)
+ Index Cond: (a = $0)
+ -> Bitmap Heap Scan on ab_a2_b2 (never executed)
+ Recheck Cond: (a = $0)
+ Filter: (b = $1)
+ -> Bitmap Index Scan on ab_a2_b2_a_idx (never executed)
+ Index Cond: (a = $0)
+ -> Bitmap Heap Scan on ab_a2_b3 (never executed)
+ Recheck Cond: (a = $0)
+ Filter: (b = $1)
+ -> Bitmap Index Scan on ab_a2_b3_a_idx (never executed)
+ Index Cond: (a = $0)
+ -> Bitmap Heap Scan on ab_a3_b1 (never executed)
+ Recheck Cond: (a = $0)
+ Filter: (b = $1)
+ -> Bitmap Index Scan on ab_a3_b1_a_idx (never executed)
+ Index Cond: (a = $0)
+ -> Bitmap Heap Scan on ab_a3_b2 (actual rows=0 loops=1)
+ Recheck Cond: (a = $0)
+ Filter: (b = $1)
+ -> Bitmap Index Scan on ab_a3_b2_a_idx (actual rows=0 loops=1)
+ Index Cond: (a = $0)
+ -> Bitmap Heap Scan on ab_a3_b3 (never executed)
+ Recheck Cond: (a = $0)
+ Filter: (b = $1)
+ -> Bitmap Index Scan on ab_a3_b3_a_idx (never executed)
+ Index Cond: (a = $0)
+(52 rows)
+
+deallocate ab_q1;
+deallocate ab_q2;
+deallocate ab_q3;
+deallocate ab_q4;
+drop table ab, lprt_a;
+-- join
+create table tbl1(col1 int);
+insert into tbl1 values (501), (505);
+-- basic table
+create table tprt (col1 int) partition by range (col1);
+create table tprt_1 partition of tprt for values from (1) to (501);
+create table tprt_2 partition of tprt for values from (501) to (1001);
+create table tprt_3 partition of tprt for values from (1001) to (2001);
+create table tprt_4 partition of tprt for values from (2001) to (3001);
+create table tprt_5 partition of tprt for values from (3001) to (4001);
+create table tprt_6 partition of tprt for values from (4001) to (5001);
+create index tprt1_idx on tprt_1 (col1);
+create index tprt2_idx on tprt_2 (col1);
+create index tprt3_idx on tprt_3 (col1);
+create index tprt4_idx on tprt_4 (col1);
+create index tprt5_idx on tprt_5 (col1);
+create index tprt6_idx on tprt_6 (col1);
+insert into tprt values (10), (20), (501), (502), (505), (1001), (4500);
+set enable_hashjoin = off;
+set enable_mergejoin = off;
+explain (analyze, costs off, summary off, timing off)
+select * from tbl1 join tprt on tbl1.col1 > tprt.col1;
+ QUERY PLAN
+-------------------------------------------------------------------------------
+ Nested Loop (actual rows=6 loops=1)
+ -> Seq Scan on tbl1 (actual rows=2 loops=1)
+ -> Append (actual rows=3 loops=2)
+ -> Index Only Scan using tprt1_idx on tprt_1 (actual rows=2 loops=2)
+ Index Cond: (col1 < tbl1.col1)
+ Heap Fetches: 4
+ -> Index Only Scan using tprt2_idx on tprt_2 (actual rows=2 loops=1)
+ Index Cond: (col1 < tbl1.col1)
+ Heap Fetches: 2
+ -> Index Only Scan using tprt3_idx on tprt_3 (never executed)
+ Index Cond: (col1 < tbl1.col1)
+ Heap Fetches: 0
+ -> Index Only Scan using tprt4_idx on tprt_4 (never executed)
+ Index Cond: (col1 < tbl1.col1)
+ Heap Fetches: 0
+ -> Index Only Scan using tprt5_idx on tprt_5 (never executed)
+ Index Cond: (col1 < tbl1.col1)
+ Heap Fetches: 0
+ -> Index Only Scan using tprt6_idx on tprt_6 (never executed)
+ Index Cond: (col1 < tbl1.col1)
+ Heap Fetches: 0
+(21 rows)
+
+explain (analyze, costs off, summary off, timing off)
+select * from tbl1 join tprt on tbl1.col1 = tprt.col1;
+ QUERY PLAN
+-------------------------------------------------------------------------------
+ Nested Loop (actual rows=2 loops=1)
+ -> Seq Scan on tbl1 (actual rows=2 loops=1)
+ -> Append (actual rows=1 loops=2)
+ -> Index Only Scan using tprt1_idx on tprt_1 (never executed)
+ Index Cond: (col1 = tbl1.col1)
+ Heap Fetches: 0
+ -> Index Only Scan using tprt2_idx on tprt_2 (actual rows=1 loops=2)
+ Index Cond: (col1 = tbl1.col1)
+ Heap Fetches: 2
+ -> Index Only Scan using tprt3_idx on tprt_3 (never executed)
+ Index Cond: (col1 = tbl1.col1)
+ Heap Fetches: 0
+ -> Index Only Scan using tprt4_idx on tprt_4 (never executed)
+ Index Cond: (col1 = tbl1.col1)
+ Heap Fetches: 0
+ -> Index Only Scan using tprt5_idx on tprt_5 (never executed)
+ Index Cond: (col1 = tbl1.col1)
+ Heap Fetches: 0
+ -> Index Only Scan using tprt6_idx on tprt_6 (never executed)
+ Index Cond: (col1 = tbl1.col1)
+ Heap Fetches: 0
+(21 rows)
+
+select tbl1.col1, tprt.col1 from tbl1
+inner join tprt on tbl1.col1 > tprt.col1
+order by tbl1.col1, tprt.col1;
+ col1 | col1
+------+------
+ 501 | 10
+ 501 | 20
+ 505 | 10
+ 505 | 20
+ 505 | 501
+ 505 | 502
+(6 rows)
+
+select tbl1.col1, tprt.col1 from tbl1
+inner join tprt on tbl1.col1 = tprt.col1
+order by tbl1.col1, tprt.col1;
+ col1 | col1
+------+------
+ 501 | 501
+ 505 | 505
+(2 rows)
+
+-- multiple partitions
+insert into tbl1 values (1001), (1010), (1011);
+explain (analyze, costs off, summary off, timing off)
+select * from tbl1 inner join tprt on tbl1.col1 > tprt.col1;
+ QUERY PLAN
+-------------------------------------------------------------------------------
+ Nested Loop (actual rows=23 loops=1)
+ -> Seq Scan on tbl1 (actual rows=5 loops=1)
+ -> Append (actual rows=5 loops=5)
+ -> Index Only Scan using tprt1_idx on tprt_1 (actual rows=2 loops=5)
+ Index Cond: (col1 < tbl1.col1)
+ Heap Fetches: 10
+ -> Index Only Scan using tprt2_idx on tprt_2 (actual rows=3 loops=4)
+ Index Cond: (col1 < tbl1.col1)
+ Heap Fetches: 11
+ -> Index Only Scan using tprt3_idx on tprt_3 (actual rows=1 loops=2)
+ Index Cond: (col1 < tbl1.col1)
+ Heap Fetches: 2
+ -> Index Only Scan using tprt4_idx on tprt_4 (never executed)
+ Index Cond: (col1 < tbl1.col1)
+ Heap Fetches: 0
+ -> Index Only Scan using tprt5_idx on tprt_5 (never executed)
+ Index Cond: (col1 < tbl1.col1)
+ Heap Fetches: 0
+ -> Index Only Scan using tprt6_idx on tprt_6 (never executed)
+ Index Cond: (col1 < tbl1.col1)
+ Heap Fetches: 0
+(21 rows)
+
+explain (analyze, costs off, summary off, timing off)
+select * from tbl1 inner join tprt on tbl1.col1 = tprt.col1;
+ QUERY PLAN
+-------------------------------------------------------------------------------
+ Nested Loop (actual rows=3 loops=1)
+ -> Seq Scan on tbl1 (actual rows=5 loops=1)
+ -> Append (actual rows=1 loops=5)
+ -> Index Only Scan using tprt1_idx on tprt_1 (never executed)
+ Index Cond: (col1 = tbl1.col1)
+ Heap Fetches: 0
+ -> Index Only Scan using tprt2_idx on tprt_2 (actual rows=1 loops=2)
+ Index Cond: (col1 = tbl1.col1)
+ Heap Fetches: 2
+ -> Index Only Scan using tprt3_idx on tprt_3 (actual rows=0 loops=3)
+ Index Cond: (col1 = tbl1.col1)
+ Heap Fetches: 1
+ -> Index Only Scan using tprt4_idx on tprt_4 (never executed)
+ Index Cond: (col1 = tbl1.col1)
+ Heap Fetches: 0
+ -> Index Only Scan using tprt5_idx on tprt_5 (never executed)
+ Index Cond: (col1 = tbl1.col1)
+ Heap Fetches: 0
+ -> Index Only Scan using tprt6_idx on tprt_6 (never executed)
+ Index Cond: (col1 = tbl1.col1)
+ Heap Fetches: 0
+(21 rows)
+
+select tbl1.col1, tprt.col1 from tbl1
+inner join tprt on tbl1.col1 > tprt.col1
+order by tbl1.col1, tprt.col1;
+ col1 | col1
+------+------
+ 501 | 10
+ 501 | 20
+ 505 | 10
+ 505 | 20
+ 505 | 501
+ 505 | 502
+ 1001 | 10
+ 1001 | 20
+ 1001 | 501
+ 1001 | 502
+ 1001 | 505
+ 1010 | 10
+ 1010 | 20
+ 1010 | 501
+ 1010 | 502
+ 1010 | 505
+ 1010 | 1001
+ 1011 | 10
+ 1011 | 20
+ 1011 | 501
+ 1011 | 502
+ 1011 | 505
+ 1011 | 1001
+(23 rows)
+
+select tbl1.col1, tprt.col1 from tbl1
+inner join tprt on tbl1.col1 = tprt.col1
+order by tbl1.col1, tprt.col1;
+ col1 | col1
+------+------
+ 501 | 501
+ 505 | 505
+ 1001 | 1001
+(3 rows)
+
+-- last partition
+delete from tbl1;
+insert into tbl1 values (4400);
+explain (analyze, costs off, summary off, timing off)
+select * from tbl1 join tprt on tbl1.col1 < tprt.col1;
+ QUERY PLAN
+-------------------------------------------------------------------------------
+ Nested Loop (actual rows=1 loops=1)
+ -> Seq Scan on tbl1 (actual rows=1 loops=1)
+ -> Append (actual rows=1 loops=1)
+ -> Index Only Scan using tprt1_idx on tprt_1 (never executed)
+ Index Cond: (col1 > tbl1.col1)
+ Heap Fetches: 0
+ -> Index Only Scan using tprt2_idx on tprt_2 (never executed)
+ Index Cond: (col1 > tbl1.col1)
+ Heap Fetches: 0
+ -> Index Only Scan using tprt3_idx on tprt_3 (never executed)
+ Index Cond: (col1 > tbl1.col1)
+ Heap Fetches: 0
+ -> Index Only Scan using tprt4_idx on tprt_4 (never executed)
+ Index Cond: (col1 > tbl1.col1)
+ Heap Fetches: 0
+ -> Index Only Scan using tprt5_idx on tprt_5 (never executed)
+ Index Cond: (col1 > tbl1.col1)
+ Heap Fetches: 0
+ -> Index Only Scan using tprt6_idx on tprt_6 (actual rows=1 loops=1)
+ Index Cond: (col1 > tbl1.col1)
+ Heap Fetches: 1
+(21 rows)
+
+select tbl1.col1, tprt.col1 from tbl1
+inner join tprt on tbl1.col1 < tprt.col1
+order by tbl1.col1, tprt.col1;
+ col1 | col1
+------+------
+ 4400 | 4500
+(1 row)
+
+-- no matching partition
+delete from tbl1;
+insert into tbl1 values (10000);
+explain (analyze, costs off, summary off, timing off)
+select * from tbl1 join tprt on tbl1.col1 = tprt.col1;
+ QUERY PLAN
+------------------------------------------------------------------------
+ Nested Loop (actual rows=0 loops=1)
+ -> Seq Scan on tbl1 (actual rows=1 loops=1)
+ -> Append (actual rows=0 loops=1)
+ -> Index Only Scan using tprt1_idx on tprt_1 (never executed)
+ Index Cond: (col1 = tbl1.col1)
+ Heap Fetches: 0
+ -> Index Only Scan using tprt2_idx on tprt_2 (never executed)
+ Index Cond: (col1 = tbl1.col1)
+ Heap Fetches: 0
+ -> Index Only Scan using tprt3_idx on tprt_3 (never executed)
+ Index Cond: (col1 = tbl1.col1)
+ Heap Fetches: 0
+ -> Index Only Scan using tprt4_idx on tprt_4 (never executed)
+ Index Cond: (col1 = tbl1.col1)
+ Heap Fetches: 0
+ -> Index Only Scan using tprt5_idx on tprt_5 (never executed)
+ Index Cond: (col1 = tbl1.col1)
+ Heap Fetches: 0
+ -> Index Only Scan using tprt6_idx on tprt_6 (never executed)
+ Index Cond: (col1 = tbl1.col1)
+ Heap Fetches: 0
+(21 rows)
+
+select tbl1.col1, tprt.col1 from tbl1
+inner join tprt on tbl1.col1 = tprt.col1
+order by tbl1.col1, tprt.col1;
+ col1 | col1
+------+------
+(0 rows)
+
+drop table tbl1, tprt;
+-- test with columns defined in varying orders between each level
+create table part_abc (a int not null, b int not null, c int not null) partition by list (a);
+create table part_bac (b int not null, a int not null, c int not null) partition by list (b);
+create table part_cab (c int not null, a int not null, b int not null) partition by list (c);
+create table part_abc_p1 (a int not null, b int not null, c int not null);
+alter table part_abc attach partition part_bac for values in(1);
+alter table part_bac attach partition part_cab for values in(2);
+alter table part_cab attach partition part_abc_p1 for values in(3);
+prepare part_abc_q1 (int, int, int) as
+select * from part_abc where a = $1 and b = $2 and c = $3;
+-- Execute query 5 times to allow choose_custom_plan
+-- to start considering a generic plan.
+execute part_abc_q1 (1, 2, 3);
+ a | b | c
+---+---+---
+(0 rows)
+
+execute part_abc_q1 (1, 2, 3);
+ a | b | c
+---+---+---
+(0 rows)
+
+execute part_abc_q1 (1, 2, 3);
+ a | b | c
+---+---+---
+(0 rows)
+
+execute part_abc_q1 (1, 2, 3);
+ a | b | c
+---+---+---
+(0 rows)
+
+execute part_abc_q1 (1, 2, 3);
+ a | b | c
+---+---+---
+(0 rows)
+
+-- single partition should be scanned.
+explain (analyze, costs off, summary off, timing off) execute part_abc_q1 (1, 2, 3);
+ QUERY PLAN
+-------------------------------------------------------
+ Append (actual rows=0 loops=1)
+ -> Seq Scan on part_abc_p1 (actual rows=0 loops=1)
+ Filter: ((a = $1) AND (b = $2) AND (c = $3))
+(3 rows)
+
+deallocate part_abc_q1;
+drop table part_abc;
diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sql
index 6921e39..81ee321 100644
--- a/src/test/regress/sql/partition_prune.sql
+++ b/src/test/regress/sql/partition_prune.sql
@@ -225,3 +225,255 @@ explain (costs off) select * from lp where (a <> 'a' and a <> 'd') or a is null;
explain (costs off) select * from rlp where a = 15 and b <> 'ab' and b <> 'cd' and b <> 'xy' and b is not null;
drop table lp, coll_pruning, rlp, mc3p, mc2p, boolpart, hp, rp;
+
+--
+-- Test runtime partitioning
+--
+create table ab (a int not null, b int not null) partition by list (a);
+create table ab_a2 partition of ab for values in(2) partition by list (b);
+create table ab_a2_b1 partition of ab_a2 for values in (1);
+create table ab_a2_b2 partition of ab_a2 for values in (2);
+create table ab_a2_b3 partition of ab_a2 for values in (3);
+create table ab_a1 partition of ab for values in(1) partition by list (b);
+create table ab_a1_b1 partition of ab_a1 for values in (1);
+create table ab_a1_b2 partition of ab_a1 for values in (2);
+create table ab_a1_b3 partition of ab_a1 for values in (3);
+create table ab_a3 partition of ab for values in(3) partition by list (b);
+create table ab_a3_b1 partition of ab_a3 for values in (1);
+create table ab_a3_b2 partition of ab_a3 for values in (2);
+create table ab_a3_b3 partition of ab_a3 for values in (3);
+
+prepare ab_q1 (int, int, int) as
+select * from ab where a between $1 and $2 and b <= $3;
+
+-- Execute query 5 times to allow choose_custom_plan
+-- to start considering a generic plan.
+execute ab_q1 (1, 8, 3);
+execute ab_q1 (1, 8, 3);
+execute ab_q1 (1, 8, 3);
+execute ab_q1 (1, 8, 3);
+execute ab_q1 (1, 8, 3);
+
+explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2, 3);
+explain (analyze, costs off, summary off, timing off) execute ab_q1 (1, 2, 3);
+
+deallocate ab_q1;
+
+-- runtime pruning after optimizer pruning
+prepare ab_q1 (int, int) as
+select a from ab where a between $1 and $2 and b < 3;
+
+-- Execute query 5 times to allow choose_custom_plan
+-- to start considering a generic plan.
+execute ab_q1 (1, 8);
+execute ab_q1 (1, 8);
+execute ab_q1 (1, 8);
+execute ab_q1 (1, 8);
+execute ab_q1 (1, 8);
+
+explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2);
+explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 4);
+
+-- parallel append
+prepare ab_q2 (int, int) as
+select avg(a) from ab where a between $1 and $2 and b < 4;
+
+-- encourage use of parallel plans
+set parallel_setup_cost = 0;
+set parallel_tuple_cost = 0;
+set min_parallel_table_scan_size = 0;
+set max_parallel_workers_per_gather = 2;
+
+-- Execute query 5 times to allow choose_custom_plan
+-- to start considering a generic plan.
+execute ab_q2 (1, 8);
+execute ab_q2 (1, 8);
+execute ab_q2 (1, 8);
+execute ab_q2 (1, 8);
+execute ab_q2 (1, 8);
+
+explain (analyze, costs off, summary off, timing off) execute ab_q2 (2, 2);
+
+-- Test run-time pruning with IN lists.
+prepare ab_q3 (int, int, int) as
+select avg(a) from ab where a in($1,$2,$3) and b < 4;
+
+-- Execute query 5 times to allow choose_custom_plan
+-- to start considering a generic plan.
+execute ab_q3 (1, 2, 3);
+execute ab_q3 (1, 2, 3);
+execute ab_q3 (1, 2, 3);
+execute ab_q3 (1, 2, 3);
+execute ab_q3 (1, 2, 3);
+
+explain (analyze, costs off, summary off, timing off) execute ab_q3 (1, 1, 1);
+explain (analyze, costs off, summary off, timing off) execute ab_q3 (2, 3, 3);
+
+-- try some params whose values do not belong to any partition
+explain (analyze, costs off, summary off, timing off) execute ab_q3 (33, 44, 55);
+
+-- test parallel Append with IN list and parameterized nested loops
+create table lprt_a (a int not null);
+-- insert some values we won't find in ab
+insert into lprt_a select 0 from generate_series(1,100);
+
+-- and insert some values that we should find.
+insert into lprt_a values(1),(1);
+
+analyze lprt_a;
+
+create index ab_a2_b1_a_idx on ab_a2_b1 (a);
+create index ab_a2_b2_a_idx on ab_a2_b2 (a);
+create index ab_a2_b3_a_idx on ab_a2_b3 (a);
+create index ab_a1_b1_a_idx on ab_a1_b1 (a);
+create index ab_a1_b2_a_idx on ab_a1_b2 (a);
+create index ab_a1_b3_a_idx on ab_a1_b3 (a);
+create index ab_a3_b1_a_idx on ab_a3_b1 (a);
+create index ab_a3_b2_a_idx on ab_a3_b2 (a);
+create index ab_a3_b3_a_idx on ab_a3_b3 (a);
+
+set enable_hashjoin = 0;
+set enable_mergejoin = 0;
+
+prepare ab_q4 (int, int, int) as
+select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in($1,$2,$3);
+execute ab_q4 (1, 2, 3);
+execute ab_q4 (1, 2, 3);
+execute ab_q4 (1, 2, 3);
+execute ab_q4 (1, 2, 3);
+execute ab_q4 (1, 2, 3);
+
+explain (analyze, costs off, summary off, timing off) execute ab_q4 (0, 0, 1);
+
+insert into lprt_a values(3),(3);
+
+explain (analyze, costs off, summary off, timing off) execute ab_q4 (1, 0, 3);
+explain (analyze, costs off, summary off, timing off) execute ab_q4 (1, 0, 0);
+
+delete from lprt_a where a = 1;
+
+explain (analyze, costs off, summary off, timing off) execute ab_q4 (1, 0, 0);
+
+reset enable_hashjoin;
+reset enable_mergejoin;
+reset parallel_setup_cost;
+reset parallel_tuple_cost;
+reset min_parallel_table_scan_size;
+reset max_parallel_workers_per_gather;
+
+-- Test run-time partition pruning with an initplan
+explain (analyze, costs off, summary off, timing off)
+select * from ab where a = (select max(a) from lprt_a) and b = (select max(a)-1 from lprt_a);
+
+deallocate ab_q1;
+deallocate ab_q2;
+deallocate ab_q3;
+deallocate ab_q4;
+
+drop table ab, lprt_a;
+
+-- join
+create table tbl1(col1 int);
+insert into tbl1 values (501), (505);
+
+-- basic table
+create table tprt (col1 int) partition by range (col1);
+create table tprt_1 partition of tprt for values from (1) to (501);
+create table tprt_2 partition of tprt for values from (501) to (1001);
+create table tprt_3 partition of tprt for values from (1001) to (2001);
+create table tprt_4 partition of tprt for values from (2001) to (3001);
+create table tprt_5 partition of tprt for values from (3001) to (4001);
+create table tprt_6 partition of tprt for values from (4001) to (5001);
+
+create index tprt1_idx on tprt_1 (col1);
+create index tprt2_idx on tprt_2 (col1);
+create index tprt3_idx on tprt_3 (col1);
+create index tprt4_idx on tprt_4 (col1);
+create index tprt5_idx on tprt_5 (col1);
+create index tprt6_idx on tprt_6 (col1);
+
+insert into tprt values (10), (20), (501), (502), (505), (1001), (4500);
+
+set enable_hashjoin = off;
+set enable_mergejoin = off;
+
+explain (analyze, costs off, summary off, timing off)
+select * from tbl1 join tprt on tbl1.col1 > tprt.col1;
+
+explain (analyze, costs off, summary off, timing off)
+select * from tbl1 join tprt on tbl1.col1 = tprt.col1;
+
+select tbl1.col1, tprt.col1 from tbl1
+inner join tprt on tbl1.col1 > tprt.col1
+order by tbl1.col1, tprt.col1;
+
+select tbl1.col1, tprt.col1 from tbl1
+inner join tprt on tbl1.col1 = tprt.col1
+order by tbl1.col1, tprt.col1;
+
+-- multiple partitions
+insert into tbl1 values (1001), (1010), (1011);
+explain (analyze, costs off, summary off, timing off)
+select * from tbl1 inner join tprt on tbl1.col1 > tprt.col1;
+
+explain (analyze, costs off, summary off, timing off)
+select * from tbl1 inner join tprt on tbl1.col1 = tprt.col1;
+
+select tbl1.col1, tprt.col1 from tbl1
+inner join tprt on tbl1.col1 > tprt.col1
+order by tbl1.col1, tprt.col1;
+
+select tbl1.col1, tprt.col1 from tbl1
+inner join tprt on tbl1.col1 = tprt.col1
+order by tbl1.col1, tprt.col1;
+
+-- last partition
+delete from tbl1;
+insert into tbl1 values (4400);
+explain (analyze, costs off, summary off, timing off)
+select * from tbl1 join tprt on tbl1.col1 < tprt.col1;
+
+select tbl1.col1, tprt.col1 from tbl1
+inner join tprt on tbl1.col1 < tprt.col1
+order by tbl1.col1, tprt.col1;
+
+-- no matching partition
+delete from tbl1;
+insert into tbl1 values (10000);
+explain (analyze, costs off, summary off, timing off)
+select * from tbl1 join tprt on tbl1.col1 = tprt.col1;
+
+select tbl1.col1, tprt.col1 from tbl1
+inner join tprt on tbl1.col1 = tprt.col1
+order by tbl1.col1, tprt.col1;
+
+drop table tbl1, tprt;
+
+-- test with columns defined in varying orders between each level
+
+create table part_abc (a int not null, b int not null, c int not null) partition by list (a);
+create table part_bac (b int not null, a int not null, c int not null) partition by list (b);
+create table part_cab (c int not null, a int not null, b int not null) partition by list (c);
+create table part_abc_p1 (a int not null, b int not null, c int not null);
+
+alter table part_abc attach partition part_bac for values in(1);
+alter table part_bac attach partition part_cab for values in(2);
+alter table part_cab attach partition part_abc_p1 for values in(3);
+
+prepare part_abc_q1 (int, int, int) as
+select * from part_abc where a = $1 and b = $2 and c = $3;
+
+-- Execute query 5 times to allow choose_custom_plan
+-- to start considering a generic plan.
+execute part_abc_q1 (1, 2, 3);
+execute part_abc_q1 (1, 2, 3);
+execute part_abc_q1 (1, 2, 3);
+execute part_abc_q1 (1, 2, 3);
+execute part_abc_q1 (1, 2, 3);
+
+-- single partition should be scanned.
+explain (analyze, costs off, summary off, timing off) execute part_abc_q1 (1, 2, 3);
+
+deallocate part_abc_q1;
+
+drop table part_abc;