Re: postgresql 10.1 wrong plan in when using partitions bug

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

 




On Sun, Feb 4, 2018 at 5:14 AM, Mariel Cherkassky <mariel.cherkassky@xxxxxxxxx> 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 ?

Thanks , Mariel.

I'm wrestling with a very similar problem too - except instead of official partitions I have a views on top of a bunch (50+) of unioned materialized views, each "partition" with 10M - 100M rows.  On 9.6.6 the queries would use the indexes on each materialized view.  On 10.1, every materialized view is sequence scanned.  (Killing the performance of many queries.)  I have 4 or 5 sets of materialized views organized this way with views on top of them.

I've checked for invalid indexes.

I've done Analyze, and Vaccuum Analyze on all sub-materialized views.

I've reindexed the materialized views.

I've experimented with geqo tunables.
I've experimented with  turning parallel gather off and on and setting it to different levels.
I've tried setting random page cost very high, and very low.
I tried turning nested loops on and off.
I tried setting effective_cache_size very small.

None of the various queries using these views on top of my hand constructed "partitions" are using indexes.

All of the exact same queries used the indexes in 9.6.6 before the upgrade.  Without the indexes, hitting these 1B+ row aggregate tables I'm seeing a 10x to 100x slowdown since upgrading.  This is killing us.

Not only that but with 50 tables under the view, and each one getting a parallel sequence scan, it is kind of impressive how much CPU one of these queries can use at once.

I'm mostly hoping with fingers crossed that something in 10.2, which is coming out next week, fixes it.  I was planning on posting my dilemma to this list this morning since I'm running out of ideas.  I really need to fix the issue this weekend to meet some business deadlines for data processing early in the week.  So my other hail mary pass this weekend, besides seeking ideas on this list, was to see if I could bump my version to 10.2 early.  (I'm not sure how to do that since I've been using Ubuntu packages and waiting for official releases prior to now, but I'm sure I can figure it out.)





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

  Powered by Linux