Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit4e85642

Browse files
committed
Apply constraint exclusion more generally in partitioning
We were applying constraint exclusion on the partition constraint whengenerating pruning steps for a clause, but only for the ratherrestricted situation of them being boolean OR operators; however it ispossible to have differently shaped clauses that also benefit fromconstraint exclusion. This applies particularly to the defaultpartition since their constraints are in essence a long list of OR'edsubclauses ... but it applies to other cases too. So in certain caseswe're scanning partitions that we don't need to.Remove the specialized code in OR clauses, and add a generallyapplicable test of the clause refuting the partition constraint; markthe 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 seemsunavoidable as far as I can tell without some additional work, butthat's not the recommended setting for that parameter anyway.However, because this imposes additional processing cost for allqueries using partitioned tables, I decided not to backpatch thischange.Author: Amit Langote, Yuzuko Hosoya, Álvaro HerreraReviewers: Shawn Wang, Thibaut Madeleine, Yoshikazu Imai, KyotaroHoriguchi; they were also uncredited reviewers for commit489247b.Discussion:https://postgr.es/m/9bb31dfe-b0d0-53f3-3ea6-e64b811424cf@lab.ntt.co.jp
1 parent1f33f21 commit4e85642

File tree

3 files changed

+52
-26
lines changed

3 files changed

+52
-26
lines changed

‎src/backend/partitioning/partprune.c

Lines changed: 30 additions & 26 deletions
Original file line numberDiff line numberDiff line change
@@ -849,10 +849,11 @@ get_matching_partitions(PartitionPruneContext *context, List *pruning_steps)
849849
* the context's steps list. Each step is assigned a step identifier, unique
850850
* even across recursive calls.
851851
*
852-
* If we find clauses that are mutually contradictory, or a pseudoconstant
853-
* clause that contains false, we set context->contradictory to true and
854-
* return NIL (that is, no pruning steps). Caller should consider all
855-
* partitions as pruned in that case.
852+
* If we find clauses that are mutually contradictory, or contradictory with
853+
* the partitioning constraint, or a pseudoconstant clause that contains
854+
* false, we set context->contradictory to true and return NIL (that is, no
855+
* pruning steps). Caller should consider all partitions as pruned in that
856+
* case.
856857
*/
857858
staticList*
858859
gen_partprune_steps_internal(GeneratePruningStepsContext*context,
@@ -942,35 +943,15 @@ gen_partprune_steps_internal(GeneratePruningStepsContext *context,
942943
}
943944
else
944945
{
946+
PartitionPruneStep*orstep;
947+
945948
/*
946949
* The arg didn't contain a clause matching this
947950
* partition key. We cannot prune using such an arg.
948951
* To indicate that to the pruning code, we must
949952
* construct a dummy PartitionPruneStepCombine whose
950953
* source_stepids is set to an empty List.
951-
*
952-
* However, if we can prove using constraint exclusion
953-
* that the clause refutes the table's partition
954-
* constraint (if it's sub-partitioned), we need not
955-
* bother with that. That is, we effectively ignore
956-
* this OR arm.
957954
*/
958-
List*partconstr=context->rel->partition_qual;
959-
PartitionPruneStep*orstep;
960-
961-
if (partconstr)
962-
{
963-
partconstr= (List*)
964-
expression_planner((Expr*)partconstr);
965-
if (context->rel->relid!=1)
966-
ChangeVarNodes((Node*)partconstr,1,
967-
context->rel->relid,0);
968-
if (predicate_refuted_by(partconstr,
969-
list_make1(arg),
970-
false))
971-
continue;
972-
}
973-
974955
orstep=gen_prune_step_combine(context,NIL,
975956
PARTPRUNE_COMBINE_UNION);
976957
arg_stepids=lappend_int(arg_stepids,orstep->step_id);
@@ -1038,6 +1019,29 @@ gen_partprune_steps_internal(GeneratePruningStepsContext *context,
10381019
*/
10391020
}
10401021

1022+
/*
1023+
* If the clause contradicts the partition constraint, mark the clause
1024+
* as contradictory and we're done. This is particularly helpful to
1025+
* prune the default partition.
1026+
*/
1027+
if (context->rel->partition_qual)
1028+
{
1029+
List*partconstr;
1030+
1031+
partconstr= (List*)
1032+
expression_planner((Expr*)context->rel->partition_qual);
1033+
if (context->rel->relid!=1)
1034+
ChangeVarNodes((Node*)partconstr,1,
1035+
context->rel->relid,0);
1036+
if (predicate_refuted_by(partconstr,
1037+
list_make1(clause),
1038+
false))
1039+
{
1040+
context->contradictory= true;
1041+
returnNIL;
1042+
}
1043+
}
1044+
10411045
/*
10421046
* See if we can match this clause to any of the partition keys.
10431047
*/

‎src/test/regress/expected/partition_prune.out

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -592,6 +592,24 @@ explain (costs off) select * from rlp where a < 1 or (a > 20 and a < 25);
592592
Filter: ((a < 1) OR ((a > 20) AND (a < 25)))
593593
(5 rows)
594594

595+
-- where clause contradicts sub-partition's constraint
596+
explain (costs off) select * from rlp where a = 20 or a = 40;
597+
QUERY PLAN
598+
----------------------------------------
599+
Append
600+
-> Seq Scan on rlp4_1
601+
Filter: ((a = 20) OR (a = 40))
602+
-> Seq Scan on rlp5_default
603+
Filter: ((a = 20) OR (a = 40))
604+
(5 rows)
605+
606+
explain (costs off) select * from rlp3 where a = 20; /* empty */
607+
QUERY PLAN
608+
--------------------------
609+
Result
610+
One-Time Filter: false
611+
(2 rows)
612+
595613
-- redundant clauses are eliminated
596614
explain (costs off) select * from rlp where a > 1 and a = 10;/* only default */
597615
QUERY PLAN

‎src/test/regress/sql/partition_prune.sql

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -85,6 +85,10 @@ explain (costs off) select * from rlp where a = 29;
8585
explain (costs off)select*from rlpwhere a>=29;
8686
explain (costs off)select*from rlpwhere a<1or (a>20and a<25);
8787

88+
-- where clause contradicts sub-partition's constraint
89+
explain (costs off)select*from rlpwhere a=20or a=40;
90+
explain (costs off)select*from rlp3where a=20;/* empty*/
91+
8892
-- redundant clauses are eliminated
8993
explain (costs off)select*from rlpwhere a>1and a=10;/* only default*/
9094
explain (costs off)select*from rlpwhere a>1and a>=15;/* rlp3 onwards, including default*/

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp