Recursive query slow on strange conditions

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.

Responses

Browse pgsql-performance by date

  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.