我最近從 PostgreSQL 的 V11 切換到 V14，我的一些 CTE 查詢過去需要幾毫秒，現在需要幾秒鐘。
重現此問題的最小查詢如下：
WITH MyTable (id) AS (
SELECT 1
UNION ALL SELECT 1
... repeat 2000 times
UNION ALL SELECT 1
)
SELECT count(*) from MyTable;
大約需要 3 秒，而在 PostgreSQL V11 中需要 80 毫秒。
在執行計劃中，我可以看到計劃時間需要很多秒，但我不知道為什么它比 PostgreSQL V11 慢
這是執行計劃：
Aggregate (cost=33.71..33.72 rows=1 width=8) (actual time=105.145..140.802 rows=1 loops=1)
-> Append (cost=0.00..28.89 rows=1926 width=0) (actual time=0.024..123.153 rows=1926 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.008..0.024 rows=1 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.008..0.024 rows=1 loops=1)
....
-> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.008..0.024 rows=1 loops=1)
Planning Time: 3066.151 ms
Execution Time: 148.261 ms
有誰知道為什么會這樣，如果有辦法解決這個問題？
編輯：使用 MATERIALIZED 關鍵字有助于將時間從 3 秒減少到 1 秒。這距離 PostgreSQL V11 中的 80 毫秒還很遠。使用 MATERIALIZED，執行計劃變為：
Aggregate (cost=72.23..72.24 rows=1 width=8) (actual time=137.779..171.381 rows=1 loops=1)
CTE mytable
-> Append (cost=0.00..28.89 rows=1926 width=4) (actual time=0.025..119.213 rows=1926 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.009..0.024 rows=1 loops=1)
...
-> Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.008..0.023 rows=1 loops=1)
-> CTE Scan on mytable (cost=0.00..38.52 rows=1926 width=0) (actual time=0.042..120.818 rows=1926 loops=1)
Planning Time: 1123.003 ms
Execution Time: 181.167 ms
uj5u.com熱心網友回復：
在我手中，物化的只需要250ms，所以我不知道為什么你發現它需要1s。我的硬體遠非壯觀。
無論如何，在較新的版本中，PostgreSQL 會更加努力地嘗試優化查詢，這需要更多時間。并非對軟體的所有改進都是免費的，必須做出權衡。
您可以嘗試將您的愚蠢查詢重寫為稍微不那么愚蠢的愚蠢查詢：
explain (analyze) WITH MyTable (id) AS (
SELECT * from (values
(1),
(1),
-- 2000 more
(1)
) foo
)
SELECT count(*) from MyTable;
在我的手中，這在 v14 中比你目前正在做的甚至在 v11 下要快得多。
如果選擇以下選項，您可以通過擺脫它來使其更清潔：
explain (analyze) WITH MyTable (id) AS (
values
(1),
(1),
--
(1)
)
SELECT count(*) from MyTable;
uj5u.com熱心網友回復：
在舊版本中，內部查詢僅在沒有任何其他優化的情況下實作。PostgreSQL 12 及更高版本嘗試對 CTE 進行全面優化。您可以嘗試使用MATERIALIZE子句來強制舊行為。
WITH MyTable (id) AS MATERIALIZED (
SELECT 1
UNION ALL SELECT 1
... repeat 2000 times
UNION ALL SELECT 1
)
SELECT count(*) from MyTable;
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/511369.html
標籤：PostgreSQL表现公共表表达式postgresql-14