BUG #16201: Second column in Range Partition is scanning all the partitions
От | PG Bug reporting form |
---|---|
Тема | BUG #16201: Second column in Range Partition is scanning all the partitions |
Дата | |
Msg-id | 16201-c3c89cdee7215e9f@postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #16201: Second column in Range Partition is scanning all the partitions
|
Список | pgsql-bugs |
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 ?
В списке pgsql-bugs по дате отправления: