Re: Planning performance problem (67626.278ms)

On 2019/04/08 16:33, Pavel Stehule wrote:
> 
> 
> po 8. 4. 2019 v 16:11 odesílatel Krzysztof Plocharz 
> <plocharz@9livesdata.com <mailto:plocharz@9livesdata.com>> napsal:
> 
>     Hi
> 
>     We have some very strange query planning problem. Long story short it
>     takes 67626.278ms just to plan. Query execution takes 12ms.
> 
>     Query has 7 joins and 2 subselects.
>     It looks like the issue is not deterministic, sometimes is takes few ms
>     to plan the query.
> 
>     One of the tables has 550,485,942 live tuples and 743,504,012 dead
>     tuples. Running ANALYZE on that tables solves the problem only
>     temporarily.
> 
>     Question is how can we debug what is going on?
> 
> 
> please check your indexes against bloating. Planner get min and max from 
> indexes and this operation is slow on bloat indexes.
> 

Yes, we thought about this, there are over 700,000,000 dead tuples. But 
as you said, it should not result in 67 second planning...

> but 67 sec is really slow - it can be some other other problem - it is 
> real computer or virtual?
> 

real, with pretty good specs: NVME drives, Six-Core AMD Opteron, 64GB of 
ram. During testing system was mostly idle.

> 
>     Best Regards,
>     Krzysztof Płocharz
> 
>