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

Dear all,

I want to add to the bug report below that I have also tested with
PostgreSQL 12.0 - same problem, as behavior as 12.1. So, the regression
seems to have happened between 11.x and 12.0

Do you need any more information? I had assumed that providing docker
commands would be enough, but if I can be of further assistance, please
let me know.

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.

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.
So, I would be thankful if any of these four gentlemen could have a look
at it. (Or someone else, of course.)

Best regards
Christian

On 22.01.20 07:02, PG Bug reporting form wrote:
> The following bug has been logged on the website:
>
> Bug reference: 16223
> Logged by: ChristianS
> Email address: schwaderer(at)ivocotec(dot)de
> PostgreSQL version: 12.1
> Operating system: Ubuntu 18.04
> Description:
>
> I have a query that runs significantly slower in Postgres 12.1 than it does
> in Postgres 11.6.
>
> (I have asked on dba.stackexchange
> https://dba.stackexchange.com/questions/257759/recursive-cte-based-on-function-values-significantly-slower-on-postgres-12-than
> - hoping that someone would jump in and tell me why this is my fault and why
> it has nothing to do with PostgreSQL itself. However, since there is still
> no reaction after two days, I can be somewhat sure that it's not a very
> obvious mistake of mine and maybe something you might want to
> investigate.)
>
>
> First, we create this simple function
>
>
> CREATE OR REPLACE FUNCTION public.my_test_function()
> RETURNS SETOF record
> LANGUAGE sql
> IMMUTABLE SECURITY DEFINER
> AS $function$
>
> SELECT
> 1::integer AS id,
> '2019-11-20'::date AS "startDate",
> '2020-01-01'::date AS "endDate"
>
> $function$;
>
>
> Then for the actual query
>
>
> WITH "somePeriods" AS (
> SELECT * FROM my_test_function() AS
> f(id integer, "startDate" date, "endDate" date)
> ),
>
> "maxRecursiveEndDate" AS (
>
> SELECT "startDate", "endDate", id,
> (
> WITH RECURSIVE prep("startDateParam", "endDateParam") AS (
>
> SELECT "startDate","endDate" FROM "somePeriods" WHERE id = od.id
> UNION
> SELECT "startDate","endDate" FROM "somePeriods", prep
> WHERE
> "startDate" <= ("endDateParam" + '1 day'::interval ) AND ("endDateParam"
> + '1 day'::interval ) <= "endDate"
> )
> SELECT max("endDateParam") FROM prep
> ) AS "endDateNew"
>
> FROM "somePeriods" AS od
>
> )
>
> SELECT * FROM "maxRecursiveEndDate";
>
>
> What this actually does it not so important here, I guess. The important
> point is: It runs very fast on Postgres 11.6 (like ca 4ms) and much slower
> on PostgreSQL 12.1 (ca 150ms). The output of EXPLAIN ANALYZE did not give me
> further hints.
> A crucial point might or might be not, that are multiple CTEs involved,
> including a RECURSIVE one. However, that's speculation.
>
> What I tried out:
> - I did try without my_test_function, i.e. putting the values directly into
> the first CTE without using a function. This way, there was no problem at
> all. Like this, it runs equally fast both on 12.1 and on 11.6.
> - On Postgres 12, I played around with MATERIALIZED, but could not see any
> effect. The query still runs as slow as before.
>
> Note on reproducibility:
> I was able to reproduce the phenomenon on various systems: on multiple VMs
> in VirtualBox; via Docker on two different physical machines. (See below for
> Docker commands.) However, strange enough, I cannot reproduce it on
> https://www.db-fiddle.com/ (no difference to be seen there, both are
> fast).
>
>
> Docker commands:
>
> # First, pull images of both versions
>
> docker pull postgres:12.1
> docker pull postgres:11.6
>
> # Now, run Postgres 12
>
> docker run -d --name my_postgres_12_container postgres:12.1
>
> # Now, execute the query
>
> docker exec my_postgres_12_container psql -U postgres -c "
>
> CREATE OR REPLACE FUNCTION public.my_test_function()
> RETURNS SETOF record
> LANGUAGE sql
> IMMUTABLE SECURITY DEFINER
> AS \$function\$
>
> SELECT
> 1::integer AS id,
> '2019-11-20'::date AS \"startDate\",
> '2020-01-01'::date AS \"endDate\"
>
> \$function\$;
>
> EXPLAIN ANALYZE WITH \"somePeriods\" AS (
> SELECT * FROM my_test_function() AS
> f(id integer, \"startDate\" date, \"endDate\" date)
> ),
>
> \"maxRecursiveEndDate\" AS (
>
> SELECT \"startDate\", \"endDate\", id,
> (
> WITH RECURSIVE prep(\"startDateParam\", \"endDateParam\") AS (
>
> SELECT \"startDate\",\"endDate\" FROM \"somePeriods\" WHERE id = od.id
> UNION
> SELECT \"startDate\",\"endDate\" FROM \"somePeriods\", prep
> WHERE
> \"startDate\" <= (\"endDateParam\" + '1 day'::interval ) AND
> (\"endDateParam\" + '1 day'::interval ) <= \"endDate\"
> )
> SELECT max(\"endDateParam\") FROM prep
> ) AS \"endDateNew\"
>
> FROM \"somePeriods\" AS od
>
> )
>
> SELECT * FROM \"maxRecursiveEndDate\";
> "
>
> # Stop the Postgres 12 container
>
> docker stop my_postgres_12_container
>
> # Start Postgres 11 for comparison
>
> docker run -d --name my_postgres_11_container postgres:11.6
>
> # Execute the query in Postgres 11
>
> docker exec my_postgres_11_container psql -U postgres -c "
>
> CREATE OR REPLACE FUNCTION public.my_test_function()
> RETURNS SETOF record
> LANGUAGE sql
> IMMUTABLE SECURITY DEFINER
> AS \$function\$
>
> SELECT
> 1::integer AS id,
> '2019-11-20'::date AS \"startDate\",
> '2020-01-01'::date AS \"endDate\"
>
> \$function\$;
>
> EXPLAIN ANALYZE WITH \"somePeriods\" AS (
> SELECT * FROM my_test_function() AS
> f(id integer, \"startDate\" date, \"endDate\" date)
> ),
>
> \"maxRecursiveEndDate\" AS (
>
> SELECT \"startDate\", \"endDate\", id,
> (
> WITH RECURSIVE prep(\"startDateParam\", \"endDateParam\") AS (
>
> SELECT \"startDate\",\"endDate\" FROM \"somePeriods\" WHERE id = od.id
> UNION
> SELECT \"startDate\",\"endDate\" FROM \"somePeriods\", prep
> WHERE
> \"startDate\" <= (\"endDateParam\" + '1 day'::interval ) AND
> (\"endDateParam\" + '1 day'::interval ) <= \"endDate\"
> )
> SELECT max(\"endDateParam\") FROM prep
> ) AS \"endDateNew\"
>
--
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