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

Commitfb2b61a

Browse files
alvherredilipbalaut11Amit Langote
committed
Fix partition pruning with IS [NOT] NULL clauses
The original code was unable to prune partitions that could not possiblycontain NULL values, when the query specified less than all columns in amulticolumn partition key. Reorder the if-tests so that it is, and addmore commentary and regression tests.Reported-by: Ashutosh Bapat <ashutosh.bapat@enterprisedb.com>Co-authored-by: Dilip Kumar <dilipbalaut@gmail.com>Co-authored-by: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>Co-authored-by: Álvaro Herrera <alvherre@alvh.no-ip.org>Reviewed-by: Ashutosh Bapat <ashutosh.bapat@enterprisedb.com>Reviewed-by: amul sul <sulamul@gmail.com>Discussion:https://postgr.es/m/CAFjFpRc7qjLUfXLVBBC_HAnx644sjTYM=qVoT3TJ840HPbsTXw@mail.gmail.com
1 parent9ec9f8f commitfb2b61a

File tree

3 files changed

+90
-36
lines changed

3 files changed

+90
-36
lines changed

‎src/backend/partitioning/partprune.c‎

Lines changed: 42 additions & 36 deletions
Original file line numberDiff line numberDiff line change
@@ -853,54 +853,60 @@ gen_partprune_steps_internal(GeneratePruningStepsContext *context,
853853
}
854854
}
855855

856-
/*
857-
* If generate_opsteps is set to false it means no OpExprs were directly
858-
* present in the input list.
856+
/*-----------
857+
* Now generate some (more) pruning steps. We have three strategies:
858+
*
859+
* 1) Generate pruning steps based on IS NULL clauses:
860+
* a) For list partitioning, null partition keys can only be found in
861+
* the designated null-accepting partition, so if there are IS NULL
862+
* clauses containing partition keys we should generate a pruning
863+
* step that gets rid of all partitions but that one. We can
864+
* disregard any OpExpr we may have found.
865+
* b) For range partitioning, only the default partition can contain
866+
* NULL values, so the same rationale applies.
867+
* c) For hash partitioning, we only apply this strategy if we have
868+
* IS NULL clauses for all the keys. Strategy 2 below will take
869+
* care of the case where some keys have OpExprs and others have
870+
* IS NULL clauses.
871+
*
872+
* 2) If not, generate steps based on OpExprs we have (if any).
873+
*
874+
* 3) If this doesn't work either, we may be able to generate steps to
875+
* prune just the null-accepting partition (if one exists), if we have
876+
* IS NOT NULL clauses for all partition keys.
859877
*/
860-
if (!generate_opsteps)
878+
if (!bms_is_empty(nullkeys)&&
879+
(part_scheme->strategy==PARTITION_STRATEGY_LIST||
880+
part_scheme->strategy==PARTITION_STRATEGY_RANGE||
881+
(part_scheme->strategy==PARTITION_STRATEGY_HASH&&
882+
bms_num_members(nullkeys)==part_scheme->partnatts)))
861883
{
862-
/*
863-
* Generate one prune step for the information derived from IS NULL,
864-
* if any. To prune hash partitions, we must have found IS NULL
865-
* clauses for all partition keys.
866-
*/
867-
if (!bms_is_empty(nullkeys)&&
868-
(part_scheme->strategy!=PARTITION_STRATEGY_HASH||
869-
bms_num_members(nullkeys)==part_scheme->partnatts))
870-
{
871-
PartitionPruneStep*step;
872-
873-
step=gen_prune_step_op(context,InvalidStrategy,
874-
false,NIL,NIL,nullkeys);
875-
result=lappend(result,step);
876-
}
877-
878-
/*
879-
* Note that for IS NOT NULL clauses, simply having step suffices;
880-
* there is no need to propagate the exact details of which keys are
881-
* required to be NOT NULL. Hash partitioning expects to see actual
882-
* values to perform any pruning.
883-
*/
884-
if (!bms_is_empty(notnullkeys)&&
885-
part_scheme->strategy!=PARTITION_STRATEGY_HASH)
886-
{
887-
PartitionPruneStep*step;
884+
PartitionPruneStep*step;
888885

889-
step=gen_prune_step_op(context,InvalidStrategy,
890-
false,NIL,NIL,NULL);
891-
result=lappend(result,step);
892-
}
886+
/* Strategy 1 */
887+
step=gen_prune_step_op(context,InvalidStrategy,
888+
false,NIL,NIL,nullkeys);
889+
result=lappend(result,step);
893890
}
894-
else
891+
elseif (generate_opsteps)
895892
{
896893
PartitionPruneStep*step;
897894

898-
/*Generate pruning steps from OpExpr clauses in keyclauses. */
895+
/*Strategy 2 */
899896
step=gen_prune_steps_from_opexps(part_scheme,context,
900897
keyclauses,nullkeys);
901898
if (step!=NULL)
902899
result=lappend(result,step);
903900
}
901+
elseif (bms_num_members(notnullkeys)==part_scheme->partnatts)
902+
{
903+
PartitionPruneStep*step;
904+
905+
/* Strategy 3 */
906+
step=gen_prune_step_op(context,InvalidStrategy,
907+
false,NIL,NIL,NULL);
908+
result=lappend(result,step);
909+
}
904910

905911
/*
906912
* Finally, results from all entries appearing in result should be

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

Lines changed: 41 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -993,6 +993,47 @@ explain (costs off) select * from mc2p where a = 1 and b > 1;
993993
Filter: ((b > 1) AND (a = 1))
994994
(3 rows)
995995

996+
-- all partitions but the default one should be pruned
997+
explain (costs off) select * from mc2p where a = 1 and b is null;
998+
QUERY PLAN
999+
-------------------------------------------
1000+
Append
1001+
-> Seq Scan on mc2p_default
1002+
Filter: ((b IS NULL) AND (a = 1))
1003+
(3 rows)
1004+
1005+
explain (costs off) select * from mc2p where a is null and b is null;
1006+
QUERY PLAN
1007+
-----------------------------------------------
1008+
Append
1009+
-> Seq Scan on mc2p_default
1010+
Filter: ((a IS NULL) AND (b IS NULL))
1011+
(3 rows)
1012+
1013+
explain (costs off) select * from mc2p where a is null and b = 1;
1014+
QUERY PLAN
1015+
-------------------------------------------
1016+
Append
1017+
-> Seq Scan on mc2p_default
1018+
Filter: ((a IS NULL) AND (b = 1))
1019+
(3 rows)
1020+
1021+
explain (costs off) select * from mc2p where a is null;
1022+
QUERY PLAN
1023+
--------------------------------
1024+
Append
1025+
-> Seq Scan on mc2p_default
1026+
Filter: (a IS NULL)
1027+
(3 rows)
1028+
1029+
explain (costs off) select * from mc2p where b is null;
1030+
QUERY PLAN
1031+
--------------------------------
1032+
Append
1033+
-> Seq Scan on mc2p_default
1034+
Filter: (b IS NULL)
1035+
(3 rows)
1036+
9961037
-- boolean partitioning
9971038
create table boolpart (a bool) partition by list (a);
9981039
create table boolpart_default partition of boolpart default;

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

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -137,6 +137,13 @@ explain (costs off) select * from mc2p where a = 2 and b < 1;
137137
explain (costs off)select*from mc2pwhere a>1;
138138
explain (costs off)select*from mc2pwhere a=1and b>1;
139139

140+
-- all partitions but the default one should be pruned
141+
explain (costs off)select*from mc2pwhere a=1and b isnull;
142+
explain (costs off)select*from mc2pwhere a isnulland b isnull;
143+
explain (costs off)select*from mc2pwhere a isnulland b=1;
144+
explain (costs off)select*from mc2pwhere a isnull;
145+
explain (costs off)select*from mc2pwhere b isnull;
146+
140147
-- boolean partitioning
141148
createtableboolpart (a bool) partition by list (a);
142149
createtableboolpart_default partition of boolpart default;

0 commit comments

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