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 ?