Re: BUG #16201: Second column in Range Partition is scanning all the partitions

On Fri, Jan 10, 2020 at 5:59 PM PG Bug reporting form
<noreply(at)postgresql(dot)org> wrote:
> I created range partitions on two columns and when I try to see explain plan
> for column b as where conditions, it does scanning on all the partitions. Is
> it expected behaviour or this is a bug. Please find below the steps -
>
> PG Version 10.5 -
>
> Create Parent Table:
> ================
> CREATE TABLE sales3
> (
> dept_no int,
> sale_year int,
> sale_month int,
> sale_day int,
> amount int
> )
> PARTITION BY RANGE(sale_year, sale_month);
>
> Create Partition/Child Table:
> ========================
> create table child1 partition of sales3 for values from ('2000','4') to
> ('2010','7');
>
> create table child2 partition of sales3 for values from ('2011','8') to
> ('2020','12');
>
> create table child3 partition of sales3 for values from ('2021','13') to
> ('2023','16');
>
> create table child4 partition of sales3 for values from ('2021','17') to
> ('2023','1000');

> Explain plans with column B
> =======================
>
> postgres=> explain select * from sales3 where sale_month='9';
> QUERY PLAN
> --------------------------------------------------------------
> Append (cost=0.00..62.50 rows=16 width=20)
> -> Seq Scan on child1 (cost=0.00..31.25 rows=8 width=20)
> Filter: (sale_month = 9)
> -> Seq Scan on child2 (cost=0.00..31.25 rows=8 width=20)
> Filter: (sale_month = 9)
> (5 rows)
>
> If you see the last Explain plan, it is scanning both the child table. Is
> it expected behaviour, if yes can you please help me understand that ?

I’ve not tested this on the target version yet, so I might be missing
something, but this seems to me expected behavior because the range of
partition child1 would include sale_month=9 of any sale_year >= 2000
and sale_year <= 2009, and the range of partition child2 would include
sale_month=9 of any sale_year >= 2011 and sale_year <= 2020. See the
documentation on the CREATE TABLE page, especially this:

When creating a range partition, the lower bound specified with FROM
is an inclusive bound, whereas the upper bound specified with TO is an
exclusive bound. That is, the values specified in the FROM list are
valid values of the corresponding partition key columns for this
partition, whereas those in the TO list are not. Note that this
statement must be understood according to the rules of row-wise
comparison (Section 9.23.5). For example, given PARTITION BY RANGE
(x,y), a partition bound FROM (1, 2) TO (3, 4) allows x=1 with any
y>=2, x=2 with any non-null y, and x=3 with any y<4.

Best regards,
Etsuro Fujita