基於函式值的遞歸 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\";
"

感謝 pgbugs 郵件列表中的幫助人員，事實證明，在 PostgreSQL 12 中預設打開即時編譯（一些有用的背景資訊可在此處找到）是我的問題。

執行我的查詢SET jit = off;解決了這個問題：沒有它，我的查詢執行得很快。

引用自：https://dba.stackexchange.com/questions/257759