pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: rahulsaha0309@gmail.com
Subject: BUG #16201: Second column in Range Partition is scanning all the partitions
Date: Fri, 10 Jan 2020 08:58:23 +0000
Message-ID: <16201-c3c89cdee7215e9f@postgresql.org> (raw)

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 ?



view thread (2+ messages)  latest in thread

Message-ID: <16201-c3c89cdee7215e9f@postgresql.org>
Permalink:  ../16201-c3c89cdee7215e9f@postgresql.org/
Also on:    postgresql.org/message-id/16201-c3c89cdee7215e9f@postgresql.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: noreply@postgresql.org, rahulsaha0309@gmail.com
  Subject: Re: BUG #16201: Second column in Range Partition is scanning all the partitions
  In-Reply-To: <16201-c3c89cdee7215e9f@postgresql.org>
  References: <16201-c3c89cdee7215e9f@postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox