Postgresql planning time too high

2019-11-22 Thread Sterpu Victor 

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
"Limit  (cost=67.86..67.87 rows=1 width=3972) (actual time=0.033..0.033 rows=0 
loops=1)"
"  ->  Sort  (cost=67.86..67.87 rows=1 width=3972) (actual time=0.032..0.032 
rows=0 loops=1)"
"Sort Key: j1031101.validfrom DESC"
"Sort Method: quicksort  Memory: 25kB"
"->  Nested Loop Left Join  (cost=9.12..67.85 rows=1 width=3972) 
(actual time=0.022..0.022 rows=0 loops=1)"
"  Join Filter: (j1034965.id = j1031074.id_persontype)"
"  ->  Nested Loop Left Join  (cost=9.12..66.71 rows=1 width=3940) 
(actual time=0.022..0.022 rows=0 loops=1)"
"->  Nested Loop Left Join  (cost=8.98..66.14 rows=1 
width=3936) (actual time=0.021..0.021 rows=0 loops=1)"
"  ->  Nested Loop Left Join  (cost=8.84..64.50 rows=1 
width=3932) (actual time=0.021..0.021 rows=0 loops=1)"
"Join Filter: (j1033358.id = 
j1031074.id_drg_tip_cetatenie)"
"->  Nested Loop Left Join  (cost=8.84..63.41 
rows=1 width=3900) (actual time=0.021..0.021 rows=0 loops=1)"
"  ->  Nested Loop Left Join  
(cost=8.70..62.84 rows=1 width=3384) (actual time=0.021..0.021 rows=0 loops=1)"
"->  Nested Loop Left Join  
(cost=8.55..62.14 rows=1 width=3277) (actual time=0.021..0.021 rows=0 loops=1)"
"  ->  Nested Loop Left Join  
(cost=8.40..60.49 rows=1 width=3270) (actual time=0.021..0.021 rows=0 loops=1)"
"Join Filter: 
(j1031737.id = j1031074.id_focg_stare_externare)"
"->  Nested Loop Left 
Join  (cost=8.40..59.38 rows=1 width=3238) (actual time=0.021..0.021 rows=0 
loops=1)"
"  Join Filter: 
(j1031736.id = j1031074.id_focg_tip_externare)"
"  ->  Nested Loop 
Left Join  (cost=8.40..58.29 rows=1 width=3206) (actual time=0.020..0.020 
rows=0 loops=1)"
"Join 
Filter: (j1031100.id = j1031074.id_exceptie_bilet_internare)"
"->  Nested 
Loop Left Join  (cost=8.40..57.09 rows=1 width=3174) (actual time=0.020..0.020 
rows=0 loops=1)"
"  ->  
Nested Loop Left Join  (cost=8.27..56.53 rows=1 width=3142) (actual 
time=0.019..0.019 rows=0 loops=1)"
"   
 Join Filter: (j1031098.id = j1031074.id_focg_contract_modes)"
"   
 ->  Nested Loop Left Join  (cost=8.27..55.46 rows=1 width=3110) (actual 
time=0.019..0.019 rows=0 loops=1)"
"   
   ->  Nested Loop Left Join  (cost=8.13..54.89 rows=1 width=3078) (actual 
time=0.019..0.019 rows=0 loops=1)"
"   
 ->  Nested Loop Left Join  (cost=7.99..54.33 rows=1 width=3050) 
(actual time=0.019..0.019 rows=0 loops=1)"
"   
   ->  Nested Loop Left Join  (cost=7.71..51.35 rows=1 
width=3011) (actual time=0.019..0.019 rows=0 loops=1)"
"   
 Join Filter: (j1031095.id = 
j1031074.id_focg_sursa_internare)"
"   
 ->  Nested Loop Left Join  (cost=7.71..50.26 rows=1 
width=2979) (actual time=0.019..0.019 rows=0 loops=1)"
"   
   Join Filter: (j1031094.id = 
j1031074.id_cnp_mama)"
"   
   ->  Nested Loop Left Join  (cost=7.71..49.24 
rows=1 width=2969) (actual time=0.019..0.019 rows=0 loops=1)"
"   
 ->  Nest

Re[2]: Postgresql planning time too high

2019-11-22 Thread Sterpu Victor 

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_fromIf 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ç" 
To: "Sterpu Victor" 
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 
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


"Limit  (cost=67.86..67.87 rows=1 width=3972) (actual time=0.033..0.033 rows=0 
loops=1)"
"  ->  Sort  (cost=67.86..67.87 rows=1 width=3972) (actual time=0.032..0.032 
rows=0 loops=1)"
"Sort Key: j1031101.validfrom DESC"
"Sort Method: quicksort  Memory: 25kB"
"->  Nested Loop Left Join  (cost=9.12..67.85 rows=1 width=3972) 
(actual time=0.022..0.022 rows=0 loops=1)"
"  Join Filter: (j1034965.id = j1031074.id_persontype)"
"  ->  Nested Loop Left Join  (cost=9.12..66.71 rows=1 width=3940) 
(actual time=0.022..0.022 rows=0 loops=1)"
"->  Nested Loop Left Join  (cost=8.98..66.14 rows=1 
width=3936) (actual time=0.021..0.021 rows=0 loops=1)"
"  ->  Nested Loop Left Join  (cost=8.84..64.50 rows=1 
width=3932) (actual time=0.021..0.021 rows=0 loops=1)"
"Join Filter: (j1033358.id = 
j1031074.id_drg_tip_cetatenie)"
"->  Nested Loop Left Join  (cost=8.84..63.41 
rows=1 width=3900) (actual time=0.021..0.021 rows=0 loops=1)"
"  ->  Nested Loop Left Join  
(cost=8.70..62.84 rows=1 width=3384) (actual time=0.021..0.021 rows=0 loops=1)"
"->  Nested Loop Left Join  
(cost=8.55..62.14 rows=1 width=3277) (actual time=0.021..0.021 rows=0 loops=1)"
"  ->  Nested Loop Left Join  
(cost=8.40..60.49 rows=1 width=3270) (actual time=0.021..0.021 rows=0 loops=1)"
"Join Filter: 
(j1031737.id = j1031074.id_focg_stare_externare)"
"->  Nested Loop Left 
Join  (cost=8.40..59.38 rows=1 width=3238) (actual time=0.021..0.021 rows=0 
loops=1)"
"  Join Filter: 
(j1031736.id = j1031074.id_focg_tip_externare)"
"  ->  Nested Loop 
Left Join  (cost=8.40..58.29 rows=1 width=3206) (actual time=0.020..0.020 
rows=0 loops=1)"
"Join 
Filter: (j1031100.id = j1031074.id_exceptie_bilet_internare)"
"->  Nested 
Loop Left Join  (cost=8.40..57.09 rows=1 width=3174) (actual time=0.020..0.020 
rows=0 loops=1)"
"  ->  
Nested Loop Left Join  (cost=8.27..56.53 rows=1 width=3142) (actual 
time=0.019..0.019 rows=0 loops=1)"
"   
 Join Filter: (j1031098.id = j1031074.id_focg_contract_modes)"
"   
 ->  Nested Loop Left Join  (cost=8.27..55.46 rows=1 width=3110) (actual 
time=0.019..0.019 rows=0 loops=1)"
"   
   ->  Nested Loop Left Join  (cost=8.13..54.89 rows=1 width=3078) (actual

Re[2]: Postgresql planning time too high

2019-11-22 Thread Sterpu Victor 

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

-- Original Message --
From: "Fırat Güleç" 
To: "Sterpu Victor" 
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 
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

Re: Postgresql planning time too high

2019-11-22 Thread Luís Roberto Weck 

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

To: "Sterpu Victor" mailto:[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İ

image.png

*From:*Sterpu Victor mailto:[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


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

Re[4]: Postgresql planning time too high

2019-11-22 Thread Sterpu Victor 
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ç" 
To: "Sterpu Victor" 
Cc: [email protected]
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 
Sent: Friday, November 22, 2019 2:46 PM
To: Fırat Güleç 
Cc:[email protected]
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ç" 

To: "Sterpu Victor" 

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

Re[4]: Postgresql planning time too high

2019-11-22 Thread Sterpu Victor 
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ç" 
To: "Sterpu Victor" 
Cc: [email protected]
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 
Sent: Friday, November 22, 2019 2:46 PM
To: Fırat Güleç 
Cc:[email protected]
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ç" 

To: "Sterpu Victor" 

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

Re[3]: Postgresql planning time too high

2019-11-22 Thread Sterpu Victor 
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" 
To: "Fırat Güleç" 
Cc: [email protected]
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_fromIf 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ç" 
To: "Sterpu Victor" 
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 
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

RE: Postgresql planning time too high

2019-11-22 Thread Fırat Güleç 
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İ

[image: image.png]







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

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

2019-11-22 Thread Fırat Güleç 
Could you run  VACCUM ANALYZE.



*From:* Sterpu Victor 
*Sent:* Friday, November 22, 2019 2:46 PM
*To:* Fırat Güleç 
*Cc:* [email protected]
*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ç" 

To: "Sterpu Victor" 

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İ

[image: image.png]







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

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

2019-11-22 Thread Pavel Stehule 
pá 22. 11. 2019 v 12:46 odesílatel Sterpu Victor  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 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ç" 
> To: "Sterpu Victor" 
> 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İ
>
> [image: image.png]
>
>
>
>
>
>
>
> *From:* Sterpu Victor 
> *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
>
>
>
>

Re[4]: Postgresql planning time too high

2019-11-22 Thread Sterpu Victor 

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" 
To: "Sterpu Victor" 
Cc: "Fırat Güleç" ; "Pgsql Performance" 


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  
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_fromIf 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ç" 
To: "Sterpu Victor" 
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 
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

Re[4]: Postgresql planning time too high

2019-11-22 Thread Sterpu Victor 
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" 
To: "Sterpu Victor" 
Cc: "Fırat Güleç" ; "Pgsql Performance" 


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  
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_fromIf 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ç" 
To: "Sterpu Victor" 
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 
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

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

2019-11-22 Thread Pavel Stehule 
pá 22. 11. 2019 v 15:06 odesílatel Sterpu Victor  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" 
> To: "Sterpu Victor" 
> Cc: "Fırat Güleç" ; "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  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> 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ç" 
>> To: "Sterpu Victor" 
>> 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İ
>>
>> [image: image.png]
>>
>>
>>
>>
>>
>>
>>
>> *From:* Sterpu Victor 
>> *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
>>
>>
>>
>>

Hash Join over Nested Loop

2019-11-22 Thread Luís Roberto Weck 

Hey,

I'm trying to figure out why Postgres is choosing a Hash Join over a 
Nested Loop in this query:


SELECT T1.PesID, T1.PesNom, T1.PesValSal, T1.PesCPFCNPJ, T2.CarAti, 
T1.CarCod, T1.EmpCod,
   T2.CarFan, T1.PesDatAge, T1.PesCod, COALESCE( T3.PesDatAnt, DATE 
'00010101') AS PesDatAnt

  FROM ((public.Pessoa T1
    INNER JOIN public.Carteira T2 ON T2.EmpCod = T1.EmpCod AND 
T2.CarCod = T1.CarCod)
 LEFT JOIN  (SELECT MIN(COALESCE( T5.ConVenAnt, DATE 
'00010101')) AS PesDatAnt, T4.EmpCod, T4.CarCod, T4.ConPesCod AS ConPesCod

   FROM (public.Contrato T4
 LEFT JOIN  (SELECT MIN(ConParDatVen) 
AS ConVenAnt, EmpCod, CarCod, ConPesCod, ConSeq
   FROM 
public.ContratoParcela T5

  WHERE ConParAti = true
    AND ConParValSal > 0
GROUP BY EmpCod, CarCod, ConPesCod, ConSeq ) T5 ON T5.EmpCod    = 
T4.EmpCod    AND

                         T5.CarCod    = T4.CarCod    AND
                             T5.ConPesCod = T4.ConPesCod AND
                             T5.ConSeq    = T4.ConSeq)
  WHERE T4.ConAti = TRUE
GROUP BY T4.EmpCod, T4.CarCod, T4.ConPesCod ) T3 ON t3.EmpCod    = 
T1.EmpCod AND

          t3.CarCod    = T1.CarCod AND
              t3.ConPesCod = T1.PesCod)
 WHERE (T2.CarAti = true)
   AND (T1.EmpCod = 112)
   and (UPPER(T1.PesNom) like UPPER('%MARIA%'))
 ORDER BY T1.EmpCod, T1.CarCod, T1.PesCod

Here the Hash Join[1] plan takes ~700ms, and if I change the first LEFT 
JOIN to a LEFT JOIN LATERAL, forcing a nested loop, the query[2] runs in 
3ms.


[1] https://explain.depesz.com/s/8IL3
[2] https://explain.depesz.com/s/f8Q9

Re: Hash Join over Nested Loop

2019-11-22 Thread Luís Roberto Weck 

Hey,

I'm trying to figure out why Postgres is choosing a Hash Join over a 
Nested Loop in this query:


SELECT T1.PesID, T1.PesNom, T1.PesValSal, T1.PesCPFCNPJ, T2.CarAti, 
T1.CarCod, T1.EmpCod,
   T2.CarFan, T1.PesDatAge, T1.PesCod, COALESCE( T3.PesDatAnt, 
DATE '00010101') AS PesDatAnt

  FROM ((public.Pessoa T1
    INNER JOIN public.Carteira T2 ON T2.EmpCod = T1.EmpCod AND 
T2.CarCod = T1.CarCod)
 LEFT JOIN  (SELECT MIN(COALESCE( T5.ConVenAnt, DATE 
'00010101')) AS PesDatAnt, T4.EmpCod, T4.CarCod, T4.ConPesCod AS 
ConPesCod

   FROM (public.Contrato T4
 LEFT JOIN  (SELECT MIN(ConParDatVen) 
AS ConVenAnt, EmpCod, CarCod, ConPesCod, ConSeq
   FROM 
public.ContratoParcela T5

  WHERE ConParAti = true
    AND ConParValSal > 0
GROUP BY EmpCod, CarCod, ConPesCod, ConSeq ) T5 ON T5.EmpCod    = 
T4.EmpCod    AND

                             T5.CarCod    = T4.CarCod    AND
                                 T5.ConPesCod = T4.ConPesCod AND
                                 T5.ConSeq    = T4.ConSeq)
  WHERE T4.ConAti = TRUE
GROUP BY T4.EmpCod, T4.CarCod, T4.ConPesCod ) T3 ON t3.EmpCod    = 
T1.EmpCod AND

              t3.CarCod    = T1.CarCod AND
                  t3.ConPesCod = T1.PesCod)
 WHERE (T2.CarAti = true)
   AND (T1.EmpCod = 112)
   and (UPPER(T1.PesNom) like UPPER('%MARIA%'))
 ORDER BY T1.EmpCod, T1.CarCod, T1.PesCod

Here the Hash Join[1] plan takes ~700ms, and if I change the first 
LEFT JOIN to a LEFT JOIN LATERAL, forcing a nested loop, the query[2] 
runs in 3ms.


[1] https://explain.depesz.com/s/8IL3
[2] https://explain.depesz.com/s/f8Q9 


PostgreSQL version is 11.5, I have run analyze on all the tables.

PG settings:

name   |setting  |unit|
---|-||
autovacuum |on   |    |
default_statistics_target  |250  |    |
effective_cache_size   |983040   |8kB |
effective_io_concurrency   |200  |    |
max_parallel_workers   |6    |    |
max_parallel_workers_per_gather|3    |    |
random_page_cost   |1.1  |    |
work_mem   |51200    |kB  |

Re: Hash Join over Nested Loop

2019-11-22 Thread Pavel Stehule 
pá 22. 11. 2019 v 18:37 odesílatel Luís Roberto Weck <
[email protected]> napsal:

> Hey,
>
> I'm trying to figure out why Postgres is choosing a Hash Join over a
> Nested Loop in this query:
>
> SELECT T1.PesID, T1.PesNom, T1.PesValSal, T1.PesCPFCNPJ, T2.CarAti,
> T1.CarCod, T1.EmpCod,
>T2.CarFan, T1.PesDatAge, T1.PesCod, COALESCE( T3.PesDatAnt, DATE
> '00010101') AS PesDatAnt
>   FROM ((public.Pessoa T1
> INNER JOIN public.Carteira T2 ON T2.EmpCod = T1.EmpCod AND
> T2.CarCod = T1.CarCod)
>  LEFT JOIN  (SELECT MIN(COALESCE( T5.ConVenAnt, DATE
> '00010101')) AS PesDatAnt, T4.EmpCod, T4.CarCod, T4.ConPesCod AS ConPesCod
>FROM (public.Contrato T4
>  LEFT JOIN  (SELECT MIN(ConParDatVen) AS
> ConVenAnt, EmpCod, CarCod, ConPesCod, ConSeq
>FROM public.ContratoParcela
> T5
>   WHERE ConParAti = true
> AND ConParValSal > 0
>   GROUP BY EmpCod, CarCod,
> ConPesCod, ConSeq ) T5 ON T5.EmpCod= T4.EmpCodAND
>
>  T5.CarCod= T4.CarCodAND
>
>  T5.ConPesCod = T4.ConPesCod AND
>
>  T5.ConSeq= T4.ConSeq)
>   WHERE T4.ConAti = TRUE
>   GROUP BY T4.EmpCod, T4.CarCod, T4.ConPesCod )
> T3 ON t3.EmpCod= T1.EmpCod AND
>
>   t3.CarCod= T1.CarCod AND
>
>   t3.ConPesCod = T1.PesCod)
>  WHERE (T2.CarAti = true)
>AND (T1.EmpCod = 112)
>and (UPPER(T1.PesNom) like UPPER('%MARIA%'))
>  ORDER BY T1.EmpCod, T1.CarCod, T1.PesCod
>
> Here the Hash Join[1] plan takes ~700ms, and if I change the first LEFT
> JOIN to a LEFT JOIN LATERAL, forcing a nested loop, the query[2] runs in
> 3ms.
>
> [1] https://explain.depesz.com/s/8IL3
> [2] https://explain.depesz.com/s/f8Q9
>
>
Maybe I am wrong, but probably you have to do more than just change LEFT
JOIN to LATERAL JOIN. Lateral join is based on correlated subquery - so you
had to push some predicates to subquery - and then the query can be much
more effective.

Regards

Pavel





> PostgreSQL version is 11.5, I have run analyze on all the tables.
>
> PG settings:
>
> name   |setting  |unit|
> ---|-||
> autovacuum |on   ||
> default_statistics_target  |250  ||
> effective_cache_size   |983040   |8kB |
> effective_io_concurrency   |200  ||
> max_parallel_workers   |6||
> max_parallel_workers_per_gather|3||
> random_page_cost   |1.1  ||
> work_mem   |51200|kB  |
>

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

2019-11-22 Thread Michael Lewis 
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.

>

Re: Hash Join over Nested Loop

2019-11-22 Thread Luís Roberto Weck 


Em 22/11/2019 14:55, Pavel Stehule escreveu:



pá 22. 11. 2019 v 18:37 odesílatel Luís Roberto Weck 
mailto:[email protected]>> napsal:



Hey,

I'm trying to figure out why Postgres is choosing a Hash Join
over a Nested Loop in this query:

SELECT T1.PesID, T1.PesNom, T1.PesValSal, T1.PesCPFCNPJ,
T2.CarAti, T1.CarCod, T1.EmpCod,
   T2.CarFan, T1.PesDatAge, T1.PesCod, COALESCE(
T3.PesDatAnt, DATE '00010101') AS PesDatAnt
  FROM ((public.Pessoa T1
    INNER JOIN public.Carteira T2 ON T2.EmpCod =
T1.EmpCod AND T2.CarCod = T1.CarCod)
 LEFT JOIN  (SELECT MIN(COALESCE( T5.ConVenAnt, DATE
'00010101')) AS PesDatAnt, T4.EmpCod, T4.CarCod, T4.ConPesCod AS
ConPesCod
   FROM (public.Contrato T4
 LEFT JOIN (SELECT
MIN(ConParDatVen) AS ConVenAnt, EmpCod, CarCod, ConPesCod, ConSeq
FROM public.ContratoParcela T5
WHERE ConParAti = true
AND ConParValSal > 0
GROUP BY EmpCod, CarCod, ConPesCod, ConSeq ) T5 ON T5.EmpCod    =
T4.EmpCod    AND
T5.CarCod    = T4.CarCod    AND
T5.ConPesCod = T4.ConPesCod AND
T5.ConSeq    = T4.ConSeq)
  WHERE T4.ConAti = TRUE
GROUP BY T4.EmpCod, T4.CarCod, T4.ConPesCod ) T3 ON t3.EmpCod   
= T1.EmpCod AND
                      t3.CarCod    = T1.CarCod AND
                          t3.ConPesCod = T1.PesCod)
 WHERE (T2.CarAti = true)
   AND (T1.EmpCod = 112)
   and (UPPER(T1.PesNom) like UPPER('%MARIA%'))
 ORDER BY T1.EmpCod, T1.CarCod, T1.PesCod

Here the Hash Join[1] plan takes ~700ms, and if I change the
first LEFT JOIN to a LEFT JOIN LATERAL, forcing a nested loop,
the query[2] runs in 3ms.

[1] https://explain.depesz.com/s/8IL3
[2] https://explain.depesz.com/s/f8Q9



Maybe I am wrong, but probably you have to do more than just change 
LEFT JOIN to LATERAL JOIN. Lateral join is based on correlated 
subquery - so you had to push some predicates to subquery - and then 
the query can be much more effective.


Regards

Pavel





PostgreSQL version is 11.5, I have run analyze on all the tables.

PG settings:

name   |setting  |unit|
---|-||
autovacuum |on   |    |
default_statistics_target  |250  |    |
effective_cache_size   |983040   |8kB |
effective_io_concurrency   |200  |    |
max_parallel_workers   |6    |    |
max_parallel_workers_per_gather|3    |    |
random_page_cost   |1.1  |    |
work_mem   |51200    |kB  |



I'm sorry, I am not sure I understood.

This is the altered query:

SELECT T1.PesID, T1.PesNom, T1.PesValSal, T1.PesCPFCNPJ, T2.CarAti, 
T1.CarCod, T1.EmpCod, T2.CarFan, T1.PesDatAge, T1.PesCod,

   COALESCE( T3.PesDatAnt, DATE '00010101') AS PesDatAnt
  FROM ((public.Pessoa T1
    INNER JOIN public.Carteira T2 ON T2.EmpCod = T1.EmpCod AND 
T2.CarCod = T1.CarCod)
 LEFT JOIN *LATERAL *(SELECT MIN(COALESCE( T5.ConVenAnt, 
DATE '00010101')) AS PesDatAnt, T4.EmpCod, T4.CarCod, T4.ConPesCod AS 
ConPesCod

  FROM (public.Contrato T4
    LEFT JOIN  (SELECT 
MIN(ConParDatVen) AS ConVenAnt, EmpCod, CarCod, ConPesCod, ConSeq
 FROM 
public.ContratoParcela T5

    WHERE ConParAti = true
  and ConParValSal > 0

    GROUP BY EmpCod, 
CarCod, ConPesCod, ConSeq ) T5 ON  T5.EmpCod = T4.EmpCod AND T5.CarCod = 
T4.CarCod AND T5.ConPesCod = T4.ConPesCod AND T5.ConSeq = T4.ConSeq)

 WHERE T4.ConAti = TRUE
*AND t4.EmpCod = T1.EmpCod AND t4.CarCod = T1.CarCod AND t4.ConPesCod = 
T1.PesCod*
 GROUP BY T4.EmpCod, T4.CarCod, 
T4.ConPesCod ) T3 ON *TRUE ) --ON t3.EmpCod = T1.EmpCod AND t3.CarCod = 
T1.CarCod AND t3.ConPesCod = T1.PesCod) *

 WHERE (T2.CarAti = true)
   AND (T1.EmpCod = 112)
   and (UPPER(T1.PesNom) like UPPER('%MARIA%'))
 ORDER BY T1.EmpCod, T1.CarCod, T1.PesCod

In bold are the changes I've made to the query. I am sure PostgreSQL is 
able to push it down, since it is much faster now.The problem I have is 
that this is a query generated by an ORM, So I can't change it.


I would like to understand why wasn't Postgres able to optimize it to a 
nested loop. Is there something I can do with the statistics?


Thanks!!

Re: Hash Join over Nested Loop

2019-11-22 Thread Pavel Stehule 
pá 22. 11. 2019 v 19:42 odesílatel Luís Roberto Weck <
[email protected]> napsal:

> Em 22/11/2019 14:55, Pavel Stehule escreveu:
>
>
>
> pá 22. 11. 2019 v 18:37 odesílatel Luís Roberto Weck <
> [email protected]> napsal:
>
>> Hey,
>>
>> I'm trying to figure out why Postgres is choosing a Hash Join over a
>> Nested Loop in this query:
>>
>> SELECT T1.PesID, T1.PesNom, T1.PesValSal, T1.PesCPFCNPJ, T2.CarAti,
>> T1.CarCod, T1.EmpCod,
>>T2.CarFan, T1.PesDatAge, T1.PesCod, COALESCE( T3.PesDatAnt, DATE
>> '00010101') AS PesDatAnt
>>   FROM ((public.Pessoa T1
>> INNER JOIN public.Carteira T2 ON T2.EmpCod = T1.EmpCod AND
>> T2.CarCod = T1.CarCod)
>>  LEFT JOIN  (SELECT MIN(COALESCE( T5.ConVenAnt, DATE
>> '00010101')) AS PesDatAnt, T4.EmpCod, T4.CarCod, T4.ConPesCod AS ConPesCod
>>FROM (public.Contrato T4
>>  LEFT JOIN  (SELECT MIN(ConParDatVen) AS
>> ConVenAnt, EmpCod, CarCod, ConPesCod, ConSeq
>>FROM
>> public.ContratoParcela T5
>>   WHERE ConParAti = true
>> AND ConParValSal > 0
>>   GROUP BY EmpCod, CarCod,
>> ConPesCod, ConSeq ) T5 ON T5.EmpCod= T4.EmpCodAND
>>
>>  T5.CarCod= T4.CarCodAND
>>
>>  T5.ConPesCod = T4.ConPesCod AND
>>
>>  T5.ConSeq= T4.ConSeq)
>>   WHERE T4.ConAti = TRUE
>>   GROUP BY T4.EmpCod, T4.CarCod, T4.ConPesCod )
>> T3 ON t3.EmpCod= T1.EmpCod AND
>>
>>   t3.CarCod= T1.CarCod AND
>>
>>   t3.ConPesCod = T1.PesCod)
>>  WHERE (T2.CarAti = true)
>>AND (T1.EmpCod = 112)
>>and (UPPER(T1.PesNom) like UPPER('%MARIA%'))
>>  ORDER BY T1.EmpCod, T1.CarCod, T1.PesCod
>>
>> Here the Hash Join[1] plan takes ~700ms, and if I change the first LEFT
>> JOIN to a LEFT JOIN LATERAL, forcing a nested loop, the query[2] runs in
>> 3ms.
>>
>> [1] https://explain.depesz.com/s/8IL3
>> [2] https://explain.depesz.com/s/f8Q9
>>
>>
> Maybe I am wrong, but probably you have to do more than just change LEFT
> JOIN to LATERAL JOIN. Lateral join is based on correlated subquery - so you
> had to push some predicates to subquery - and then the query can be much
> more effective.
>
> Regards
>
> Pavel
>
>
>
>
>
>> PostgreSQL version is 11.5, I have run analyze on all the tables.
>>
>> PG settings:
>>
>> name   |setting  |unit|
>> ---|-||
>> autovacuum |on   ||
>> default_statistics_target  |250  ||
>> effective_cache_size   |983040   |8kB |
>> effective_io_concurrency   |200  ||
>> max_parallel_workers   |6||
>> max_parallel_workers_per_gather|3||
>> random_page_cost   |1.1  ||
>> work_mem   |51200|kB  |
>>
>
> I'm sorry, I am not sure I understood.
>
> This is the altered query:
>
> SELECT T1.PesID, T1.PesNom, T1.PesValSal, T1.PesCPFCNPJ, T2.CarAti,
> T1.CarCod, T1.EmpCod, T2.CarFan, T1.PesDatAge, T1.PesCod,
>COALESCE( T3.PesDatAnt, DATE '00010101') AS PesDatAnt
>   FROM ((public.Pessoa T1
> INNER JOIN public.Carteira T2 ON T2.EmpCod = T1.EmpCod AND
> T2.CarCod = T1.CarCod)
>  LEFT JOIN *LATERAL *(SELECT MIN(COALESCE( T5.ConVenAnt, DATE
> '00010101')) AS PesDatAnt, T4.EmpCod, T4.CarCod, T4.ConPesCod AS ConPesCod
>   FROM (public.Contrato T4
> LEFT JOIN  (SELECT
> MIN(ConParDatVen) AS ConVenAnt, EmpCod, CarCod, ConPesCod, ConSeq
>  FROM
> public.ContratoParcela T5
> WHERE ConParAti = true
>   and ConParValSal > 0
>
> GROUP BY EmpCod,
> CarCod, ConPesCod, ConSeq ) T5 ON  T5.EmpCod = T4.EmpCod AND T5.CarCod =
> T4.CarCod AND T5.ConPesCod = T4.ConPesCod AND T5.ConSeq = T4.ConSeq)
>  WHERE T4.ConAti = TRUE
>*AND t4.EmpCod = T1.EmpCod AND
> t4.CarCod = T1.CarCod AND t4.ConPesCod = T1.PesCod*
>  GROUP BY T4.EmpCod, T4.CarCod,
> T4.ConPesCod ) T3 ON *TRUE ) --ON t3.EmpCod = T1.EmpCod AND t3.CarCod =
> T1.CarCod AND t3.ConPesCod = T1.PesCod) *
>  WHERE (T2.CarAti = true)
>AND (T1.EmpCod = 112)
>and (UPPER(T1.PesNom) like UPPER('%MARIA%'))
>  ORDER BY T1.EmpCod, T1.CarCod, T1.PesCod
>
> In bold are the changes I've made to the query. I am sure PostgreSQL is
> able to push it down, since it is much faster now. The problem I have is
> that this is a query generated by an ORM, S

Re: Postgresql planning time too high

2019-11-22 Thread Tomas Vondra 

On Fri, Nov 22, 2019 at 11:44:51AM +, 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_fromIf 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