diff --git a/src/backend/partitioning/partprune.c b/src/backend/partitioning/partprune.c index badd31a44c..8bac2a4abb 100644 --- a/src/backend/partitioning/partprune.c +++ b/src/backend/partitioning/partprune.c @@ -1308,34 +1308,18 @@ gen_prune_steps_from_opexps(GeneratePruningStepsContext *context, { case PARTITION_STRATEGY_LIST: case PARTITION_STRATEGY_RANGE: - { - PartClauseInfo *last = NULL; - - /* - * Add this clause to the list of clauses to be used - * for pruning if this is the first such key for this - * operator strategy or if it is consecutively next to - * the last column for which a clause with this - * operator strategy was matched. - */ - if (btree_clauses[pc->op_strategy] != NIL) - last = llast(btree_clauses[pc->op_strategy]); - - if (last == NULL || - i == last->keyno || i == last->keyno + 1) - btree_clauses[pc->op_strategy] = - lappend(btree_clauses[pc->op_strategy], pc); + btree_clauses[pc->op_strategy] = + lappend(btree_clauses[pc->op_strategy], pc); - /* - * We can't consider subsequent partition keys if the - * clause for the current key contains a non-inclusive - * operator. - */ - if (pc->op_strategy == BTLessStrategyNumber || - pc->op_strategy == BTGreaterStrategyNumber) - consider_next_key = false; - break; - } + /* + * We can't consider subsequent partition keys if the + * clause for the current key contains a non-inclusive + * operator. + */ + if (pc->op_strategy == BTLessStrategyNumber || + pc->op_strategy == BTGreaterStrategyNumber) + consider_next_key = false; + break; case PARTITION_STRATEGY_HASH: if (pc->op_strategy != HTEqualStrategyNumber) @@ -1374,6 +1358,7 @@ gen_prune_steps_from_opexps(GeneratePruningStepsContext *context, List *eq_clauses = btree_clauses[BTEqualStrategyNumber]; List *le_clauses = btree_clauses[BTLessEqualStrategyNumber]; List *ge_clauses = btree_clauses[BTGreaterEqualStrategyNumber]; + bool pk_has_clauses[PARTITION_MAX_KEYS]; int strat; /* @@ -1396,6 +1381,35 @@ gen_prune_steps_from_opexps(GeneratePruningStepsContext *context, ListCell *lc1; List *prefix = NIL; List *pc_steps; + bool prefix_valid = true; + + /* + * If this is a clause for the first partition key, + * there are no preceding expressions; generate a + * pruning step without a prefix. + * + * Note that we pass NULL for step_nullkeys, because + * we don't search list/range partition bounds where + * some keys are NULL. + */ + if (pc->keyno == 0) + { + Assert(pc->op_strategy == strat); + pc_steps = get_steps_using_prefix(context, strat, + pc->op_is_ne, + pc->expr, + pc->cmpfn, + 0, + NULL, + NIL); + opsteps = list_concat(opsteps, pc_steps); + continue; + } + + /* (Re-)initialize the pk_has_clauses array */ + Assert(pc->keyno > 0); + for (i = 0; i < pc->keyno; i++) + pk_has_clauses[i] = false; /* * Expressions from = clauses can always be in the @@ -1408,7 +1422,10 @@ gen_prune_steps_from_opexps(GeneratePruningStepsContext *context, if (eqpc->keyno == pc->keyno) break; if (eqpc->keyno < pc->keyno) + { prefix = lappend(prefix, eqpc); + pk_has_clauses[eqpc->keyno] = true; + } } /* @@ -1426,7 +1443,10 @@ gen_prune_steps_from_opexps(GeneratePruningStepsContext *context, if (lepc->keyno == pc->keyno) break; if (lepc->keyno < pc->keyno) + { prefix = lappend(prefix, lepc); + pk_has_clauses[lepc->keyno] = true; + } } } @@ -1445,11 +1465,33 @@ gen_prune_steps_from_opexps(GeneratePruningStepsContext *context, if (gepc->keyno == pc->keyno) break; if (gepc->keyno < pc->keyno) + { prefix = lappend(prefix, gepc); + pk_has_clauses[gepc->keyno] = true; + } } } /* + * Check whether every earlier partition key has at + * least one clause. + */ + for (i = 0; i < pc->keyno; i++) + { + if (!pk_has_clauses[i]) + { + prefix_valid = false; + break; + } + } + + /* + * If prefix_valid, generate PartitionPruneStepOps. + * Otherwise, we would not find clauses for a valid + * subset of the partition keys anymore for the + * strategy; give up on generating partition pruning + * steps further for the strategy. + * * As mentioned above, if 'prefix' contains multiple * expressions for the same key, the following will * generate multiple steps, one for each combination @@ -1459,15 +1501,20 @@ gen_prune_steps_from_opexps(GeneratePruningStepsContext *context, * we don't search list/range partition bounds where * some keys are NULL. */ - Assert(pc->op_strategy == strat); - pc_steps = get_steps_using_prefix(context, strat, - pc->op_is_ne, - pc->expr, - pc->cmpfn, - pc->keyno, - NULL, - prefix); - opsteps = list_concat(opsteps, pc_steps); + if (prefix_valid) + { + Assert(pc->op_strategy == strat); + pc_steps = get_steps_using_prefix(context, strat, + pc->op_is_ne, + pc->expr, + pc->cmpfn, + pc->keyno, + NULL, + prefix); + opsteps = list_concat(opsteps, pc_steps); + } + else + break; } } break; @@ -2278,23 +2325,19 @@ get_steps_using_prefix_recurse(GeneratePruningStepsContext *context, for_each_cell(lc, prefix, start) { List *moresteps; + List *step_exprs1, + *step_cmpfns1; pc = lfirst(lc); if (pc->keyno == cur_keyno) { - /* clean up before starting a new recursion cycle. */ - if (cur_keyno == 0) - { - list_free(step_exprs); - list_free(step_cmpfns); - step_exprs = list_make1(pc->expr); - step_cmpfns = list_make1_oid(pc->cmpfn); - } - else - { - step_exprs = lappend(step_exprs, pc->expr); - step_cmpfns = lappend_oid(step_cmpfns, pc->cmpfn); - } + /* Leave the original step_exprs unmodified. */ + step_exprs1 = list_copy(step_exprs); + step_exprs1 = lappend(step_exprs1, pc->expr); + + /* Leave the original step_cmpfns unmodified. */ + step_cmpfns1 = list_copy(step_cmpfns); + step_cmpfns1 = lappend_oid(step_cmpfns1, pc->cmpfn); } else { @@ -2311,8 +2354,8 @@ get_steps_using_prefix_recurse(GeneratePruningStepsContext *context, step_nullkeys, prefix, next_start, - step_exprs, - step_cmpfns); + step_exprs1, + step_cmpfns1); result = list_concat(result, moresteps); } } @@ -2323,7 +2366,13 @@ get_steps_using_prefix_recurse(GeneratePruningStepsContext *context, * each clause with cur_keyno, which is all clauses from here onward * till the end of the list. */ - Assert(list_length(step_exprs) == cur_keyno); + Assert(list_length(step_exprs) == cur_keyno || + (context->rel->part_scheme->strategy == + PARTITION_STRATEGY_HASH && + step_opstrategy == HTEqualStrategyNumber && + !bms_is_empty(step_nullkeys) && + bms_num_members(step_nullkeys) + list_length(step_exprs) + 2 == + context->rel->part_scheme->partnatts)); for_each_cell(lc, prefix, start) { PartClauseInfo *pc = lfirst(lc); diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out index 4315e8e0a3..10ef907c96 100644 --- a/src/test/regress/expected/partition_prune.out +++ b/src/test/regress/expected/partition_prune.out @@ -3671,3 +3671,63 @@ explain (costs off) update listp1 set a = 1 where a = 2; reset constraint_exclusion; reset enable_partition_pruning; drop table listp; +create table rp_prefix_test1 (a int, b varchar) partition by range(a, b); +create table rp_prefix_test1_p1 partition of rp_prefix_test1 for values from (1, 'a') to (1, 'b'); +create table rp_prefix_test1_p2 partition of rp_prefix_test1 for values from (2, 'a') to (2, 'b'); +explain (costs off) select * from rp_prefix_test1 where a <= 1 and b = 'a'; + QUERY PLAN +-------------------------------------------------- + Seq Scan on rp_prefix_test1_p1 rp_prefix_test1 + Filter: ((a <= 1) AND ((b)::text = 'a'::text)) +(2 rows) + +explain (costs off) select * from rp_prefix_test1 where a <= 2 and b = 'a'; + QUERY PLAN +-------------------------------------------------------- + Append + -> Seq Scan on rp_prefix_test1_p1 rp_prefix_test1_1 + Filter: ((a <= 2) AND ((b)::text = 'a'::text)) + -> Seq Scan on rp_prefix_test1_p2 rp_prefix_test1_2 + Filter: ((a <= 2) AND ((b)::text = 'a'::text)) +(5 rows) + +create table rp_prefix_test2 (a int, b int, c int) partition by range(a, b, c); +create table rp_prefix_test2_p1 partition of rp_prefix_test2 for values from (1, 1, 0) to (1, 1, 10); +create table rp_prefix_test2_p2 partition of rp_prefix_test2 for values from (2, 2, 0) to (2, 2, 10); +explain (costs off) select * from rp_prefix_test2 where a <= 1 and b = 1 and c >= 0; + QUERY PLAN +------------------------------------------------ + Seq Scan on rp_prefix_test2_p1 rp_prefix_test2 + Filter: ((a <= 1) AND (c >= 0) AND (b = 1)) +(2 rows) + +explain (costs off) select * from rp_prefix_test2 where a <= 2 and b = 2 and c >= 0; + QUERY PLAN +-------------------------------------------------------- + Append + -> Seq Scan on rp_prefix_test2_p1 rp_prefix_test2_1 + Filter: ((a <= 2) AND (c >= 0) AND (b = 2)) + -> Seq Scan on rp_prefix_test2_p2 rp_prefix_test2_2 + Filter: ((a <= 2) AND (c >= 0) AND (b = 2)) +(5 rows) + +create table rp_prefix_test3 (a int, b int, c int, d int) partition by range(a, b, c, d); +create table rp_prefix_test3_p1 partition of rp_prefix_test3 for values from (1, 1, 1, 0) to (1, 1, 1, 10); +create table rp_prefix_test3_p2 partition of rp_prefix_test3 for values from (2, 2, 2, 0) to (2, 2, 2, 10); +explain (costs off) select * from rp_prefix_test3 where a >= 1 and b >= 1 and b >= 2 and c >= 2 and d >= 0; + QUERY PLAN +-------------------------------------------------------------------------- + Seq Scan on rp_prefix_test3_p2 rp_prefix_test3 + Filter: ((a >= 1) AND (b >= 1) AND (b >= 2) AND (c >= 2) AND (d >= 0)) +(2 rows) + +create table hp_prefix_test (a int, b int, c int, d int) partition by hash (a part_test_int4_ops, b part_test_int4_ops, c part_test_int4_ops, d part_test_int4_ops); +create table hp_prefix_test_p1 partition of hp_prefix_test for values with (modulus 2, remainder 0); +create table hp_prefix_test_p2 partition of hp_prefix_test for values with (modulus 2, remainder 1); +explain (costs off) select * from hp_prefix_test where a = 1 and b is null and c = 1 and d = 1; + QUERY PLAN +------------------------------------------------------------- + Seq Scan on hp_prefix_test_p1 hp_prefix_test + Filter: ((b IS NULL) AND (a = 1) AND (c = 1) AND (d = 1)) +(2 rows) + diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sql index 6658455a74..628be0901e 100644 --- a/src/test/regress/sql/partition_prune.sql +++ b/src/test/regress/sql/partition_prune.sql @@ -1050,3 +1050,29 @@ reset constraint_exclusion; reset enable_partition_pruning; drop table listp; + +create table rp_prefix_test1 (a int, b varchar) partition by range(a, b); +create table rp_prefix_test1_p1 partition of rp_prefix_test1 for values from (1, 'a') to (1, 'b'); +create table rp_prefix_test1_p2 partition of rp_prefix_test1 for values from (2, 'a') to (2, 'b'); + +explain (costs off) select * from rp_prefix_test1 where a <= 1 and b = 'a'; +explain (costs off) select * from rp_prefix_test1 where a <= 2 and b = 'a'; + +create table rp_prefix_test2 (a int, b int, c int) partition by range(a, b, c); +create table rp_prefix_test2_p1 partition of rp_prefix_test2 for values from (1, 1, 0) to (1, 1, 10); +create table rp_prefix_test2_p2 partition of rp_prefix_test2 for values from (2, 2, 0) to (2, 2, 10); + +explain (costs off) select * from rp_prefix_test2 where a <= 1 and b = 1 and c >= 0; +explain (costs off) select * from rp_prefix_test2 where a <= 2 and b = 2 and c >= 0; + +create table rp_prefix_test3 (a int, b int, c int, d int) partition by range(a, b, c, d); +create table rp_prefix_test3_p1 partition of rp_prefix_test3 for values from (1, 1, 1, 0) to (1, 1, 1, 10); +create table rp_prefix_test3_p2 partition of rp_prefix_test3 for values from (2, 2, 2, 0) to (2, 2, 2, 10); + +explain (costs off) select * from rp_prefix_test3 where a >= 1 and b >= 1 and b >= 2 and c >= 2 and d >= 0; + +create table hp_prefix_test (a int, b int, c int, d int) partition by hash (a part_test_int4_ops, b part_test_int4_ops, c part_test_int4_ops, d part_test_int4_ops); +create table hp_prefix_test_p1 partition of hp_prefix_test for values with (modulus 2, remainder 0); +create table hp_prefix_test_p2 partition of hp_prefix_test for values with (modulus 2, remainder 1); + +explain (costs off) select * from hp_prefix_test where a = 1 and b is null and c = 1 and d = 1;