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

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

Please don't top-post.

> 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.

> This is speculation, but for me, it seems like the point "Allow common table expressions (CTEs) to be inlined into
theouter query (Andreas Karlsson, Andrew Gierth, David Fetter, Tom Lane)" mentioned in release notes
(https://www.postgresql.org/docs/release/12.0/)could be the problem here. 

Why do you think that particular feature is the culprit?  Your original email
already states that you've tried using AS NOT MATERIALIZED with no change on
the end result.

cheers ./daniel