基于函数值的递归 CTE 在 Postgres 12 上明显比在 11 上慢

跟进我关于 Postgres 12 中的一些查询比我认为的 11 慢的问题，我能够缩小问题的范围。似乎一个基于函数值的递归 CTE 是有问题的地方。

我能够分离出一个相当小的 SQL 查询，它在 Postgres 12.1 上比在 Postgres 11.6 上运行的时间要长得多，比如 Postgres 12.1 中的 ca 150ms 和 Postgres 11.6 中的 ca 4ms。我能够在各种系统上重现这种现象：在 VirtualBox 中的多个虚拟机上；通过 Docker 在两台不同的物理机器上。（有关 docker 命令，请参阅附录）。但是，很奇怪，我无法在https://www.db-fiddle.com/上重现它（在那里没有区别，两者都很快）。

现在进行查询。首先，我们创建这个简单的函数

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$;

然后进行实际查询

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

我想这实际上在这里并不重要。重要的一点可能是涉及多个 CTE，包括RECURSIVE一个。

我尝试了什么：

• 我没有尝试my_test_function过，即将值直接放入第一个 CTE。这样一来，就完全没有问题了。在 12 和 11 上运行同样快。
• 在 Postgres 12 上，我玩过MATERIALIZED，但看不到任何效果。查询仍然像以前一样慢。

我不知道这实际上是否可能是 Postgres 12 错误（或性能回归），或者我是否在这里遗漏了一些东西。

附录：我用于复现的 Docker 命令

一、拉取两个版本的镜像

docker pull postgres:12.1
docker pull postgres:11.6

现在，运行 Postgres 12

docker run -d --name my_postgres_12_container postgres:12.1

现在，执行查询

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\";
"

停止 Postgres 12 容器

docker stop my_postgres_12_container

启动 Postgres 11 进行比较

docker run -d --name my_postgres_11_container postgres:11.6

在 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\"

FROM \"somePeriods\" AS od

)

SELECT * FROM \"maxRecursiveEndDate\";
"