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

On 27.01.20 11:20, Daniel Gustafsson wrote:

>> On 27 Jan 2020, at 06:39, Christian Schwaderer <schwaderer@ivocotec.de> wrote:

>> Just for clarification: While the expressions "significantly slower" and "performance regression" used in my bug
reportmight seem like a tiny issue, this is a big deal right now for my project. A query duration increase from 4ms to
150msis negligible in absolute numbers, but in my real-life case, this adds up! Some queries run 300 times slower in
Postgres12 than in 11. Imagine, for a big data-set, a query runs 2 seconds - which is still sort of acceptable. Now, it
wouldbe 600 seconds ~ 5 minutes. Such delays could break the whole system. So, ultimately, this issue prevents my
projectfrom 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