From: | "Sterpu Victor" <victor(at)caido(dot)ro> |
---|---|
To: | Fırat Güleç <firat(dot)gulec(at)hepsijet(dot)com> |
Cc: | pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | Re[2]: Postgresql planning time too high |
Date: | 2019-11-22 11:44:51 |
Message-ID: | em579730b3-be88-438e-9f14-4c4e203d0c89@victor-pc |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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.
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İ
>
>
>
>
>
>
>
>
>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
>
>
>
Attachment | Content-Type | Size |
---|---|---|
explain.txt | text/plain | 32.3 KB |
query.sql | application/octet-stream | 6.6 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Sterpu Victor | 2019-11-22 11:46:05 | Re[2]: Postgresql planning time too high |
Previous Message | Fırat Güleç | 2019-11-22 11:35:15 | RE: Postgresql planning time too high |