postgres.git / summary / log / commit / refs
commit b9d9e9bcc5786e48713d0d44de3f02c200bb7eff
Author: David Rowley <drowley@postgresql.org>
Date: Fri Sep 18 05:14:21 2026 +0000
Fix pruning of DEFAULT partition in RANGE partitioned tables
Some code added in 489247b0e tried to prune the DEFAULT partition when
the next partition had a MINVALUE clause and likewise when the final
partition to scan had a MAXVALUE clause for the final partition key in
the pruning step. This code was incorrect as it could prune the default
partition incorrectly in cases such as:
p: PARTITION BY RANGE (a, b)
p1: FOR VALUES FROM (13, 0) TO (19, MAXVALUE)
pd: DEFAULT
SELECT * FROM t WHERE a = 32 AND b >= -7;
Here the pruning step for a = 32 and b >= -7 would see that only the
default partition needs to be scan, but it would then see that the
partition prior to the default had a MAXVALUE bound then prune away the
default thinking that it needn't be scanned. This could result in
incorrect results.
Fix this by moving the code that looks for the MAXVALUE bound into the
code handling BTEqualStrategyNumber so that when we're pruning with a
prefix of the partition keys, we check if the bound for the offset we've
calculated lands on a partition where the next partition key is bounded
with MAXVALUE. If so we don't include the default partition.
This leaves only the case of the first partition key. We handle that by
modifying the existing code that was checking the last key covered by
the given values.
Author: Ewan Young <kdbase.hack@gmail.com>
Reviewed-by: Tender Wang <tndrwang@gmail.com>
Reviewed-by: David Rowley <dgrowleyml@gmail.com>
Discussion: https://postgr.es/m/CAON2xHO=sqdqp=z8zWkybnWp0AuvefnAi2ez2vOYWrhXB6hHWQ@mail.gmail.com
Backpatch-through: 14
src/backend/partitioning/partprune.c | 62 ++++++++++--------
src/test/regress/expected/partition_prune.out | 94 +++++++++++++++++++++++++++
src/test/regress/sql/partition_prune.sql | 47 ++++++++++++++
3 files changed, 177 insertions(+), 26 deletions(-)
diff --git a/src/backend/partitioning/partprune.c b/src/backend/partitioning/partprune.c
index c93e0c8ab65..2efb2c864de 100644
--- a/src/backend/partitioning/partprune.c
+++ b/src/backend/partitioning/partprune.c
@@ -3145,9 +3145,17 @@ get_matching_range_bounds(PartitionPruneContext *context,
/*
* off + 1, then would be the offset of the greatest bound
- * to be included in the result.
+ * to be included in the result. The exception is the
+ * mirror image of the MINVALUE case above: if the matched
+ * bound is exactly (prefix, MAXVALUE), no row carrying
+ * this prefix can sort above it, so the key space beyond
+ * it need not be considered.
*/
- maxoff = off + 1;
+ if (boundinfo->kind[off][nvalues] ==
+ PARTITION_RANGE_DATUM_MAXVALUE)
+ maxoff = off;
+ else
+ maxoff = off + 1;
}
Assert(minoff >= 0 && maxoff >= 0);
@@ -3338,43 +3346,45 @@ get_matching_range_bounds(PartitionPruneContext *context,
Assert(minoff >= 0 && minoff <= boundinfo->ndatums);
Assert(maxoff >= 0 && maxoff <= boundinfo->ndatums);
+
/*
- * If the smallest partition to return has MINVALUE (negative infinity) as
- * its lower bound, increment it to point to the next finite bound
- * (supposedly its upper bound), so that we don't inadvertently end up
- * scanning the default partition.
+ * Check for cases that we're scanning the DEFAULT partition when no rows
+ * can exist there for the given value. This can happen when the first or
+ * final bound for partitions we're scanning have a MINVALUE or MAXVALUE
+ * clause respectively. We needn't scan the DEFAULT partition for values
+ * beyond the bound since the MINVALUE / MAXVALUE bound handles up to
+ * negative or positive infinity. The BTEqualStrategyNumber code above
+ * handled doing this for subsequent partition keys, so all that's left to
+ * handle here is the same for the first partition key. We needn't
+ * perform this for all steps as the intersected results from the
+ * recursive processing of pruning steps for leading keys means we only
+ * scan the partitions common to all steps in the intersected set of
+ * steps.
*/
- if (minoff < boundinfo->ndatums && partindices[minoff] < 0)
+ if (nvalues == 1)
{
- int lastkey = nvalues - 1;
-
- if (boundinfo->kind[minoff][lastkey] ==
- PARTITION_RANGE_DATUM_MINVALUE)
+ /*
+ * Check if we're scanning the default and if a MINVALUE bound covers
+ * the key space for the lower end.
+ */
+ if (minoff < boundinfo->ndatums && partindices[minoff] < 0 &&
+ boundinfo->kind[minoff][0] == PARTITION_RANGE_DATUM_MINVALUE)
{
minoff++;
Assert(boundinfo->indexes[minoff] >= 0);
}
- }
- /*
- * If the previous greatest partition has MAXVALUE (positive infinity) as
- * its upper bound (something only possible to do with multi-column range
- * partitioning), we scan switch to it as the greatest partition to
- * return. Again, so that we don't inadvertently end up scanning the
- * default partition.
- */
- if (maxoff >= 1 && partindices[maxoff] < 0)
- {
- int lastkey = nvalues - 1;
-
- if (boundinfo->kind[maxoff - 1][lastkey] ==
- PARTITION_RANGE_DATUM_MAXVALUE)
+ /*
+ * Likewise, do the same check for the upper range partition if the
+ * prior bound is a MAXVALUE kind.
+ */
+ if (maxoff >= 1 && partindices[maxoff] < 0 &&
+ boundinfo->kind[maxoff - 1][0] == PARTITION_RANGE_DATUM_MAXVALUE)
{
maxoff--;
Assert(boundinfo->indexes[maxoff] >= 0);
}
}
-
Assert(minoff >= 0 && maxoff >= 0);
if (minoff <= maxoff)
result->bound_offsets = bms_add_range(NULL, minoff, maxoff);
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index 8f1119a026d..191901210a8 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -1132,6 +1132,100 @@ explain (costs off) select count(*) from mc2bp where c1 > 7;
(3 rows)
drop table mc2bp;
+-- Ensure the default partition is not pruned when a bound is unbounded only
+-- in a trailing key
+create table mc2pdmax (a int, b int) partition by range (a, b);
+create table mc2pdmax_default partition of mc2pdmax default;
+create table mc2pdmax1 partition of mc2pdmax for values from (13, 0) to (19, maxvalue);
+-- Ensure we only scan the default partition in the following two cases
+explain (costs off) select * from mc2pdmax where a = 32 and b >= -7;
+ QUERY PLAN
+-----------------------------------------------
+ Seq Scan on mc2pdmax_default mc2pdmax
+ Filter: ((b >= '-7'::integer) AND (a = 32))
+(2 rows)
+
+explain (costs off) select * from mc2pdmax where a >= 25 and b >= 0;
+ QUERY PLAN
+---------------------------------------
+ Seq Scan on mc2pdmax_default mc2pdmax
+ Filter: ((a >= 25) AND (b >= 0))
+(2 rows)
+
+-- Ensure we don't scan the default when the MAXVALUE bound makes it so
+-- no rows matching the WHERE clause can exist there.
+explain (costs off) select * from mc2pdmax where a = 19 and b >= 5;
+ QUERY PLAN
+-----------------------------------
+ Seq Scan on mc2pdmax1 mc2pdmax
+ Filter: ((b >= 5) AND (a = 19))
+(2 rows)
+
+-- Ensure we do scan the default to get the values with a > 19.
+explain (costs off) select * from mc2pdmax where a >= 19 and b >= 5;
+ QUERY PLAN
+-----------------------------------------------
+ Append
+ -> Seq Scan on mc2pdmax1 mc2pdmax_1
+ Filter: ((a >= 19) AND (b >= 5))
+ -> Seq Scan on mc2pdmax_default mc2pdmax_2
+ Filter: ((a >= 19) AND (b >= 5))
+(5 rows)
+
+drop table mc2pdmax;
+create table mc2pdmin (a int, b int) partition by range (a, b);
+create table mc2pdmin_default partition of mc2pdmin default;
+create table mc2pdmin1 partition of mc2pdmin for values from (13, minvalue) to (19, 5);
+-- Ensure we only scan the default
+explain (costs off) select * from mc2pdmin where a = 5 and b <= 7;
+ QUERY PLAN
+---------------------------------------
+ Seq Scan on mc2pdmin_default mc2pdmin
+ Filter: ((b <= 7) AND (a = 5))
+(2 rows)
+
+-- Ensure we don't scan the default when the MINVALUE bound results in no rows
+-- matching in the default partition.
+explain (costs off) select * from mc2pdmin where a = 13 and b <= 7;
+ QUERY PLAN
+-----------------------------------
+ Seq Scan on mc2pdmin1 mc2pdmin
+ Filter: ((b <= 7) AND (a = 13))
+(2 rows)
+
+-- Ensure we do scan the default to get rows with a < 13.
+explain (costs off) select * from mc2pdmin where a <= 13 and b <= 7;
+ QUERY PLAN
+-----------------------------------------------
+ Append
+ -> Seq Scan on mc2pdmin1 mc2pdmin_1
+ Filter: ((a <= 13) AND (b <= 7))
+ -> Seq Scan on mc2pdmin_default mc2pdmin_2
+ Filter: ((a <= 13) AND (b <= 7))
+(5 rows)
+
+drop table mc2pdmin;
+create table mc2pinf (a int, b int) partition by range (a, b);
+create table mc2pinf_default partition of mc2pinf default;
+create table mc2pinf1 partition of mc2pinf for values from (0, 0) to (maxvalue, maxvalue);
+create table mc2pinf2 partition of mc2pinf for values from (minvalue, minvalue) to (0, 0);
+-- Ensure the default partition is still pruned when the first key is
+-- unbounded
+explain (costs off) select * from mc2pinf where a = 32 and b >= -7;
+ QUERY PLAN
+-----------------------------------------------
+ Seq Scan on mc2pinf1 mc2pinf
+ Filter: ((b >= '-7'::integer) AND (a = 32))
+(2 rows)
+
+explain (costs off) select * from mc2pinf where a = -32 and b <= 7;
+ QUERY PLAN
+-----------------------------------------------
+ Seq Scan on mc2pinf2 mc2pinf
+ Filter: ((b <= 7) AND (a = '-32'::integer))
+(2 rows)
+
+drop table mc2pinf;
-- 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 f967658d4b5..01f44538fb6 100644
--- a/src/test/regress/sql/partition_prune.sql
+++ b/src/test/regress/sql/partition_prune.sql
@@ -212,6 +212,53 @@ explain (costs off) select count(*) from mc2bp where c1 > 7;
drop table mc2bp;
+-- Ensure the default partition is not pruned when a bound is unbounded only
+-- in a trailing key
+create table mc2pdmax (a int, b int) partition by range (a, b);
+create table mc2pdmax_default partition of mc2pdmax default;
+create table mc2pdmax1 partition of mc2pdmax for values from (13, 0) to (19, maxvalue);
+
+-- Ensure we only scan the default partition in the following two cases
+explain (costs off) select * from mc2pdmax where a = 32 and b >= -7;
+explain (costs off) select * from mc2pdmax where a >= 25 and b >= 0;
+
+-- Ensure we don't scan the default when the MAXVALUE bound makes it so
+-- no rows matching the WHERE clause can exist there.
+explain (costs off) select * from mc2pdmax where a = 19 and b >= 5;
+
+-- Ensure we do scan the default to get the values with a > 19.
+explain (costs off) select * from mc2pdmax where a >= 19 and b >= 5;
+
+drop table mc2pdmax;
+
+create table mc2pdmin (a int, b int) partition by range (a, b);
+create table mc2pdmin_default partition of mc2pdmin default;
+create table mc2pdmin1 partition of mc2pdmin for values from (13, minvalue) to (19, 5);
+
+-- Ensure we only scan the default
+explain (costs off) select * from mc2pdmin where a = 5 and b <= 7;
+
+-- Ensure we don't scan the default when the MINVALUE bound results in no rows
+-- matching in the default partition.
+explain (costs off) select * from mc2pdmin where a = 13 and b <= 7;
+
+-- Ensure we do scan the default to get rows with a < 13.
+explain (costs off) select * from mc2pdmin where a <= 13 and b <= 7;
+
+drop table mc2pdmin;
+
+create table mc2pinf (a int, b int) partition by range (a, b);
+create table mc2pinf_default partition of mc2pinf default;
+create table mc2pinf1 partition of mc2pinf for values from (0, 0) to (maxvalue, maxvalue);
+create table mc2pinf2 partition of mc2pinf for values from (minvalue, minvalue) to (0, 0);
+
+-- Ensure the default partition is still pruned when the first key is
+-- unbounded
+explain (costs off) select * from mc2pinf where a = 32 and b >= -7;
+explain (costs off) select * from mc2pinf where a = -32 and b <= 7;
+
+drop table mc2pinf;
+
-- boolean partitioning
create table boolpart (a bool) partition by list (a);
create table boolpart_default partition of boolpart default;