postgres.git / summary / log / commit / refs
commit 7a74e5ed92d4e36f5b8da3db66457d6f32cf80f3
Author: David Rowley <drowley@postgresql.org>
Date: Fri Aug 28 01:10:56 2026 +0000
Fix incorrect multi-column RANGE partition pruning
When performing partition pruning with a RANGE partitioned table where
the pruning quals are only present for a leading prefix of the partition
key, it was possible that partition pruning would accidentally prune away
some partitions which shouldn't be pruned and include some partitions that
were not needed.
This happened due to an incorrectly coded loop bound which was
terminating the loop when the bound reached the first or last element in
the partition bound array. This resulted in those end elements not being
checked in cases where they should be checked. It appears that it might
have been coded this way to avoid stepping off the array, but that was
done incorrectly as it failed to take into account the direction of travel
through the array (the loop can go forwards or backwards). I.e., it's
valid to loop when 'off' is the last element if we're going backwards
through the array, and valid to loop if 'off' is 0 and we're looping
forward through the array, but the code as it was didn't allow that.
Here we fix this by moving the loop condition check to after we've
calculated the array element to process, and break from the loop if that
element is beyond either end of the array.
Example of accidentally pruned partition:
p: partition by range (a, b);
p1: for values from (1, 4) to (1, 7);
p2: for values from (1, 7) to (3, 8);
p3: for values from (4, 8) to (6, 9);
def: default;
select * from p where a <= 1;
Here p2 was pruned by mistake.
Example of accidentally not pruning a partition:
p: partition by range (a, b);
p1: for values from (7, 2) to (7, 7);
def: default;
select * from p where a > 7;
No partitions would be pruned in this case, despite it being impossible
for matching rows to exist in p1.
Author: David Rowley <dgrowleyml@gmail.com>
Reviewed-by: Ayush Tiwari <ayushtiwari.slg01@gmail.com>
Reviewed-by: Tender Wang <tndrwang@gmail.com>
Discussion: https://postgr.es/m/CAApHDvp5ne9AWaH-tG1Lke-USLz3NwWLWTUdP5NT7ypKtcFqcg@mail.gmail.com
Backpatch-through: 14
src/backend/partitioning/partprune.c | 14 +++++++++---
src/test/regress/expected/partition_prune.out | 33 +++++++++++++++++++++++++++
src/test/regress/sql/partition_prune.sql | 20 ++++++++++++++++
3 files changed, 64 insertions(+), 3 deletions(-)
diff --git a/src/backend/partitioning/partprune.c b/src/backend/partitioning/partprune.c
index afe57ac297d..86fa13b3e05 100644
--- a/src/backend/partitioning/partprune.c
+++ b/src/backend/partitioning/partprune.c
@@ -3207,12 +3207,15 @@ get_matching_range_bounds(PartitionPruneContext *context,
* of smallest such bound) or find the smallest one that's
* greater than the lookup values and set minoff to that.
*/
- while (off >= 1 && off < boundinfo->ndatums - 1)
+ while (true)
{
int32 cmpval;
int nextoff;
nextoff = inclusive ? off - 1 : off + 1;
+
+ if (nextoff < 0 || nextoff >= boundinfo->ndatums)
+ break;
cmpval =
partition_rbound_datum_cmp(partsupfunc,
partcollation,
@@ -3266,16 +3269,21 @@ get_matching_range_bounds(PartitionPruneContext *context,
if (off >= 0)
{
/*
- * See the comment above.
+ * As above, check adjacent bounds to see if the bound is
+ * equal to the lookup value.
*/
if (is_equal && nvalues < partnatts)
{
- while (off >= 1 && off < boundinfo->ndatums - 1)
+ while (true)
{
int32 cmpval;
int nextoff;
nextoff = inclusive ? off + 1 : off - 1;
+
+ if (nextoff < 0 || nextoff >= boundinfo->ndatums)
+ break;
+
cmpval = partition_rbound_datum_cmp(partsupfunc,
partcollation,
boundinfo->datums[nextoff],
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index aa821646011..8f1119a026d 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -1099,6 +1099,39 @@ explain (costs off) select * from mc2p where b is null;
Filter: (b IS NULL)
(2 rows)
+create table mc2ap (a int, b int) partition by range (a, b);
+create table mc2ap1 partition of mc2ap for values from (1, 4) to (1, 7);
+create table mc2ap2 partition of mc2ap for values from (1, 7) to (3, 8);
+create table mc2ap3 partition of mc2ap for values from (4, 8) to (6, 9);
+create table mc2ap_def partition of mc2ap default;
+-- Ensure we scan all partitions apart from mc2ap3
+explain (costs off) select count(*) from mc2ap where a <= 1;
+ QUERY PLAN
+-------------------------------------------
+ Aggregate
+ -> Append
+ -> Seq Scan on mc2ap1 mc2ap_1
+ Filter: (a <= 1)
+ -> Seq Scan on mc2ap2 mc2ap_2
+ Filter: (a <= 1)
+ -> Seq Scan on mc2ap_def mc2ap_3
+ Filter: (a <= 1)
+(8 rows)
+
+drop table mc2ap;
+create table mc2bp (c1 int, c2 int) partition by range (c1, c2);
+create table mc2bp1 partition of mc2bp for values from (7, 2) to (7, 7);
+create table mc2bp_def partition of mc2bp default;
+-- Ensure mc2bp1 is pruned and we only scan mc2bp_def
+explain (costs off) select count(*) from mc2bp where c1 > 7;
+ QUERY PLAN
+-----------------------------------
+ Aggregate
+ -> Seq Scan on mc2bp_def mc2bp
+ Filter: (c1 > 7)
+(3 rows)
+
+drop table mc2bp;
-- boolean partitioning
create table boolpart (a bool) partition by list (a);
create table boolpart_default partition of boolpart default;
diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sql
index dac673ef80a..f967658d4b5 100644
--- a/src/test/regress/sql/partition_prune.sql
+++ b/src/test/regress/sql/partition_prune.sql
@@ -192,6 +192,26 @@ explain (costs off) select * from mc2p where a is null and b = 1;
explain (costs off) select * from mc2p where a is null;
explain (costs off) select * from mc2p where b is null;
+create table mc2ap (a int, b int) partition by range (a, b);
+create table mc2ap1 partition of mc2ap for values from (1, 4) to (1, 7);
+create table mc2ap2 partition of mc2ap for values from (1, 7) to (3, 8);
+create table mc2ap3 partition of mc2ap for values from (4, 8) to (6, 9);
+create table mc2ap_def partition of mc2ap default;
+
+-- Ensure we scan all partitions apart from mc2ap3
+explain (costs off) select count(*) from mc2ap where a <= 1;
+
+drop table mc2ap;
+
+create table mc2bp (c1 int, c2 int) partition by range (c1, c2);
+create table mc2bp1 partition of mc2bp for values from (7, 2) to (7, 7);
+create table mc2bp_def partition of mc2bp default;
+
+-- Ensure mc2bp1 is pruned and we only scan mc2bp_def
+explain (costs off) select count(*) from mc2bp where c1 > 7;
+
+drop table mc2bp;
+
-- boolean partitioning
create table boolpart (a bool) partition by list (a);
create table boolpart_default partition of boolpart default;
[parent: 3fcb7167198e]