Postgres Pro
Downloads
Home   >   mailing lists

Re[4]: Postgresql planning time too high - Mailing list pgsql-performance

From Sterpu Victor
Subject Re[4]: Postgresql planning time too high
Date
Msg-id emec9bf2e6-7a9a-4847-aaee-85bbe0577fa5@victor-pc
Whole thread Raw
In response to Re: Re[2]: Postgresql planning time too high  (Pavel Stehule <pavel.stehule@gmail.com>)
Responses Re: Re[4]: Postgresql planning time too high
List pgsql-performance
Tree view
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?

Thank you.


------ Original Message ------
From: "Pavel Stehule" <pavel.stehule@gmail.com>
To: "Sterpu Victor" <victor@caido.ro>
Cc: "Fırat Güleç" <firat.gulec@hepsijet.com>; "Pgsql Performance" <pgsql-performance@lists.postgresql.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@caido.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


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.gulec@hepsijet.com>
To: "Sterpu Victor" <victor@caido.ro>
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.gulec@hepsijet.com

 

M: 0 532 210 57 18 
İnönü Mh. Mimar Sinan Cd. No:3 Güzeller Org.San.Bölg. GEBZE / KOCAELİ

image.png

 

 

 

From: Sterpu Victor <victor@caido.ro>
Sent: Friday, November 22, 2019 2:21 PM
To: pgsql-performance@lists.postgresql.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

 

Attachment

pgsql-performance by date:

Previous
From: "Sterpu Victor"
Date:
Subject: Re[4]: Postgresql planning time too high
Next
From: Pavel Stehule
Date:
Subject: Re: Re[4]: Postgresql planning time too high