Re[4]: Postgresql planning time too high

I did some testing and the results are surprising.
I did 3 tests:

Test 1
Test 2
Test 3
Test conditions
SHOW geqo: "on"
SHOW geqo_threshold: "5"
SHOW geqo: "on"
SHOW geqo_threshold: "12"
SHOW geqo: "off"
Planning Time
43691.910 ms
5114.959 ms
7305.504 ms
Execution Time
4.002 ms
3.987 ms
5.034 ms
This are things that are way over my knowledge, I can only speculate 
about this: in the documentation from here 
<https://www.postgresql.org/docs/9.6/runtime-config-query.html#GUC-GEQO-THRESHOLD> 
geqo_threshold is defined as the number of joins after wich postgres 
will start to use the generic planner.
On my query there are  about 50 joins so test 1 and test 2 should both 
be done with the generic planner but the planning time of these tests 
sugest that this is not the case.
So I think test 1 is generic and test 2 and 3 are deterministic(test 3 
can be only deterministic as as setted this way the postgres server).
Anyway, in the end the deterministic planner is much more effective at 
planning this query that the generic one(test 3 is with generic planner 
turned off).





------ Original Message ------
From: "Pavel Stehule" <[email protected]>
To: "Sterpu Victor" <[email protected]>
Cc: "Fırat Güleç" <[email protected]>; "Pgsql Performance" 
<[email protected]>
Sent: 2019-11-22 2:59:11 PM
Subject: Re: Re[2]: Postgresql planning time too high

>
>
>pá 22. 11. 2019 v 12:46 odesílatel Sterpu Victor <[email protected]> 
>napsal:
>>No rows should be returned, DB is empty.
>>I'm testing now on a empty DB trying to find out how to improve this.
>>
>>In this query I have 3 joins like this:
>>
>>SELECT t1.id, t2.valid_from
>>FROM t1
>>JOIN t2 ON (t1.id_t1 = t1.id)
>>LEFT JOIN t3 ON (t3.id_t1 = t1.id AND t3.valid_from<t2.valid_from)
>>WHERE t3.id IS NULL
>>
>>If I delete these 3 joins than the planning time goes down from 5.482 
>>ms to 754.708 ms but I'm not sure why this context is so demanding on 
>>the planner.
>>I'm tryng now to make a materialized view that will allow me to stop 
>>using the syntax above.
>
>This query is little bit crazy - it has more than 40 joins - but 700ms 
>for planning is looks too much. Maybe your comp has slow CPU.
>
>Postgres has two planners - deterministic and genetic
>
>   https://www.postgresql.org/docs/9.1/geqo-pg-intro.html
>
>Probably slow plan is related to deterministic planner.
>
>
>>
>>I reattached the same files, they should be fine like this.
>>
>>
>>
>>
>>------ Original Message ------
>>From: "Fırat Güleç" <[email protected]>
>>To: "Sterpu Victor" <[email protected]>
>>Cc: [email protected]
>>Sent: 2019-11-22 1:35:15 PM
>>Subject: RE: Postgresql planning time too high
>>
>>>Hello Sterpu,
>>>
>>>
>>>
>>>First, please run vaccum for your Postgresql DB.
>>>
>>>
>>>
>>>No rows returned from your query. Could you double check your query 
>>>criteria.
>>>
>>>
>>>
>>>After that could you send explain analyze again .
>>>
>>>
>>>
>>>Regards,
>>>
>>>
>>>
>>>FIRAT GÜLEÇ
>>>Infrastructure & Database Operations Manager
>>>[email protected]
>>>
>>>
>>>
>>>M: 0 532 210 57 18
>>>İnönü Mh. Mimar Sinan Cd. No:3 Güzeller Org.San.Bölg. GEBZE / KOCAELİ
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>From: Sterpu Victor <[email protected]>
>>>Sent: Friday, November 22, 2019 2:21 PM
>>>To:[email protected]
>>>Subject: Postgresql planning time too high
>>>
>>>
>>>
>>>Hello
>>>
>>>
>>>
>>>I'm on a PostgreSQL 12.1 and I just restored a database from a 
>>>backup.
>>>
>>>When I run a query I get a big execution time: 5.482 ms
>>>
>>>After running EXPLAIN ANALYZE I can see that the "Planning Time: 
>>>5165.742 ms" and the "Execution Time: 6.244 ms".
>>>
>>>The database is new(no need to vacuum) and i'm the only one connected 
>>>to it. I use a single partition on the harddrive.
>>>
>>>I also tried this on a postgresql 9.5 and the result was the same.
>>>
>>>I'm not sure what to do to improve this situation.
>>>
>>>The query and the explain is attached.
>>>
>>>
>>>
>>>Thank you
>>>
>>>
>>>
>>>