Re: postgresql 10.1 wrong plan in when using partitions bug

On Sun, Feb 4, 2018 at 5:14 AM, Mariel Cherkassky <
mariel(dot)cherkassky(at)gmail(dot)com> 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.)