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

pá 22. 11. 2019 v 12:46 odesílatel Sterpu Victor <victor(at)caido(dot)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(dot)gulec(at)hepsijet(dot)com>
> To: "Sterpu Victor" <victor(at)caido(dot)ro>
> Cc: pgsql-performance(at)lists(dot)postgresql(dot)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(dot)gulec(at)hepsijet(dot)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 <victor(at)caido(dot)ro>
> *Sent:* Friday, November 22, 2019 2:21 PM
> *To:* pgsql-performance(at)lists(dot)postgresql(dot)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
>
>
>
>