Postgres Pro
Downloads
Home   >   mailing lists

Thread: Postgresql planning time too high

Postgresql planning time too high

From
"Sterpu Victor"
Date:
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

RE: Postgresql planning time too high

From
Fırat Güleç
Date:

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

Re[2]: Postgresql planning time too high

From
"Sterpu Victor"
Date:
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.

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

Re[2]: Postgresql planning time too high

From
"Sterpu Victor"
Date:
I did runned "VACCUM FULL" followed by "VACUUM" but no difference.

------ 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

RE: Re[2]: Postgresql planning time too high

From
Fırat Güleç
Date:

Could you run  VACCUM ANALYZE.

 

From: Sterpu Victor <victor@caido.ro>
Sent: Friday, November 22, 2019 2:46 PM
To: Fırat Güleç <firat.gulec@hepsijet.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re[2]: Postgresql planning time too high

 

I did runned "VACCUM FULL" followed by "VACUUM" but no difference.

 

------ 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

Re: Postgresql planning time too high

From
Luís Roberto Weck
Date:
Em 22/11/2019 08:46, Sterpu Victor escreveu:
I did runned "VACCUM FULL" followed by "VACUUM" but no difference.

------ 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

 

Have you run the ANALYZE command to update your DB statistics?
Attachment

Re[4]: Postgresql planning time too high

From
"Sterpu Victor"
Date:
This is interesting because "VACUUM ANALYZE" solved the problem on postgresql 12.1, the planning time was cut down from 5165.742 ms to 517 ms.
This is great but I didn't think to do this on postgresql 12.1 because I did the same thing on the production server(postgresql 9.5) and the problem was not solved there by this command on the 9.5.
I guess I should update the production server. 
Is there another way?

Thank you

------ Original Message ------
From: "Fırat Güleç" <firat.gulec@hepsijet.com>
To: "Sterpu Victor" <victor@caido.ro>
Sent: 2019-11-22 2:05:44 PM
Subject: RE: Re[2]: Postgresql planning time too high

Could you run  VACCUM ANALYZE.

 

From: Sterpu Victor <victor@caido.ro>
Sent: Friday, November 22, 2019 2:46 PM
To: Fırat Güleç <firat.gulec@hepsijet.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re[2]: Postgresql planning time too high

 

I did runned "VACCUM FULL" followed by "VACUUM" but no difference.

 

------ 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

Re[4]: Postgresql planning time too high

From
"Sterpu Victor"
Date:
I'm sorry, I messed up between a lot of queries ..... there is no difference after running "VACUUM ANALYZE".
I guess this was to be expected as the database was just restored from backup.

------ Original Message ------
From: "Fırat Güleç" <firat.gulec@hepsijet.com>
To: "Sterpu Victor" <victor@caido.ro>
Sent: 2019-11-22 2:05:44 PM
Subject: RE: Re[2]: Postgresql planning time too high

Could you run  VACCUM ANALYZE.

 

From: Sterpu Victor <victor@caido.ro>
Sent: Friday, November 22, 2019 2:46 PM
To: Fırat Güleç <firat.gulec@hepsijet.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re[2]: Postgresql planning time too high

 

I did runned "VACCUM FULL" followed by "VACUUM" but no difference.

 

------ 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

Re[3]: Postgresql planning time too high

From
"Sterpu Victor"
Date:
I finnished testing with the matterialized view and the result is much improved, planning time goes down from 5.482 ms to 1507.741 ms.
This is much better but I still don't understand why postgres is planning so much time as long the main table is empty(there are no records in table focg).


------ Original Message ------
From: "Sterpu Victor" <victor@caido.ro>
To: "Fırat Güleç" <firat.gulec@hepsijet.com>
Sent: 2019-11-22 1:44:51 PM
Subject: Re[2]: Postgresql planning time too high

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.

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

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

From
Pavel Stehule
Date:


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

Re[4]: Postgresql planning time too high

From
"Sterpu Victor"
Date:
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 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" <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

Re[4]: Postgresql planning time too high

From
"Sterpu Victor"
Date:
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

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

From
Pavel Stehule
Date:


pá 22. 11. 2019 v 15:06 odesílatel Sterpu Victor <victor@caido.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.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

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

From
Michael Lewis
Date:
As a matter of habit, I put all inner joins that may limit the result set as the first joins, then the left joins that have where conditions on them. I am not sure whether the optimizer sees that only those tables are needed to determine which rows will be in the end result and automatically prioritizes them as far as joins. With 40+ joins, I would want if this re-ordering of the declared joins may be significant.

If that doesn't help, then I would put all of those in a sub-query to break up the problem for the optimizer (OFFSET 0 being an optimization fence, though if this is an example of "simple" pagination then I assume but am not sure that OFFSET 20 would also be an optimization fence). Else, put all that in a CTE with MATERIALIZED keyword when on v12 and without on 9.5 since it did not exist yet and was default behavior then.

With an empty database, there are no statistics so perhaps the optimizer has too many plans that are very close in expected costs. I'd be curious if the planning time gets shorter once you have data, assuming default_statistics_target is left at the standard 100, or is not increased too hugely.

Attachment

Re: Postgresql planning time too high

From
Tomas Vondra
Date:
On Fri, Nov 22, 2019 at 11:44:51AM +0000, Sterpu Victor wrote:
>No rows should be returned, DB is empty.
>I'm testing now on a empty DB trying to find out how to improve this.
>

I'm a bit puzzled why you're doinf tests on an empty database, when in
production it'll certainly contain data. I guess you're assuming that
this way you isolate planning time, which should remain about the same
even with data loaded, but I'm not entirely sure that's true - all this
planning is done with no statistics (histograms, MCV lists, ...) and
maybe it's forcing the planner to do more work? I wouldn't be surprised
if having those stats would allow the planner to take some shortcuts,
cutting the plannnig time down.

Not to mention that we don't know if the plan is actually any good, for
all what we know it might take 10 years on real data, making the
planning duration irrelevant.


Let's put that aside, though. Let's assume it's because of expensive
join order planning. I don't think you have a lot of options, here,
unfortunately.

One option is to try reducing the planner options that determine how
much effort should be spent on join planning, e.g. join_collapse_limit
and geqo_threshold. If this is the root cause, you might even rewrite
the query to use optimal join order and set join_collapse_limit=1.
You'll have to play with it.

The other option is using CTEs with materialization, with the same
effect, i.e. prevention of optimization across CTEs, reducing the
total effort.

>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.
>
>I reattached the same files, they should be fine like this.
>

It'd be useful to have something others can use to reproduce the issue,
and investigate locally. SQL script that creates the whole schema and
runs the query, for example.

What I'd like to see is a perf profile from the planning, so that we can
see where exactly is the bottleneck. Maybe there actually is a bug that
makes it muych more expensive than it should be, in some corner case?


regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services