Re: postgresql 10.1 wrong plan in when using partitions bug

Mybe I wasnt clear. I'm having a 2 layers patitions mechanism :
My main table is called log_full :
CREATE TABLE log_full (a text,b text,c text, start_stop text, end_Date
date) partition range by (end_date))

Every day I create a partition that represent data from that day :
create table log_full_04_02_2018 partition of radius_log_full(end_date) for
VALUES from ('04-02-2018 00:00:00') TO ('05-02-2018 00:00:00') partition by
list (start_stop) ;

The partition that represent the current day consist of 8 paritions on
column start_stop that look like that :
create table log_full_04_02_2018_action_status partition of
log_full_04_02_2018 for VALUES in ('Start','Stop');

ALTER TABLE ONLY log_full_04_02_2018_action_status
ADD CONSTRAINT log_full_04_02_2018_action_status_pkey PRIMARY KEY (a,
b, c);

I checked the plan of the next query :
explain select count(*) from log_full where end_date between
to_date('2017/12/03','YY/MM/DD') and to_date('2017/12/03','YY/MM/DD');

and the result if full scan on all partitions.

Why it decided to run a full table scan on all partitions ?

2018-02-04 14:03 GMT+02:00 Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>:

>
>
> On 02/04/2018 11:14 AM, Mariel Cherkassky wrote:
> >
> > Hi,
> > I configured range partitions on a date column of my main
> > table(log_full). Each partition represents a day in the month. Every day
> > partition has a list parition of 4 tables on a text column.
> >
> > log_full
> > log_full_01_11_2017 -->
> > log_full _01_11_2017_x1
> > log_full _01_11_2017_x2
> > log_full _01_11_2017_x3
> > log_full _01_11_2017_x4
> > log_full_02_11_2017
> > log_full _02_11_2017_x1
> > log_full _02_11_2017_x2
> > log_full _02_11_2017_x3
> > log_full _02_11_2017_x4
> >
> > and so on....
> >
> >
> > The date column consist of date in the next format : YYYY-MM-DD HH:24:SS
> > for example : 2017-11-01 00:01:40
> >
> > I wanted to check the plan that I'm getting for a query that is using
> > the date column and it seems that the planner choose to do seq scans on
> > all tables.
> >
> > -Each partition consist from 15M rows.
> > I have about 120 partitions.
> >
> > The query :
> > explain select count(*) from log_full where end_date between
> > to_date('2017/12/03','YY/MM/DD') and to_date('2017/12/03','YY/MM/DD');
> >
> > The output is too long but it do full scans on all paritions...
> > any idea what can be the problem? Is it connected to the date format ?
> >
>
> You haven't shown us how the partitions are defined, nor the query plan.
> So it's rather hard to say. You mentioned text format, but then you use
> to_date() to query the partitioned table. Which I guess might be the
> cause, but it's hard to say for sure.
>
> regards
>
> --
> Tomas Vondra http://www.2ndQuadrant.com
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>