pá 22. 11. 2019 v 15:06 odesílatel Sterpu Victor <vic...@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.steh...@gmail.com> > To: "Sterpu Victor" <vic...@caido.ro> > Cc: "Fırat Güleç" <firat.gu...@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 <vic...@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 > > https://www.postgresql.org/docs/9.1/geqo-pg-intro.html > > Probably slow plan is related to deterministic planner. > > > >> I reattached the same files, they should be fine like this. >> >> >> >> >> ------ Original Message ------ >> From: "Fırat Güleç" <firat.gu...@hepsijet.com> >> To: "Sterpu Victor" <vic...@caido.ro> >> 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 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 >> >> >> >> *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 <vic...@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 >> >> >> >>
