Re: Slow planing...

pá 6. 12. 2019 v 15:12 odesílatel Mladen Marinović <
mladen(dot)marinovic(at)kset(dot)org> napsal:

> After a couple of hours of trying different stuff, set enable_mergejoin =
> off made the planning time look better: Planning time: 0.322 ms
> Any ideas why this helps?
>

pls, can you try reindex all related indexes? Sometimes planning time is
high when indexes are bloated.

Regards

Pavel

> Regards,
> Mladen Marinović
>
> On Fri, Dec 6, 2019 at 11:14 AM Mladen Marinović <
> mladen(dot)marinovic(at)kset(dot)org> wrote:
>
>> Hi,
>>
>> Since this morning our system is running slower than usual. It turns out
>> that some queries take a very long time to plan ( > 1 second). The problem
>> occurs when joining bigger tables. There are no partition for the used
>> tables. The problem has a time correlation with the last
>> autovacuum/autoanalyse this morning, but manual vacuuming and analysing did
>> not fix the problem.
>>
>> An example explain is:
>>
>> EXPLAIN ANALYSE
>> SELECT 1
>> FROM table_a a
>> LEFT JOIN table_b bON b.a_id= a.id
>> WHERE a.object_id=13
>> AND a.timestamp<'2019-12-06'
>> AND a.timestamp>'2019-12-03'
>>
>> Nested Loop Left Join (cost=1.28..18137.57 rows=6913 width=4) (actual
>> time=0.043..90.016 rows=14850 loops=1)
>> -> Index Scan using uq_object_id_timestamp on table_a a
>> (cost=0.70..7038.49 rows=6913 width=8) (actual time=0.028..21.832
>> rows=14850 loops=1)
>> Index Cond: ((object_id = 13) AND (timestamp <
>> '2019-12-06'::timestamp with time zone) AND (timestamp >
>> '2019-12-03'::timestamp with time zone))
>> -> Index Only Scan using table_b_a_id on table_b b (cost=0.57..1.60
>> rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=14850)
>> Index Cond: (a_id = a.id)
>> Heap Fetches: 0
>> Planning time: 1908.550 ms
>> Execution time: 91.004 ms
>>
>> The same query on a similar parallel system takes 5ms for planing (PG
>> 9.4.).
>>
>> Is there a way to detect why the planing is taking this long?
>>
>> The database is a 9.6.1 with 32GB of shared_buffers, and 1GB of
>> maintanance_work_mem, and machine CPU is below 80% all the time.
>>
>> Regards,
>> Mladen Marinović
>>
>