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
<[email protected]> wrote:
> I created range partitions on two columns and when I try to see explain p=
lan
> 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:
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> 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:
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> 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
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
>
> postgres=3D> explain select * from sales3 where  sale_month=3D'9';
>                           QUERY PLAN
> --------------------------------------------------------------
>  Append  (cost=3D0.00..62.50 rows=3D16 width=3D20)
>    ->  Seq Scan on child1  (cost=3D0.00..31.25 rows=3D8 width=3D20)
>          Filter: (sale_month =3D 9)
>    ->  Seq Scan on child2  (cost=3D0.00..31.25 rows=3D8 width=3D20)
>          Filter: (sale_month =3D 9)
> (5 rows)
>
> If you see the last Explain plan, it is scanning both  the child table. I=
s
> it expected behaviour, if yes can you please help me understand that ?

I=E2=80=99ve not tested this on the target version yet, so I might be missi=
ng
something, but this seems to me expected behavior because the range of
partition child1 would include sale_month=3D9 of any sale_year >=3D 2000
and sale_year <=3D 2009, and the range of partition child2 would include
sale_month=3D9 of any sale_year >=3D 2011 and sale_year <=3D 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=3D1 with any
y>=3D2, x=3D2 with any non-null y, and x=3D3 with any y<4.

Best regards,
Etsuro Fujita