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

út 17. 12. 2019 v 7:32 odesílatel avinash varma <avinashvarma443(at)gmail(dot)com>
napsal:

> Hi Pavel,
>
> Collation " en_US.UTF-8" is same on both databases. Infact both the v10
> and V11 databases are on same machine.
>
> Can you please let us know if i need to check anything..
>

a) please don't send top post reply
https://en.wikipedia.org/wiki/Posting_style - top posting is prohibited here

b) can you compare size of databases, indexes?

Pavel

> Thanks,
> Avinash
>
> On Tue, Dec 17, 2019 at 11:50 AM Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
>
>>
>>
>> út 17. 12. 2019 v 7:11 odesílatel avinash varma <
>> avinashvarma443(at)gmail(dot)com> napsal:
>>
>>> Hi Tom,
>>>
>>> You didn't answer the question:
>>>
>>> >> First thing to do is to look into pg_stats and see how large those
>>> >> arrays actually are in each case ...
>>>
>>> We reproduced issue with sample tables "child" and "core", moreover
>>> these tables doesn't contains any data in it in both postgresql 10 & 11.
>>>
>>> select count(1) from child --0
>>> select count(1) from core --0
>>>
>>> I did analyzed both the tables using the below command and after which i
>>> ran the below explain analyze and took the perf report.
>>>
>>> Vacuum analyze child;
>>> vacuum analyze core;
>>>
>>> Both configuration parameters are identical in V10 & V11. But we observe
>>> high planning time in V11 when compared with V10.
>>>
>>> PostDB11=# explain analyze SELECT --DISTINCT kc.childid AS rlid,
>>> PostDB11-# kc.id AS rlrightid--,
>>> PostDB11-# -- 0 AS rlproxytype
>>> PostDB11-# FROM child kc
>>> PostDB11-# WHERE NOT (EXISTS ( SELECT 1
>>> PostDB11(# FROM core
>>> PostDB11(# WHERE kc.id = core.groupid));
>>>
>>>
>>> Postgres 10
>>>
>>> -bash-4.2$ perf report -g
>>> Samples: 6K of event 'cpu-clock:uhH', Event count (approx.): 1541000000
>>> Children Self Command Shared Object Symbol
>>>
>>>
>>>
>>>
>>> *+ 13.72% 0.00% postgres [unknown] [.]
>>> 0000000000000000+ 5.34% 0.08% postgres libc-2.17.so
>>> <http://libc-2.17.so> [.] __vsnprintf_chk+ 4.62% 1.52%
>>> postgres libc-2.17.so <http://libc-2.17.so> [.] vfprintf+
>>> 4.59% 4.59% postgres postgres [.] SearchCatCache+
>>> 4.12% 0.00% postgres [unknown] [.] 0x0000000001d86000*
>>> + 4.09% 4.09% postgres postgres [.] base_yyparse
>>> + 2.09% 0.00% postgres [unknown] [.]
>>> 0x312e2e32362e3135
>>> + 2.04% 2.03% postgres postgres [.]
>>> hash_search_with_hash_value
>>> 1.83% 1.83% postgres libc-2.17.so [.]
>>> __strcmp_sse42
>>> + 1.78% 0.00% postgres [unknown] [.]
>>> 0x0000000001d83638
>>> + 1.74% 0.00% postgres [unknown] [.]
>>> 0x0000000000cb3260
>>> + 1.49% 1.43% postgres libc-2.17.so [.]
>>> __GI___printf_fp_l
>>>
>>>
>>>
>>> Postgres 11
>>>
>>>
>>> bash-4.2$ perf report -g
>>> Samples: 235K of event 'cpu-clock:uhH', Event count (approx.):
>>> 58888750000
>>> Children Self Command Shared Object Symbol
>>> + 37.73% 37.73% postgres postgres [.] FunctionCall2Coll
>>> + 28.57% 28.57% postgres postgres [.] eqjoinsel
>>> + 13.94% 13.94% postgres postgres [.] int8eq
>>> + 5.68% 5.68% postgres postgres [.]
>>> eqjoinsel_semi.isra.3
>>> + 1.78% 0.14% postgres libc-2.17.so [.] __clock_gettime
>>> + 1.76% 1.76% postgres postgres [.] pglz_decompress
>>> + 1.68% 1.68% postgres [vdso] [.]
>>> __vdso_clock_gettime
>>> + 1.43% 0.00% postgres [unknown] [.] 0000000000000000
>>> + 1.22% 0.00% postgres postgres [.] TTSOpsVirtual+0x0
>>> + 0.93% 0.00% postgres postgres [.]
>>> TTSOpsBufferHeapTuple+0x0
>>> + 0.72% 0.00% postgres [unknown] [.]
>>> 0x00000000026bb0d0
>>> 0.44% 0.44% postgres postgres [.] deconstruct_array
>>>
>>> Thanks,
>>> Avinash
>>>
>>
>> is same collation in both databases?
>>
>> Maybe there are some issues in virtualization
>>
>> Pavel
>>
>
>
> --
> Thanks & Regards,
>
> Avinash.
>