From f25937e866b8c5a99c5a0be64a3d3036dfb17b47 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?=E4=B8=80=E6=8C=83?= Date: Thu, 4 Mar 2021 12:08:41 +0800 Subject: [PATCH v1] adjust cost model for partition prune case. --- src/backend/optimizer/path/allpaths.c | 15 +- src/backend/optimizer/path/costsize.c | 348 +++++++++++++++++- src/backend/optimizer/util/pathnode.c | 2 +- src/include/optimizer/cost.h | 3 +- src/test/regress/expected/partition_prune.out | 280 +++++++------- 5 files changed, 485 insertions(+), 163 deletions(-) diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c index d73ac562eb..f4bd73b3ed 100644 --- a/src/backend/optimizer/path/allpaths.c +++ b/src/backend/optimizer/path/allpaths.c @@ -947,7 +947,7 @@ set_append_rel_size(PlannerInfo *root, RelOptInfo *rel, Index rti, RangeTblEntry *rte) { int parentRTindex = rti; - bool has_live_children; + int n_live_children = 0; double parent_rows; double parent_size; double *parent_attrsizes; @@ -984,7 +984,6 @@ set_append_rel_size(PlannerInfo *root, RelOptInfo *rel, * Note: if you consider changing this logic, beware that child rels could * have zero rows and/or width, if they were excluded by constraints. */ - has_live_children = false; parent_rows = 0; parent_size = 0; nattrs = rel->max_attr - rel->min_attr + 1; @@ -1112,7 +1111,7 @@ set_append_rel_size(PlannerInfo *root, RelOptInfo *rel, continue; /* We have at least one live child. */ - has_live_children = true; + n_live_children += 1; /* * If any live child is not parallel-safe, treat the whole appendrel @@ -1169,7 +1168,7 @@ set_append_rel_size(PlannerInfo *root, RelOptInfo *rel, } } - if (has_live_children) + if (n_live_children > 0) { /* * Save the finished size estimates. @@ -1182,6 +1181,14 @@ set_append_rel_size(PlannerInfo *root, RelOptInfo *rel, for (i = 0; i < nattrs; i++) rel->attr_widths[i] = rint(parent_attrsizes[i] / parent_rows); + if (enable_partition_pruning && IS_PARTITIONED_REL(rel)) + { + /* reduce the rows that can be pruned at init partition prune stage */ + rel->rows *= calculate_relrows_prune_ratio(rel, rte, n_live_children); + if (rel->rows < 1) + rel->rows = 1; + } + /* * Set "raw tuples" count equal to "rows" for the appendrel; needed * because some places assume rel->tuples is valid for any baserel. diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c index a25b674a19..0ac87ea9f2 100644 --- a/src/backend/optimizer/path/costsize.c +++ b/src/backend/optimizer/path/costsize.c @@ -76,6 +76,7 @@ #include "access/amapi.h" #include "access/htup_details.h" #include "access/tsmapi.h" +#include "catalog/pg_class.h" #include "executor/executor.h" #include "executor/nodeAgg.h" #include "executor/nodeHash.h" @@ -188,6 +189,20 @@ static double page_size(double tuples, int width); static double get_parallel_divisor(Path *path); +static bool is_simple_append(AppendPath *apath, PlannerInfo *root); +static bool is_exact_equal_partkey(Expr *clause, Expr *partkey, + PartitionScheme part_scheme, + bool external_param_only); +static bool is_exact_bool_equal_partkey(BoolExpr *boolexpr, Expr *partkey, + PartitionScheme part_scheme, + bool extern_param_only); +static bool has_exact_equal_partkey(List *clauses, Expr *partkey, + PartitionScheme part_scheme, + bool extern_param_only); +static double estimate_runtime_prune_ratio(AppendPath *apath, + PlannerInfo *root); + + /* * clamp_row_est * Force a row-count estimate to a sane value. @@ -1879,7 +1894,7 @@ cost_tuplesort(Cost *startup_cost, Cost *run_cost, /* * cost_incremental_sort - * Determines and returns the cost of sorting a relation incrementally, when + * Determines and returns the cost of sorting a relation incrementally, when * the input path is presorted by a prefix of the pathkeys. * * 'presorted_keys' is the number of leading pathkeys by which the input path @@ -2136,14 +2151,17 @@ append_nonpartial_cost(List *subpaths, int numpaths, int parallel_workers) * Determines and returns the cost of an Append node. */ void -cost_append(AppendPath *apath) +cost_append(AppendPath *apath, PlannerInfo *root) { ListCell *l; + double prune_ratio = 1; apath->path.startup_cost = 0; apath->path.total_cost = 0; apath->path.rows = 0; + prune_ratio = estimate_runtime_prune_ratio(apath, root); + if (apath->subpaths == NIL) return; @@ -2154,6 +2172,7 @@ cost_append(AppendPath *apath) if (pathkeys == NIL) { Path *subpath = (Path *) linitial(apath->subpaths); + double total_startup_cost = 0; /* * For an unordered, non-parallel-aware Append we take the startup @@ -2168,7 +2187,10 @@ cost_append(AppendPath *apath) apath->path.rows += subpath->rows; apath->path.total_cost += subpath->total_cost; + total_startup_cost += subpath->startup_cost; } + if (prune_ratio < 1) + apath->path.startup_cost = total_startup_cost; } else { @@ -2276,6 +2298,13 @@ cost_append(AppendPath *apath) apath->path.parallel_workers); } + apath->path.total_cost *= prune_ratio; + apath->path.startup_cost *= prune_ratio; + apath->path.rows *= prune_ratio; + + if (apath->path.rows < 1) + apath->path.rows = 1; + /* * Although Append does not do any selection or projection, it's not free; * add a small per-tuple overhead. @@ -5992,3 +6021,318 @@ compute_bitmap_pages(PlannerInfo *root, RelOptInfo *baserel, Path *bitmapqual, return pages_fetched; } + + +/* + * estimate_runtime_prune_ratio + * + * estimate a runtime prune ratio for a AppendPath. + */ +static double +estimate_runtime_prune_ratio(AppendPath *apath, PlannerInfo *root) +{ + List *prunequal; + RelOptInfo *rel; + Expr *partkey; + + if (!is_simple_append(apath, root)) + return 1.0; + + rel = apath->path.parent; + + prunequal = extract_actual_clauses(rel->baserestrictinfo, false); + + if (apath->path.param_info) + prunequal = list_concat(prunequal, apath->path.param_info->ppi_clauses); + + /* We only support 1 partition key right now, see is_simple_append */ + partkey = linitial(rel->partexprs[0]); + + /* + * We need to handle 3 cases correctly. a). WHERE partexpr = $1 or partexpr = $2; + * in this case, even it probably hit 2 partitions, but we still count the factor as + * 1 / rel->parts since each partition counts the 2 variables already. b). WHERE + * p.partexpr = p2.partexpr. The append in Nestloop inner plan just need to handle + * one for sure. c). WHERE (partexpr = $1 or partexpr = $2) AND p.partexpr2 = p2.partexpr. + * The outer plan follows rule a) and inner Nestloop plan follows rule b. + * At last, we just need to handle the factor as 1 / list_length(subpaths) for all + * the cases. + */ + if (has_exact_equal_partkey(prunequal, partkey, rel->part_scheme, false)) + return 1.0 / list_length(apath->subpaths); + + return 1.0; +} + + +double +calculate_relrows_prune_ratio(RelOptInfo *rel, RangeTblEntry *rte, int live_children) +{ + + Assert(IS_PARTITIONED_REL(rel)); + if (rel->part_scheme->partnatts == 1 && + rte->relkind == RELKIND_PARTITIONED_TABLE) + { + List *quals = rel->baserestrictinfo; + Expr *partkey = linitial(rel->partexprs[0]); + if (has_exact_equal_partkey(quals, partkey, rel->part_scheme, true /* extern param only */)) + return 1.0 / live_children; + } + return 1.0; +} + + +/* + * has_exact_equal_partkey + */ +static bool +has_exact_equal_partkey(List *clauses, Expr *partkey, + PartitionScheme part_scheme, + bool extern_param_only) +{ + ListCell *lc; + foreach(lc, clauses) + { + Expr *clause = lfirst(lc); + if (IsA(clause, RestrictInfo)) + clause = ((RestrictInfo *)clause)->clause; + + if (IsA(clause, BoolExpr)) + { + if (is_exact_bool_equal_partkey((BoolExpr*)clause, partkey, + part_scheme, extern_param_only)) + return true; + } + + if (is_exact_equal_partkey(clause, partkey, + part_scheme, extern_param_only)) + return true; + } + return false; +} + + +/* + * is_simple_append + * + * Just add some limitations for user case to make this patch + * easier. + */ +static bool +is_simple_append(AppendPath *apath, PlannerInfo *root) +{ + /* + * Append may comes from p, subp, Union, Union all + * or Append two join rel in partition wise case. + */ + + RelOptInfo *rel = apath->path.parent; + ListCell *lc; + + if (apath->path.parallel_aware) + /* We need to adjust both total_cost and startup_cost. If startup_cost + * is not adjust, the (runtime cost = total_cost - startup_cost) might + * less than 0, which will cause some bad result. However how to adjust + * the startup_cost is not clear, so I just not handle this case now. + */ + return false; + + if (!IS_PARTITIONED_REL(rel)) + /* May come from Union, UnionAll or partition wise join*/ + return false; + + if (bms_num_members(rel->relids) > 1) + /* For safety */ + return false; + + if (rel->part_scheme->partnatts != 1) + /* Only support 1 for simple */ + return false; + + /* + * Now it may still a partitoned rel or sub-partitioned rel. + * Currently the subpaths from sub-partitioned rel will be + * merged into parent->subpaths via accumulate_append_subpath + * without considering the cost of the Append, but cares about + * cost of its subpaths. This is not true any more when this + * feature involved. now I will just adjust the cost of the + * Append without touching accumulate_append_subpath. It should + * be done later. Filter out any sub-partitioned rel for now. + */ + + foreach(lc, apath->subpaths) + { + Path *path = (Path *) lfirst(lc); + RelOptInfo *pathrel = path->parent; + + if (pathrel->reloptkind != RELOPT_OTHER_MEMBER_REL) + return false; + if (bms_equal(pathrel->relids, apath->path.parent->relids)) + /* subpath from sub-partitioned rel*/ + return false; + } + + /* At last, I don't bother to handle the transated_vars for now */ + foreach(lc, apath->subpaths) + { + Path *subpath = (Path *) lfirst(lc); + AppendRelInfo *appinfo = root->append_rel_array[subpath->parent->relid]; + int i = 0; + ListCell *lc2; + if (appinfo == NULL) + { + Assert(false); + return false; + } + + foreach(lc2, appinfo->translated_vars) + { + Var *var = (Var *) lfirst(lc2); + i++; + if (var == NULL) + /* dropped column */ + continue; + if (!IsA(var, Var)) + { + Assert(false); + return false; + } + if (var->varattno != i) + return false; + } + } + return true; +} + + +/* + * similar with match_clause_to_partition_key. + */ +static bool +is_exact_equal_partkey(Expr *clause, Expr *partkey, + PartitionScheme part_scheme, + bool external_param_only) +{ + /* We only support one partkey for now */ + Oid partopfamily = part_scheme->partopfamily[0]; + + int op_strategy = InvalidStrategy; + Oid opno; + Expr *leftop, *rightop; + Oid op_lefttype, op_righttype; + + if (IsA(clause, OpExpr)) + { + bool found = false; + OpExpr *opexpr = (OpExpr *)clause; + opno = opexpr->opno; + leftop = (Expr *)get_leftop(clause); + rightop = (Expr *)get_rightop(clause); + while (IsA(leftop, RelabelType)) + leftop = ((RelabelType *) leftop)->arg; + + while (IsA(rightop, RelabelType)) + rightop = ((RelabelType *) rightop)->arg; + + if (equal(leftop, partkey)) + { + if (external_param_only) + { + if (IsA(rightop, Param) && castNode(Param, rightop)->paramkind == PARAM_EXTERN) + { + found = true; + } + } + else + found = true; + } + else if (equal(rightop, partkey)) + { + if (external_param_only) + { + if (IsA(leftop, Param) && castNode(Param, leftop)->paramkind == PARAM_EXTERN) + found = true; + } + else + found = true; + } + + if (!found) + return false; + + if (!op_in_opfamily(opno, partopfamily)) + return false; + + get_op_opfamily_properties(opno, partopfamily, + false, &op_strategy, + &op_lefttype, &op_righttype); + /* We only support BTEqualStrategyNumber on purpose, support other types + * not worthy the troubles and probably can't get a good res. */ + return op_strategy == BTEqualStrategyNumber; + } + else if (IsA(clause, ScalarArrayOpExpr)) + { + ScalarArrayOpExpr *scalar_array_opexpr = (ScalarArrayOpExpr *) clause; + opno = scalar_array_opexpr->opno; + if (!op_in_opfamily(opno, partopfamily)) + return false; + + get_op_opfamily_properties(opno, partopfamily, + false, &op_strategy, + &op_lefttype, &op_righttype); + if(op_strategy != BTEqualStrategyNumber) + return false; + leftop = linitial(scalar_array_opexpr->args); + if (!equal(leftop, partkey)) + return false; + if (external_param_only) + { + Expr *rexpr = lsecond(scalar_array_opexpr->args); + if (IsA(rexpr, ArrayCoerceExpr)) + rexpr = castNode(ArrayCoerceExpr, rexpr)->arg; + if (IsA(rexpr, ArrayExpr)) + { + List *elems = castNode(ArrayExpr, rexpr)->elements; + if (elems != NIL) + { + Expr * elem = linitial(elems); + if (IsA(elem, Param) && castNode(Param, elem)->paramkind == PARAM_EXTERN) + return true; + } + return false; + } + } + return true; + } + return false; +} + +static bool +is_exact_bool_equal_partkey(BoolExpr *boolexpr, Expr *partkey, + PartitionScheme part_scheme, + bool extern_param_only) +{ + ListCell *lc; + switch(boolexpr->boolop) + { + case AND_EXPR: + foreach(lc, boolexpr->args) + { + Expr *expr = (Expr *)lfirst(lc); + if (is_exact_equal_partkey(expr, partkey, part_scheme, extern_param_only)) + return true; + } + case OR_EXPR: + foreach(lc, boolexpr->args) + { + Expr *expr = (Expr *)lfirst(lc); + /* We need to make sure all the expr is a partkey */ + if (!is_exact_equal_partkey(expr, partkey, part_scheme, extern_param_only)) + return false; + } + return true; + case NOT_EXPR: + return false; + } + return false; +} diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c index 69b83071cf..0bfb638abc 100644 --- a/src/backend/optimizer/util/pathnode.c +++ b/src/backend/optimizer/util/pathnode.c @@ -1342,7 +1342,7 @@ create_append_path(PlannerInfo *root, pathnode->path.pathkeys = child->pathkeys; } else - cost_append(pathnode); + cost_append(pathnode, root); /* If the caller provided a row estimate, override the computed value. */ if (rows >= 0) diff --git a/src/include/optimizer/cost.h b/src/include/optimizer/cost.h index 1be93be098..38c1cef70f 100644 --- a/src/include/optimizer/cost.h +++ b/src/include/optimizer/cost.h @@ -110,7 +110,7 @@ extern void cost_incremental_sort(Path *path, Cost input_startup_cost, Cost input_total_cost, double input_tuples, int width, Cost comparison_cost, int sort_mem, double limit_tuples); -extern void cost_append(AppendPath *path); +extern void cost_append(AppendPath *path, PlannerInfo *root); extern void cost_merge_append(Path *path, PlannerInfo *root, List *pathkeys, int n_streams, Cost input_startup_cost, Cost input_total_cost, @@ -205,5 +205,6 @@ extern void set_foreign_size_estimates(PlannerInfo *root, RelOptInfo *rel); extern PathTarget *set_pathtarget_cost_width(PlannerInfo *root, PathTarget *target); extern double compute_bitmap_pages(PlannerInfo *root, RelOptInfo *baserel, Path *bitmapqual, int loop_count, Cost *cost, double *tuple); +extern double calculate_relrows_prune_ratio(RelOptInfo *rel, RangeTblEntry *rte, int live_children); #endif /* COST_H */ diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out index bde29e38a9..430014f508 100644 --- a/src/test/regress/expected/partition_prune.out +++ b/src/test/regress/expected/partition_prune.out @@ -1991,81 +1991,65 @@ select explain_parallel_append('execute ab_q4 (2, 2)'); prepare ab_q5 (int, int, int) as select avg(a) from ab where a in($1,$2,$3) and b < 4; select explain_parallel_append('execute ab_q5 (1, 1, 1)'); - explain_parallel_append ------------------------------------------------------------------------------------- - Finalize Aggregate (actual rows=N loops=N) - -> Gather (actual rows=N loops=N) - Workers Planned: 2 - Workers Launched: N - -> Partial Aggregate (actual rows=N loops=N) - -> Parallel Append (actual rows=N loops=N) - Subplans Removed: 6 - -> Parallel Seq Scan on ab_a1_b1 ab_1 (actual rows=N loops=N) - Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3]))) - -> Parallel Seq Scan on ab_a1_b2 ab_2 (actual rows=N loops=N) - Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3]))) - -> Parallel Seq Scan on ab_a1_b3 ab_3 (actual rows=N loops=N) - Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3]))) -(13 rows) + explain_parallel_append +------------------------------------------------------------------- + Aggregate (actual rows=N loops=N) + -> Append (actual rows=N loops=N) + Subplans Removed: 6 + -> Seq Scan on ab_a1_b1 ab_1 (actual rows=N loops=N) + Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3]))) + -> Seq Scan on ab_a1_b2 ab_2 (actual rows=N loops=N) + Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3]))) + -> Seq Scan on ab_a1_b3 ab_3 (actual rows=N loops=N) + Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3]))) +(9 rows) select explain_parallel_append('execute ab_q5 (2, 3, 3)'); - explain_parallel_append ------------------------------------------------------------------------------------- - Finalize Aggregate (actual rows=N loops=N) - -> Gather (actual rows=N loops=N) - Workers Planned: 2 - Workers Launched: N - -> Partial Aggregate (actual rows=N loops=N) - -> Parallel Append (actual rows=N loops=N) - Subplans Removed: 3 - -> Parallel Seq Scan on ab_a2_b1 ab_1 (actual rows=N loops=N) - Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3]))) - -> Parallel Seq Scan on ab_a2_b2 ab_2 (actual rows=N loops=N) - Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3]))) - -> Parallel Seq Scan on ab_a2_b3 ab_3 (actual rows=N loops=N) - Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3]))) - -> Parallel Seq Scan on ab_a3_b1 ab_4 (actual rows=N loops=N) - Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3]))) - -> Parallel Seq Scan on ab_a3_b2 ab_5 (actual rows=N loops=N) - Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3]))) - -> Parallel Seq Scan on ab_a3_b3 ab_6 (actual rows=N loops=N) - Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3]))) -(19 rows) + explain_parallel_append +------------------------------------------------------------------- + Aggregate (actual rows=N loops=N) + -> Append (actual rows=N loops=N) + Subplans Removed: 3 + -> Seq Scan on ab_a2_b1 ab_1 (actual rows=N loops=N) + Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3]))) + -> Seq Scan on ab_a2_b2 ab_2 (actual rows=N loops=N) + Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3]))) + -> Seq Scan on ab_a2_b3 ab_3 (actual rows=N loops=N) + Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3]))) + -> Seq Scan on ab_a3_b1 ab_4 (actual rows=N loops=N) + Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3]))) + -> Seq Scan on ab_a3_b2 ab_5 (actual rows=N loops=N) + Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3]))) + -> Seq Scan on ab_a3_b3 ab_6 (actual rows=N loops=N) + Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3]))) +(15 rows) -- Try some params whose values do not belong to any partition. select explain_parallel_append('execute ab_q5 (33, 44, 55)'); - explain_parallel_append ------------------------------------------------------------ - Finalize Aggregate (actual rows=N loops=N) - -> Gather (actual rows=N loops=N) - Workers Planned: 2 - Workers Launched: N - -> Partial Aggregate (actual rows=N loops=N) - -> Parallel Append (actual rows=N loops=N) - Subplans Removed: 9 -(7 rows) + explain_parallel_append +-------------------------------------- + Aggregate (actual rows=N loops=N) + -> Append (actual rows=N loops=N) + Subplans Removed: 9 +(3 rows) -- Test Parallel Append with PARAM_EXEC Params select explain_parallel_append('select count(*) from ab where (a = (select 1) or a = (select 3)) and b = 2'); - explain_parallel_append ------------------------------------------------------------------------------- + explain_parallel_append +--------------------------------------------------------------- Aggregate (actual rows=N loops=N) InitPlan 1 (returns $0) -> Result (actual rows=N loops=N) InitPlan 2 (returns $1) -> Result (actual rows=N loops=N) - -> Gather (actual rows=N loops=N) - Workers Planned: 2 - Params Evaluated: $0, $1 - Workers Launched: N - -> Parallel Append (actual rows=N loops=N) - -> Parallel Seq Scan on ab_a1_b2 ab_1 (actual rows=N loops=N) - Filter: ((b = 2) AND ((a = $0) OR (a = $1))) - -> Parallel Seq Scan on ab_a2_b2 ab_2 (never executed) - Filter: ((b = 2) AND ((a = $0) OR (a = $1))) - -> Parallel Seq Scan on ab_a3_b2 ab_3 (actual rows=N loops=N) - Filter: ((b = 2) AND ((a = $0) OR (a = $1))) -(16 rows) + -> Append (actual rows=N loops=N) + -> Seq Scan on ab_a1_b2 ab_1 (actual rows=N loops=N) + Filter: ((b = 2) AND ((a = $0) OR (a = $1))) + -> Seq Scan on ab_a2_b2 ab_2 (never executed) + Filter: ((b = 2) AND ((a = $0) OR (a = $1))) + -> Seq Scan on ab_a3_b2 ab_3 (actual rows=N loops=N) + Filter: ((b = 2) AND ((a = $0) OR (a = $1))) +(12 rows) -- Test pruning during parallel nested loop query create table lprt_a (a int not null); @@ -2463,74 +2447,72 @@ deallocate ab_q6; insert into ab values (1,2); explain (analyze, costs off, summary off, timing off) update ab_a1 set b = 3 from ab where ab.a = 1 and ab.a = ab_a1.a; - QUERY PLAN -------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------- Update on ab_a1 (actual rows=0 loops=1) Update on ab_a1_b1 ab_a1_1 Update on ab_a1_b2 ab_a1_2 Update on ab_a1_b3 ab_a1_3 -> Nested Loop (actual rows=0 loops=1) - -> Append (actual rows=1 loops=1) - -> Bitmap Heap Scan on ab_a1_b1 ab_1 (actual rows=0 loops=1) - Recheck Cond: (a = 1) - -> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1) - Index Cond: (a = 1) - -> Bitmap Heap Scan on ab_a1_b2 ab_2 (actual rows=1 loops=1) - Recheck Cond: (a = 1) - Heap Blocks: exact=1 - -> Bitmap Index Scan on ab_a1_b2_a_idx (actual rows=1 loops=1) - Index Cond: (a = 1) - -> Bitmap Heap Scan on ab_a1_b3 ab_3 (actual rows=0 loops=1) - Recheck Cond: (a = 1) - -> Bitmap Index Scan on ab_a1_b3_a_idx (actual rows=0 loops=1) - Index Cond: (a = 1) - -> Materialize (actual rows=0 loops=1) - -> Bitmap Heap Scan on ab_a1_b1 ab_a1_1 (actual rows=0 loops=1) - Recheck Cond: (a = 1) - -> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1) - Index Cond: (a = 1) + -> Bitmap Heap Scan on ab_a1_b1 ab_a1_1 (actual rows=0 loops=1) + Recheck Cond: (a = 1) + -> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1) + Index Cond: (a = 1) + -> Materialize (never executed) + -> Append (never executed) + -> Bitmap Heap Scan on ab_a1_b1 ab_1 (never executed) + Recheck Cond: (a = 1) + -> Bitmap Index Scan on ab_a1_b1_a_idx (never executed) + Index Cond: (a = 1) + -> Bitmap Heap Scan on ab_a1_b2 ab_2 (never executed) + Recheck Cond: (a = 1) + -> Bitmap Index Scan on ab_a1_b2_a_idx (never executed) + Index Cond: (a = 1) + -> Bitmap Heap Scan on ab_a1_b3 ab_3 (never executed) + Recheck Cond: (a = 1) + -> Bitmap Index Scan on ab_a1_b3_a_idx (never executed) + Index Cond: (a = 1) -> Nested Loop (actual rows=1 loops=1) - -> Append (actual rows=1 loops=1) - -> Bitmap Heap Scan on ab_a1_b1 ab_1 (actual rows=0 loops=1) - Recheck Cond: (a = 1) - -> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1) - Index Cond: (a = 1) - -> Bitmap Heap Scan on ab_a1_b2 ab_2 (actual rows=1 loops=1) - Recheck Cond: (a = 1) - Heap Blocks: exact=1 - -> Bitmap Index Scan on ab_a1_b2_a_idx (actual rows=1 loops=1) - Index Cond: (a = 1) - -> Bitmap Heap Scan on ab_a1_b3 ab_3 (actual rows=0 loops=1) - Recheck Cond: (a = 1) - -> Bitmap Index Scan on ab_a1_b3_a_idx (actual rows=1 loops=1) - Index Cond: (a = 1) + -> Bitmap Heap Scan on ab_a1_b2 ab_a1_2 (actual rows=1 loops=1) + Recheck Cond: (a = 1) + Heap Blocks: exact=1 + -> Bitmap Index Scan on ab_a1_b2_a_idx (actual rows=1 loops=1) + Index Cond: (a = 1) -> Materialize (actual rows=1 loops=1) - -> Bitmap Heap Scan on ab_a1_b2 ab_a1_2 (actual rows=1 loops=1) - Recheck Cond: (a = 1) - Heap Blocks: exact=1 - -> Bitmap Index Scan on ab_a1_b2_a_idx (actual rows=1 loops=1) - Index Cond: (a = 1) + -> Append (actual rows=1 loops=1) + -> Bitmap Heap Scan on ab_a1_b1 ab_1 (actual rows=0 loops=1) + Recheck Cond: (a = 1) + -> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1) + Index Cond: (a = 1) + -> Bitmap Heap Scan on ab_a1_b2 ab_2 (actual rows=1 loops=1) + Recheck Cond: (a = 1) + Heap Blocks: exact=1 + -> Bitmap Index Scan on ab_a1_b2_a_idx (actual rows=1 loops=1) + Index Cond: (a = 1) + -> Bitmap Heap Scan on ab_a1_b3 ab_3 (actual rows=0 loops=1) + Recheck Cond: (a = 1) + -> Bitmap Index Scan on ab_a1_b3_a_idx (actual rows=1 loops=1) + Index Cond: (a = 1) -> Nested Loop (actual rows=0 loops=1) - -> Append (actual rows=1 loops=1) - -> Bitmap Heap Scan on ab_a1_b1 ab_1 (actual rows=0 loops=1) - Recheck Cond: (a = 1) - -> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1) - Index Cond: (a = 1) - -> Bitmap Heap Scan on ab_a1_b2 ab_2 (actual rows=1 loops=1) - Recheck Cond: (a = 1) - Heap Blocks: exact=1 - -> Bitmap Index Scan on ab_a1_b2_a_idx (actual rows=1 loops=1) - Index Cond: (a = 1) - -> Bitmap Heap Scan on ab_a1_b3 ab_3 (actual rows=0 loops=1) - Recheck Cond: (a = 1) - -> Bitmap Index Scan on ab_a1_b3_a_idx (actual rows=1 loops=1) - Index Cond: (a = 1) - -> Materialize (actual rows=0 loops=1) - -> Bitmap Heap Scan on ab_a1_b3 ab_a1_3 (actual rows=0 loops=1) - Recheck Cond: (a = 1) - -> Bitmap Index Scan on ab_a1_b3_a_idx (actual rows=1 loops=1) - Index Cond: (a = 1) -(65 rows) + -> Bitmap Heap Scan on ab_a1_b3 ab_a1_3 (actual rows=0 loops=1) + Recheck Cond: (a = 1) + -> Bitmap Index Scan on ab_a1_b3_a_idx (actual rows=1 loops=1) + Index Cond: (a = 1) + -> Materialize (never executed) + -> Append (never executed) + -> Bitmap Heap Scan on ab_a1_b1 ab_1 (never executed) + Recheck Cond: (a = 1) + -> Bitmap Index Scan on ab_a1_b1_a_idx (never executed) + Index Cond: (a = 1) + -> Bitmap Heap Scan on ab_a1_b2 ab_2 (never executed) + Recheck Cond: (a = 1) + -> Bitmap Index Scan on ab_a1_b2_a_idx (never executed) + Index Cond: (a = 1) + -> Bitmap Heap Scan on ab_a1_b3 ab_3 (never executed) + Recheck Cond: (a = 1) + -> Bitmap Index Scan on ab_a1_b3_a_idx (never executed) + Index Cond: (a = 1) +(63 rows) table ab; a | b @@ -3713,20 +3695,16 @@ alter table listp_12_1 set (parallel_workers = 0); -- Ensure that listp_12_2 is not scanned. (The nested Append is not seen in -- the plan as it's pulled in setref.c due to having just a single subnode). select explain_parallel_append('select * from listp where a = (select 1);'); - explain_parallel_append ----------------------------------------------------------------------- - Gather (actual rows=N loops=N) - Workers Planned: 2 - Params Evaluated: $0 - Workers Launched: N + explain_parallel_append +-------------------------------------------------------------- + Append (actual rows=N loops=N) InitPlan 1 (returns $0) -> Result (actual rows=N loops=N) - -> Parallel Append (actual rows=N loops=N) - -> Seq Scan on listp_12_1 listp_1 (actual rows=N loops=N) - Filter: (a = $0) - -> Parallel Seq Scan on listp_12_2 listp_2 (never executed) - Filter: (a = $0) -(11 rows) + -> Seq Scan on listp_12_1 listp_1 (actual rows=N loops=N) + Filter: (a = $0) + -> Seq Scan on listp_12_2 listp_2 (never executed) + Filter: (a = $0) +(7 rows) -- Like the above but throw some more complexity at the planner by adding -- a UNION ALL. We expect both sides of the union not to scan the @@ -3735,32 +3713,24 @@ select explain_parallel_append( 'select * from listp where a = (select 1) union all select * from listp where a = (select 2);'); - explain_parallel_append ------------------------------------------------------------------------------------ + explain_parallel_append +-------------------------------------------------------------------- Append (actual rows=N loops=N) - -> Gather (actual rows=N loops=N) - Workers Planned: 2 - Params Evaluated: $0 - Workers Launched: N + -> Append (actual rows=N loops=N) InitPlan 1 (returns $0) -> Result (actual rows=N loops=N) - -> Parallel Append (actual rows=N loops=N) - -> Seq Scan on listp_12_1 listp_1 (actual rows=N loops=N) - Filter: (a = $0) - -> Parallel Seq Scan on listp_12_2 listp_2 (never executed) - Filter: (a = $0) - -> Gather (actual rows=N loops=N) - Workers Planned: 2 - Params Evaluated: $1 - Workers Launched: N + -> Seq Scan on listp_12_1 listp_1 (actual rows=N loops=N) + Filter: (a = $0) + -> Seq Scan on listp_12_2 listp_2 (never executed) + Filter: (a = $0) + -> Append (actual rows=N loops=N) InitPlan 2 (returns $1) -> Result (actual rows=N loops=N) - -> Parallel Append (actual rows=N loops=N) - -> Seq Scan on listp_12_1 listp_4 (never executed) - Filter: (a = $1) - -> Parallel Seq Scan on listp_12_2 listp_5 (actual rows=N loops=N) - Filter: (a = $1) -(23 rows) + -> Seq Scan on listp_12_1 listp_4 (never executed) + Filter: (a = $1) + -> Seq Scan on listp_12_2 listp_5 (actual rows=N loops=N) + Filter: (a = $1) +(15 rows) drop table listp; reset parallel_tuple_cost; -- 2.21.0