Обсуждение: BUG #16201: Second column in Range Partition is scanning all the partitions

The following bug has been logged on the website:

Bug reference:      16201
Logged by:          Rahul Saha
Email address:      rahulsaha0309@gmail.com
PostgreSQL version: 10.5
Operating system:   Amazon Linux
Description:

Hi Pg Team,

I was working on Partitions on version 10.5 and I am not able to understand
this behaviour, could you please share your comments.

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 A
=======================
postgres=> explain select * from sales3 where sale_year='2001';
                          QUERY PLAN                          
--------------------------------------------------------------
 Append  (cost=0.00..31.25 rows=8 width=20)
   ->  Seq Scan on child1  (cost=0.00..31.25 rows=8 width=20)
         Filter: (sale_year = 2001)
(3 rows)

Explain plans  with column A & B
==========================

postgres=> explain select * from sales3 where sale_year='2001' and
sale_month='5';
                          QUERY PLAN                          
--------------------------------------------------------------
 Append  (cost=0.00..35.50 rows=1 width=20)
   ->  Seq Scan on child1  (cost=0.00..35.50 rows=1 width=20)
         Filter: ((sale_year = 2001) AND (sale_month = 5))
(3 rows)

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 ?

On Fri, Jan 10, 2020 at 5:59 PM PG Bug reporting form
<noreply@postgresql.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