Recursive CTE based on function values significantly slower on Postgres 12 than on 11

Posted on

Question :

Following up on my question about some queries in Postgres 12 being slower than in 11 I think, I was able to narrow down the problem. It seems like one recursive CTE based on function values is the problematic spot.

I was able to isolate a rather smallish SQL query which runs significantly longer on Postgres 12.1 than on Postgres 11.6, like ca 150ms in Postgres 12.1 vs ca 4ms in Postgres 11.6. I was able to reproduce the phenomenon on various systems: on multiple VMs in VirtualBox; via Docker on two different physical machines. (See appendix 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).

Now for the query.
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 probably that there are multiple CTEs involved, including a RECURSIVE one.

What I tried:

  • I did try without my_test_function, i.e. putting the values directly into the first CTE. This way, there was no problem at all. Runs equally fast on 12 and on 11.
  • On Postgres 12, I played around with MATERIALIZED, but could see no effect. The query still runs as slow as before.

I don’t know whether this could actually be a Postgres 12 bug (or performance regression) or whether I’m missing something here.

Appendix: Docker commands I used for reproducing

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"",

Answer :

Leave a Reply

Your email address will not be published. Required fields are marked *