Re: postgresql 10.1 wrong plan in when using partitions bug - Mailing list pgsql-performance
| From | Rick Otten |
|---|---|
| Subject | Re: postgresql 10.1 wrong plan in when using partitions bug |
| Date | |
| Msg-id | CAMAYy4KT-xvP6Q7iBvW5YAmmcrzpyTfACaLdjKn_tTvLNUu9+w@mail.gmail.com Whole thread Raw |
| In response to | postgresql 10.1 wrong plan in when using partitions bug (Mariel Cherkassky <mariel.cherkassky@gmail.com>) |
| Responses |
Re: postgresql 10.1 wrong plan in when using partitions bug
|
| List | pgsql-performance |
On Sun, Feb 4, 2018 at 5:14 AM, Mariel Cherkassky <mariel.cherkassky@gmail.com> wrote:
None of the various queries using these views on top of my hand constructed "partitions" are using indexes.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_fulllog_full_01_11_2017 -->log_full _01_11_2017_x1log_full _01_11_2017_x2log_full _01_11_2017_x3log_full _01_11_2017_x4log_full_02_11_2017log_full _02_11_2017_x1log_full _02_11_2017_x2log_full _02_11_2017_x3log_full _02_11_2017_x4and 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:40I 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.
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.)
pgsql-performance by date: