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

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

Thanks to the helpful guys at the pgbugs mailing list, it turned out that Just-in-time-compilation (some helpful background information to be found here) being turned on by default in PostgreSQL 12 had been my problem.

Running my query with SET jit = off; solved the issue: Without it, my query runs fast as it should.

StackExchange Database Administrators Q#257759, answer score: 7