From: | Jean-Christophe Boggio <postgresql(at)thefreecat(dot)org> |
---|---|
To: | pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | Recursive query slow on strange conditions |
Date: | 2020-04-27 17:49:50 |
Message-ID: | 4a3b0246-f3ef-0767-62d1-6ceb4e2fc09c@thefreecat.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Hello,
I have a performance/regression problem on a complicated query (placed
into a function) when some tables are empty.
On Pg 11.6 the query takes 121ms
On Pg 12.2 it takes 11450ms
I first sent a message to the pgsql-bugs mailing list :
https://www.postgresql.org/message-id/16390-e9866af103d5a03a%40postgresql.org
But was redirected here. I was also told to post the actual problem, not
a simplified version (called "toy tables" by Tom Lane).
This is the first line of the plan :
Sort
(cost=812647915435832343755929914826593174290432.00..812652524250886044745873982078186103504896.00
rows=1843526021485360431505148111877616697344 width=1362) (actual
time=1.443..1.443 rows=0 loops=1)
The database is (full) vacuumed and analyzed.
Since the query plan is more than 560 lines and the query itself ~400
lines, I'm not sure it's efficient to post everything in an email.
I have rather prepared a .backup of the database in custom format (made
with PG 11.6), dropping all big unused tables so that it's ~500Kb. It is
available here :
http://freesofts.thefreecat.org/sage11demo_simple.backup
In order to test the problem, you can just call :
select * from findcontracts('{13}',7,true);
If it is more convenient to post everything in an email, just let me know.
Thanks for your help.
From | Date | Subject | |
---|---|---|---|
Next Message | Justin Pryzby | 2020-04-27 18:10:34 | Re: Recursive query slow on strange conditions |
Previous Message | Michael Lewis | 2020-04-25 20:40:15 | Re: The query plan get all columns but I'm using only one column. |