Re: Postgresql planning time too high

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

Re: Hash Join over Nested Loop

pá 22. 11. 2019 v 19:42 odesílatel Luís Roberto Weck < luisrobe...@siscobra.com.br> napsal: > Em 22/11/2019 14:55, Pavel Stehule escreveu: > > > > pá 22. 11. 2019 v 18:37 odesílatel Luís Roberto Weck < > luisrobe...@siscobra.com.br> napsal: > >> Hey, >> >> I'm trying to figure out why Postgres is

Re: Hash Join over Nested Loop

Em 22/11/2019 14:55, Pavel Stehule escreveu: pá 22. 11. 2019 v 18:37 odesílatel Luís Roberto Weck mailto:luisrobe...@siscobra.com.br>> 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.PesN

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

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 priori

Re: Hash Join over Nested Loop

pá 22. 11. 2019 v 18:37 odesílatel Luís Roberto Weck < luisrobe...@siscobra.com.br> 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, >

Re: Hash Join over Nested Loop

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   F

Hash Join over Nested Loop

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   F

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

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

Re[4]: Postgresql planning time too high

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 realtim

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 T

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

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:* pgsql-performance@lists.postgresql.org *Subject:* Re[2]: Postgresql planning time too high I did runned "VACCUM FULL" followed by "VACUUM" but no difference. -- Orig

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.gu...@hepsijet.com

Re[3]: Postgresql planning time too high

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

Re[4]: Postgresql planning time too high

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: pgsql-performance@lists.postgr

Re[4]: Postgresql planning time too high

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

Re: Postgresql planning time too high

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:vic...@caido.ro>> Cc: pgsql-performance@lists.postgresql.org

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: pgsql-performance@lists.postgresql.org Sent: 2019-11-22 1:35:15 PM Subject: RE: Postgresql planning time too high Hello Sterpu, First, please run va

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 th

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 th