Re: Planning time is high in Postgres 11.5 Compared with Postgres 10.11

Hi Tom,

Actually , we used the same backup to restore on both V10 & V11  , So  the
data on both the versions is same.
After restore, we ran vacuum full analyze on both the servers .

default_statistics_target  value "1000" is same on both the versions.
Used the same postgresql.conf , i,e it is same on both V10 and V11

Thanks,
Avinash



On Mon, Dec 16, 2019 at 8:40 PM Tom Lane <[email protected]> wrote:

> avinash varma <[email protected]> writes:
> > Perf Report Output:
> > Samples: 41K of event 'cpu-clock:uhH', Event count (approx.): 10461000000
> >   Children      Self  Command   Shared Object          Symbol
> > *+   49.97%    49.97%  postgres  postgres               [.]
> > FunctionCall2Coll+   17.58%    17.58%  postgres  postgres
>  [.]
> > int8eq+   15.15%    15.15%  postgres  postgres               [.]
> eqjoinsel+
> >   12.82%    12.82%  postgres  postgres               [.]
> > eqjoinsel_semi.isra.17*
> > +    1.82%     1.82%  postgres  postgres               [.]
> pglz_decompress
> > +    0.96%     0.00%  postgres  [unknown]              [.]
> 0000000000000000
> > +    0.62%     0.00%  postgres  [unknown]              [.]
> > 0x0000000000000010
> >      0.56%     0.56%  postgres  postgres               [.]
> deconstruct_array
> >      0.20%     0.00%  postgres  [unknown]              [.]
> > 0x0000000001674bd0
>
> Hm.  eqjoinsel didn't change at all between v10 and v11.  Are you
> *sure* those installations have the same configurations?  A plausible
> theory is that one has got a much larger default_statistics_target
> than the other (or at least, did when these tables were last analyzed),
> and in consequence these tables have large most-common-values
> statistics arrays, but not in the v10 installation.
>
> [ thinks for a bit ]  It seems possible that you could end up here
> even if the user-level settings are indeed the same, as a result of
>
>
> https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=b5db1d93d
>
> which changed the method for deciding which values are MCVs.  Maybe
> these tables have some corner-case distribution that causes the new
> method to think there are many more MCVs than the old one thought.
>
> First thing to do is to look into pg_stats and see how large those
> arrays actually are in each case ...
>
>                         regards, tom lane
>


-- 
Thanks & Regards,

Avinash.