Re: Partition pruning can incorrectly exclude a RANGE default partition

On Tue, 28 Jul 2026 at 02:53, Chengpeng Yan <chengpeng_yan(at)outlook(dot)com> wrote:
> @@ -3031,15 +3031,7 @@ get_matching_range_bounds(PartitionPruneContext *context,
> */
> if (nvalues == 0)
> {
> - /* ignore key space not covered by any partitions */
> - if (partindices[minoff] < 0)
> - minoff++;
> - if (partindices[maxoff] < 0)
> - maxoff--;
> -
> result->scan_default = partition_bound_has_default(boundinfo);
> - Assert(partindices[minoff] >= 0 &&
> - partindices[maxoff] >= 0);
> result->bound_offsets = bms_add_range(NULL, minoff, maxoff);
> return result;

(Much of this has been said already, but I wanted to summarise my
independent analysis)

The problem is down to there being two distinct ways to mark that the
DEFAULT partition should be scanned with RANGE partitioned tables. I
wrote the attached debug_part_prune.diff patch to highlight this. This
provides additional debug output during pruning.

CREATE TABLE s2 (a int) PARTITION BY RANGE (a);
CREATE TABLE s2_1 PARTITION OF s2 FOR VALUES FROM (0) TO (10);
CREATE TABLE s2_d PARTITION OF s2 DEFAULT;

SELECT * FROM s2 postgres-# WHERE a IN (5, 15);
NOTICE: PartitionPruneStepOp: 0 N-exprs = 1, offsets = 1 (1),
scan_default = NO, scan_null = NO
NOTICE: PartitionPruneStepOp: 1 N-exprs = 1, offsets = 1 (2),
scan_default = NO, scan_null = NO
NOTICE: PartitionPruneStepCombine: 2 (UNION) N-inputs = 2 (0 1),
offsets = 2 (1 2), scan_default = NO, scan_null = NO

In the reported working case (without "a IS NOT NULL"), you can see
that "scan_default = NO", even though getting a = 15 requires that the
default partition is scanned. Instead, scanning the default is
represented with the offset = 2 bit being set. It's only later in
get_matching_partitions() that the boundinfo->indexes sees that the
indexes element is set to -1 and then marks the default to be scanned.

The problem occurs during PartitionPruneStepCombine INTERSECT steps
when the "a IS NOT NULL" clause is added. The results in a
miscalculation of the common partitions that must be scanned. If some
PruneStepResults use the bound_offsets way of marking that the DEFAULT
partition is to be scanned, and another step uses the
scan_default=true method, then once the intersected results are
calculated, neither representation remains. Consider the problem case:

SELECT * FROM s2 WHERE a IS NOT NULL and a IN (5,15);
NOTICE: PartitionPruneStepOp: 0 N-exprs = 1, offsets = 1 (1),
scan_default = NO, scan_null = NO
NOTICE: PartitionPruneStepOp: 1 N-exprs = 1, offsets = 1 (2),
scan_default = NO, scan_null = NO
NOTICE: PartitionPruneStepCombine: 2 (UNION) N-inputs = 2 (0 1),
offsets = 2 (1 2), scan_default = NO, scan_null = NO
NOTICE: PartitionPruneStepOp: 3 N-exprs = 0, offsets = 1 (1),
scan_default = YES, scan_null = NO
NOTICE: PartitionPruneStepCombine: 4 (INTERSECT) N-inputs = 2 (2 3),
offsets = 1 (1), scan_default = *NO*, scan_null = NO

Here you can see that the 2nd step (step_id=1) has offset=2 set. The
following step UNIONs those and ends up with 1+2. All good so far
(this is the IN (5,15) part). The step_id=3 step is for "a IS NOT
NULL" and has markings for s2_1 to be scanned (offset=1) and uses
scan_default=true to mark that the default must be scanned. It's easy
to see that the final INTERSECT step accidentally removes the offset=2
and scan_default=true markings, which is incorrect.

If we look at a similar case with a LIST partitioned table:

CREATE TABLE lp (a INT) PARTITION BY LIST(a);
CREATE TABLE lp_5 PARTITION OF lp FOR VALUES IN (5);
CREATE TABLE lp_d PARTITION OF lp DEFAULT;

SELECT * FROM lp WHERE a IS NOT NULL AND a IN(5,15);

NOTICE: PartitionPruneStepOp: 0 N-exprs = 1, offsets = 1 (0),
scan_default = NO, scan_null = NO
NOTICE: PartitionPruneStepOp: 1 N-exprs = 1, offsets = 0 (),
scan_default = YES, scan_null = NO
NOTICE: PartitionPruneStepCombine: 2 (UNION) N-inputs = 2 (0 1),
offsets = 1 (0), scan_default = YES, scan_null = NO
NOTICE: PartitionPruneStepOp: 3 N-exprs = 0, offsets = 1 (0),
scan_default = YES, scan_null = NO
NOTICE: PartitionPruneStepCombine: 4 (INTERSECT) N-inputs = 2 (2 3),
offsets = 1 (0), scan_default = YES, scan_null = NO

You can see step_id=2 uses scan_default=true and since step_id=3 has
that set too, that continues into the final intersected step's result.
No bugs there.

The reason RANGE uses this alternative way to represent DEFAULTs is so
that gaps in the range are easier to detect. If you have:

CREATE TABLE rp (a INT) PARTITION BY RANGE(a);
CREATE TABLE rp1 PARTITION OF rp FOR VALUES FROM (0) TO (10);
CREATE TABLE rp2 PARTITION OF rp FOR VALUES FROM (20) TO (30);
CREATE TABLE rp_d PARTITION OF rp DEFAULT;

SELECT * FROM rp WHERE a BETWEEN 0 AND 25;

Then the PartitionBoundInfoData .indexes will be = { -1, 0, -1, 1, -1}
and we'll land on the middle -1 when binary searching the
PartitionBoundInfoData.datums array. That middle -1 wouldn't be there
if there was no gap between rp1 and rp2.

I see a couple of ways to fix this:

1. Modify PartitionBoundInfoData to add a new Bitmapset which marks
which of the indexes[] array maps to -1. Then in
get_matching_range_bounds(), once we've set bound_offsets, check if we
have a bms_overlap with the new Bitmapset and set scan_default=true if
there is; or
2. Modify get_matching_range_bounds() so that we mark *all*
bound_offsets for the "if (nvalues == 0)" path.

#1 is more invasive as it requires a new field in
PartitionBoundInfoData, then some extra processing when we execute
each RANGE pruning step. This can happen often for run-time pruning
and this might slow down that enough to notice.

The concern I had with #2 is that we also mark the default partition
to be scanned in the "if (nvalues < partnatts)" case. This case is hit
when we RANGE partition by multiple columns and only have a pruning
step for some leading subset of those. Consider:

CREATE TABLE rb (a INT, b INT) PARTITION BY RANGE(a,b);
CREATE TABLE rb1 PARTITION OF rb FOR VALUES FROM (0,0) TO (0,10);
CREATE TABLE rb2 PARTITION OF rb FOR VALUES FROM (0,10) TO (0,20);
CREATE TABLE rb_d PARTITION OF rb DEFAULT;

SELECT * FROM rb WHERE a = 0;
NOTICE: PartitionPruneStepOp: 0 N-exprs = 1, offsets = 4 (0 1 2 3),
scan_default = YES, scan_null = NO

As expected, we have scan_default=true. To have a problem here, we
need to produce an INTERSECT combine step that combines the above step
with one with the bound_offset way of marking that we scan the default
partition. I think it might not be possible to trigger the same issue
in this scenario without having quals that conflict and result in
pruning partitions anyway. Consider:

SELECT * FROM rb WHERE (a = 0 AND b = 9) AND (a = 0 AND b>30);
NOTICE: PartitionPruneStepOp: 0 N-exprs = 1, offsets = 4 (0 1 2 3),
scan_default = YES, scan_null = NO
NOTICE: PartitionPruneStepOp: 1 N-exprs = 2, offsets = 1 (1),
scan_default = NO, scan_null = NO
NOTICE: PartitionPruneStepOp: 2 N-exprs = 2, offsets = 1 (3),
scan_default = NO, scan_null = NO
NOTICE: PartitionPruneStepCombine: 3 (INTERSECT) N-inputs = 3 (0 1
2), offsets = 0 (), scan_default = NO, scan_null = NO

Here, step_id=0 has scan_default=true and step_id=2 marks the default
to be scanned with offset = 3. That results in the INTERSECT step
matching no partitions! But in this case, that's valid since we can't
have b=9 AND b>30 rows. So, if we did fix this with method #1,
step_id=0 would mark the bound_offsets for all
PartitionBoundInfoData.indexes that map to the DEFAULT partition, and
that would result in the final pruning step mistakenly thinking that
the default needed to be scanned.

In other words, I think #2 is the correct fix. The attached v1-0001
patchifies that method.

Another alternative fix would be to modify the
PARTPRUNE_COMBINE_INTERSECT code to special-case RANGE partitioned
tables to check for any bound_offsets that map to the default
partition and set scan_default=true when it finds some. I don't like
this method as that code is currently unaware of different
partitioning methods and to fix it this way would mean adding a
special case for RANGE partitioned tables.

David