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

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

Please don't top-post.

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

> This is speculation, but for me, it seems like the point "Allow common table expressions (CTEs) to be inlined into the outer 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