pg.ddx.io pgsql-committers@postgresql.org mailing list archive
help / color / mirror / Atom feedFrom: David Rowley <drowley@postgresql.org>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Fix incorrect multi-column RANGE partition pruning
Date: Fri, 28 Aug 2026 01:13:18 +0000
Message-ID: <E1wzl9h-00000002Px6-3A7I@gemulon.postgresql.org> (raw)
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
Branch
------
REL_14_STABLE
Details
-------
https://git.postgresql.org/pg/commitdiff/978caef1936fc06a6dcc75283115ede953ab6d34
Modified Files
--------------
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(-)
view thread (7+ messages)
Message-ID: <E1wzl9h-00000002Px6-3A7I@gemulon.postgresql.org>
Permalink: ../E1wzl9h-00000002Px6-3A7I@gemulon.postgresql.org/
Also on: postgresql.org/message-id/E1wzl9h-00000002Px6-3A7I@gemulon.postgresql.org
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-committers@postgresql.org
Cc: drowley@postgresql.org, pgsql-committers@lists.postgresql.org
Subject: Re: pgsql: Fix incorrect multi-column RANGE partition pruning
In-Reply-To: <E1wzl9h-00000002Px6-3A7I@gemulon.postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox