Re: BUG #16109: Postgres planning time is high across version - 10.6 vs 10.10

From: Mukesh Chhatani <chhatani(dot)mukesh(at)gmail(dot)com>
To: Andres Freund <andres(at)anarazel(dot)de>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #16109: Postgres planning time is high across version - 10.6 vs 10.10
Date: 2019-11-13 18:52:13
Message-ID: CACnmOYJQVHhuaYe70z0yFr2rKy8jqe=8Fx92c7-HXShQOEy8qw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-hackers

Thanks for response.

I analyzed after changing default_statistics_target but no improvement, are
there any recommendations around this parameter because as far as I have
seen increasing this parameter in the past lets optimizer choose better
plans and has never caused me this problem related to high planning time,
but I am open to suggestions since every problem is a new problem.

I know changing partitioned to unpartitioned and then changing columns
types makes no sense in terms of resolving the issue but that is what is
working now.

I will go ahead and change the parameter - default_statistics_target to 100
and analyze whole database and then wait for couple of hours and then run
my queries. Let me know if this approach is good.

Regards,
Mukesh

On Wed, Nov 13, 2019 at 12:42 PM Andres Freund <andres(at)anarazel(dot)de> wrote:

> Hi,
>
> On 2019-11-13 11:37:30 -0700, Mukesh Chhatani wrote:
> > FYI.. default_statistics_target was set to 10000 but I changed it 100 and
> > even to 1000 and still planning time was high.
>
> Note that you'd need to ANALYZE the involved tables before that change
> actually would effect planning time.
>
>
> > Update I was able to resolve the problem by changing - partitioned
> tables to
> > single table and changing data type of 2 columns used in the joins from
> > varchar to varchar(50).
>
> That's not going to be the fix. There's no efficiency difference between
> those. It's more likely that, that the different statistics target would
> have taken effect after the alter table etc.
>
> - Andres
>

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2019-11-13 19:13:51 Re: [BUG] Uninitializaed configOut.leafType used.
Previous Message Andres Freund 2019-11-13 18:47:59 Re: BUG #16104: Invalid DSA Memory Alloc Request in Parallel Hash

Browse pgsql-hackers by date

  From Date Subject
Next Message Alvaro Herrera 2019-11-13 18:58:38 Re: Invisible PROMPT2
Previous Message Alvaro Herrera 2019-11-13 18:50:47 Re: Creating foreign key on partitioned table is too slow