Re: Re[4]: Postgresql planning time too high

pá 22. 11. 2019 v 15:06 odesílatel Sterpu Victor <victor(at)caido(dot)ro> napsal:

> The CPU is at about 7% when I run the query and 5% are occupied by
> postgresql.
> CPU is Xeon E3 1240 v6 3.7Gh - not very good, but postgres is not
> overloading it.
>
> Tests are done on windows 2016 server so the next step was to try and
> change the priority of all the postgresql procesess to realtime.
> This setting had some effect as the planning time went down from 5114.959
> ms to 2999.542 ms
>
> And then I changed a single line and the planning time went from 2999.542
> ms to 175.509 ms: I deleted the line "LIMIT 20 OFFSET 0"
> Changing this line in the final query is not an option, can I do something
> else to fix this?
>

it looks like planner bug. It's strange so LIMIT OFFSET 0 can increase 10x
planning time

Pavel

> Thank you.
>
>
> ------ Original Message ------
> From: "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>
> To: "Sterpu Victor" <victor(at)caido(dot)ro>
> Cc: "Fırat Güleç" <firat(dot)gulec(at)hepsijet(dot)com>; "Pgsql Performance" <
> pgsql-performance(at)lists(dot)postgresql(dot)org>
> 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 <victor(at)caido(dot)ro> 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ç" <firat(dot)gulec(at)hepsijet(dot)com>
>> To: "Sterpu Victor" <victor(at)caido(dot)ro>
>> Cc: pgsql-performance(at)lists(dot)postgresql(dot)org
>> 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
>> firat(dot)gulec(at)hepsijet(dot)com
>>
>>
>>
>> *M:* 0 532 210 57 18
>> İnönü Mh. Mimar Sinan Cd. No:3 Güzeller Org.San.Bölg. GEBZE / KOCAELİ
>>
>> [image: image.png]
>>
>>
>>
>>
>>
>>
>>
>> *From:* Sterpu Victor <victor(at)caido(dot)ro>
>> *Sent:* Friday, November 22, 2019 2:21 PM
>> *To:* pgsql-performance(at)lists(dot)postgresql(dot)org
>> *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
>>
>>
>>
>>