On Thu, 18 Jun 2020 at 21:49, PG Bug reporting form
<noreply@postgresql.org> wrote:
> I am not good at English, so I will send a reproduction script.
Many thanks for the report. This is certainly a bug in the partition
pruning code for range partitioned tables.
A more simple case is:
create table rp (a int, b int) partition by range (a,b);
create table rp_2020 partition of rp for values from (2020, 1) to (2020, 7);
insert into rp values(2020,1);
explain select * from rp where a <= 2020 and b = 1;
Which gives:
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.00 rows=0 width=0)
One-Time Filter: false
(2 rows)
# set enable_partition_pruning=off;
SET
# select * from rp where a <= 2020 and b = 1;
a | b
------+---
2020 | 1
(1 row)
This seems to be caused by the following code, which assumes it's ok
to use the prefix for <= / = / >= btree operators. Initially, I
imagined that there's no reason to allow anything apart from = there,
but I suppose we could consider sub-ranges of partitions that are <=
2020, but then I don't really understand why the same thing can't be
done with < 2020.
/*
* We can't consider subsequent partition keys if the
* clause for the current key contains a non-inclusive
* operator.
*/
if (pc->op_strategy == BTLessStrategyNumber ||
pc->op_strategy == BTGreaterStrategyNumber)
consider_next_key = false;
break;
Perhaps Amit can explain the reason for that?
David