Skip to content

Commit e353389

Browse files
alvherredilipbalaut11Amit Langote
committed
Fix partition pruning with IS [NOT] NULL clauses
The original code was unable to prune partitions that could not possibly contain NULL values, when the query specified less than all columns in a multicolumn partition key. Reorder the if-tests so that it is, and add more 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 parent 32df1c9 commit e353389

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+
else if (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+
else if (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 mc2p where a > 1;
138138
explain (costs off) select * from mc2p where a = 1 and b > 1;
139139

140+
-- all partitions but the default one should be pruned
141+
explain (costs off) select * from mc2p where a = 1 and b is null;
142+
explain (costs off) select * from mc2p where a is null and b is null;
143+
explain (costs off) select * from mc2p where a is null and b = 1;
144+
explain (costs off) select * from mc2p where a is null;
145+
explain (costs off) select * from mc2p where b is null;
146+
140147
-- boolean partitioning
141148
create table boolpart (a bool) partition by list (a);
142149
create table boolpart_default partition of boolpart default;

0 commit comments

Comments
 (0)