Re: postgresql 10.1 wrong plan in when using partitions bug

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




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




[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux