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