postgrespro.git / summary / log / commit / refs
commit 4e85642d935eb13341584df7776eb76585d45819
Author: Alvaro Herrera <alvherre@alvh.no-ip.org>
Date: Wed Aug 07 16:21:22 2019 +0000
Apply constraint exclusion more generally in partitioning
We were applying constraint exclusion on the partition constraint when
generating pruning steps for a clause, but only for the rather
restricted situation of them being boolean OR operators; however it is
possible to have differently shaped clauses that also benefit from
constraint exclusion. This applies particularly to the default
partition since their constraints are in essence a long list of OR'ed
subclauses ... but it applies to other cases too. So in certain cases
we're scanning partitions that we don't need to.
Remove the specialized code in OR clauses, and add a generally
applicable test of the clause refuting the partition constraint; mark
the whole pruning operation as contradictory if it hits.
This has the unwanted side-effect of testing some (most? all?)
constraints more than once if constraint_exclusion=on. That seems
unavoidable as far as I can tell without some additional work, but
that's not the recommended setting for that parameter anyway.
However, because this imposes additional processing cost for all
queries using partitioned tables, I decided not to backpatch this
change.
Author: Amit Langote, Yuzuko Hosoya, Álvaro Herrera
Reviewers: Shawn Wang, Thibaut Madeleine, Yoshikazu Imai, Kyotaro
Horiguchi; they were also uncredited reviewers for commit 489247b0e615.
Discussion: https://postgr.es/m/9bb31dfe-b0d0-53f3-3ea6-e64b811424cf@lab.ntt.co.jp
src/backend/partitioning/partprune.c | 56 ++++++++++++++-------------
src/test/regress/expected/partition_prune.out | 18 +++++++++
src/test/regress/sql/partition_prune.sql | 4 ++
3 files changed, 52 insertions(+), 26 deletions(-)
diff --git a/src/backend/partitioning/partprune.c b/src/backend/partitioning/partprune.c
index 09c4ed83b6..2ed1e44c18 100644
--- a/src/backend/partitioning/partprune.c
+++ b/src/backend/partitioning/partprune.c
@@ -849,10 +849,11 @@ get_matching_partitions(PartitionPruneContext *context, List *pruning_steps)
* the context's steps list. Each step is assigned a step identifier, unique
* even across recursive calls.
*
- * If we find clauses that are mutually contradictory, or a pseudoconstant
- * clause that contains false, we set context->contradictory to true and
- * return NIL (that is, no pruning steps). Caller should consider all
- * partitions as pruned in that case.
+ * If we find clauses that are mutually contradictory, or contradictory with
+ * the partitioning constraint, or a pseudoconstant clause that contains
+ * false, we set context->contradictory to true and return NIL (that is, no
+ * pruning steps). Caller should consider all partitions as pruned in that
+ * case.
*/
static List *
gen_partprune_steps_internal(GeneratePruningStepsContext *context,
@@ -942,35 +943,15 @@ gen_partprune_steps_internal(GeneratePruningStepsContext *context,
}
else
{
+ PartitionPruneStep *orstep;
+
/*
* The arg didn't contain a clause matching this
* partition key. We cannot prune using such an arg.
* To indicate that to the pruning code, we must
* construct a dummy PartitionPruneStepCombine whose
* source_stepids is set to an empty List.
- *
- * However, if we can prove using constraint exclusion
- * that the clause refutes the table's partition
- * constraint (if it's sub-partitioned), we need not
- * bother with that. That is, we effectively ignore
- * this OR arm.
*/
- List *partconstr = context->rel->partition_qual;
- PartitionPruneStep *orstep;
-
- if (partconstr)
- {
- partconstr = (List *)
- expression_planner((Expr *) partconstr);
- if (context->rel->relid != 1)
- ChangeVarNodes((Node *) partconstr, 1,
- context->rel->relid, 0);
- if (predicate_refuted_by(partconstr,
- list_make1(arg),
- false))
- continue;
- }
-
orstep = gen_prune_step_combine(context, NIL,
PARTPRUNE_COMBINE_UNION);
arg_stepids = lappend_int(arg_stepids, orstep->step_id);
@@ -1038,6 +1019,29 @@ gen_partprune_steps_internal(GeneratePruningStepsContext *context,
*/
}
+ /*
+ * If the clause contradicts the partition constraint, mark the clause
+ * as contradictory and we're done. This is particularly helpful to
+ * prune the default partition.
+ */
+ if (context->rel->partition_qual)
+ {
+ List *partconstr;
+
+ partconstr = (List *)
+ expression_planner((Expr *) context->rel->partition_qual);
+ if (context->rel->relid != 1)
+ ChangeVarNodes((Node *) partconstr, 1,
+ context->rel->relid, 0);
+ if (predicate_refuted_by(partconstr,
+ list_make1(clause),
+ false))
+ {
+ context->contradictory = true;
+ return NIL;
+ }
+ }
+
/*
* See if we can match this clause to any of the partition keys.
*/
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index 2d3229fd73..6ccc91d390 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -592,6 +592,24 @@ explain (costs off) select * from rlp where a < 1 or (a > 20 and a < 25);
Filter: ((a < 1) OR ((a > 20) AND (a < 25)))
(5 rows)
+-- where clause contradicts sub-partition's constraint
+explain (costs off) select * from rlp where a = 20 or a = 40;
+ QUERY PLAN
+----------------------------------------
+ Append
+ -> Seq Scan on rlp4_1
+ Filter: ((a = 20) OR (a = 40))
+ -> Seq Scan on rlp5_default
+ Filter: ((a = 20) OR (a = 40))
+(5 rows)
+
+explain (costs off) select * from rlp3 where a = 20; /* empty */
+ QUERY PLAN
+--------------------------
+ Result
+ One-Time Filter: false
+(2 rows)
+
-- redundant clauses are eliminated
explain (costs off) select * from rlp where a > 1 and a = 10; /* only default */
QUERY PLAN
diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sql
index efdedaaeb8..a26c2f0e72 100644
--- a/src/test/regress/sql/partition_prune.sql
+++ b/src/test/regress/sql/partition_prune.sql
@@ -85,6 +85,10 @@ explain (costs off) select * from rlp where a = 29;
explain (costs off) select * from rlp where a >= 29;
explain (costs off) select * from rlp where a < 1 or (a > 20 and a < 25);
+-- where clause contradicts sub-partition's constraint
+explain (costs off) select * from rlp where a = 20 or a = 40;
+explain (costs off) select * from rlp3 where a = 20; /* empty */
+
-- redundant clauses are eliminated
explain (costs off) select * from rlp where a > 1 and a = 10; /* only default */
explain (costs off) select * from rlp where a > 1 and a >=15; /* rlp3 onwards, including default */
[parent: 1f33f211bc53]