Re: BUG #16223: Performance regression between 11.6 and 12.1 in an SQL query with a recursive CTE based on function

From: Christian Schwaderer <schwaderer(at)ivocotec(dot)de>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Re: BUG #16223: Performance regression between 11.6 and 12.1 in an SQL query with a recursive CTE based on function
Date: 2020-01-27 12:46:01
Message-ID: 961e4c0f-bdd4-4733-4706-0b19d8a8559f@ivocotec.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

On 27.01.20 11:20, Daniel Gustafsson wrote:

>> On 27 Jan 2020, at 06:39, Christian Schwaderer <schwaderer(at)ivocotec(dot)de> wrote:

>> Just for clarification: While the expressions "significantly slower" and "performance regression" used in my bug report might seem like a tiny issue, this is a big deal right now for my project. A query duration increase from 4ms to 150ms is negligible in absolute numbers, but in my real-life case, this adds up! Some queries run 300 times slower in Postgres 12 than in 11. Imagine, for a big data-set, a query runs 2 seconds - which is still sort of acceptable. Now, it would be 600 seconds ~ 5 minutes. Such delays could break the whole system. So, ultimately, this issue prevents my project from upgrading to PostgreSQL 12 - which is sad.
> I can't reproduce the effects that you are seeing. The query runs in ~ 0.2ms
> in the latest 11 as well as the latest 12 release for me. Any difference in
> runtime is too insignificant to be considered with my unscientific testing
> process (running explain analyze N times and inspecting).
>
> You mention running in VMs and Docker etc, are the environments where you
> examine the results equivalent? Can you try running postgres without
> virtualization to see? Even 4ms seems a tad on the slow side for such a
> trivial query.

I only tested on those virtual environments mentioned (Virtual Box and
Docker) as they resemble my real life scenarios. Environments were
equivalent except for the PostgreSQL version. As you can see, I used
Docker images from the official PostgreSQL docker repo (
https://hub.docker.com/_/postgres ) and did nothing with them except
running the query.

Thanks for testing without virtualization! So, it seems we can narrow
down the issue to virtual environments. Strange enough, if you ask me.

--
Christian Schwaderer, Software-Entwickler/software developer
------------------------------------------------------------
ivocoTec GmbH
Entwicklungsabteilung/IT department

Postplatz 3
D-16761 Hennigsdorf

https://ivocotec.de
Telefon/phone +49 (0)3302 20 63 230

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message PG Bug reporting form 2020-01-27 14:55:53 BUG #16234: LDAP Query
Previous Message Helga Tschernitz 2020-01-27 10:24:25 Moderate Error in German help of pg_ctl