排除目前行會削弱視窗函式的性能
我正在使用客戶的數據庫，其中有代表使用者實例、活動（名為 ActivityRecords 的表）和相應命名表中的 ActivityUsers 的行，其中 ActivityUsers 的實例代表特定使用者對特定活動實例的參與。
對於 ActivityUsers 中的每條記錄（使用者的參與已完成並確定為成功或失敗狀態），我正在嘗試收集有關有多少早期記錄具有成功和失敗狀態
('BOOKED', 'COLLECTED', 'DONE', 'CANCELED')的數據，這些記錄（行）按活動分組類型，使用者角色，或兩者兼而有之，我想及時累積，使用 ActivityUser.Id 作為時間代理。我將使用結果數據來訓練預測模型，根據活動類型和使用者角色預測成功或失敗機率。由於我不想使用我試圖預測的行的成功狀態，只使用以前的記錄，我使用
ROWS UNBOUNDED PRECEDING EXCLUDE CURRENT ROW. 如果我忽略它，我計算的總和和計數幾乎是正確的，如果目前行的成功值為 1，則除以 1。（ROWS UNBOUNDED PRECEDING是預設行為。編輯，感謝@Erwin Brandstetter：RANGE UNBOUNDED PRECEDING是預設行為，但應該在我的情況下工作相同。）奇怪的是，在
ROWS UNBOUNDED PRECEDING EXCLUDE CURRENT ROW指定的情況下，我會根據處理的 ActivityUser 行數獲得指數級增長的查詢時間，但如果沒有它，整個記錄集的查詢將在大約 2 秒內完成。這是在 Azure 上執行的 Postgres 12.10 伺服器。大約有 200K ActivityUser 行，其中大約 135K 具有已知成功或失敗的狀態。通過
ROWS UNBOUNDED PRECEDING EXCLUDE CURRENT ROW指定並限制類似的行數WHERE act_user."Id" < 50000，我得到如下查詢時間。10K -> 2s 20K -> 11s 30K -> 23s 40K -> 40s 50K -> 60s當我不限制記錄計數時，查詢會執行大約 8 小時，然後顯然在伺服器上被中止。但是，如果我不限制記錄數但不使用
ROWS UNBOUNDED PRECEDING EXCLUDE CURRENT ROW，則整個查詢只需 2 秒。只需 2 秒即可ROWS UNBOUNDED PRECEDING。此外，無論我指定
ROWS UNBOUNDED PRECEDING EXCLUDE CURRENT ROW、 justROWS UNBOUNDED PRECEDING還是什麼都不指定，查詢計劃都是相同的。查詢和查詢計劃如下。如果有幫助，我也可以發布
EXPLAIN ANALYZE50K 查詢的結果。/* CREATE OR REPLACE FUNCTION pg_temp.status_to_int(status text) RETURNS integer AS $$ SELECT CASE status WHEN 'BOOKED' THEN 1 WHEN 'COLLECTED' THEN 1 WHEN 'DONE' THEN 1 WHEN 'CANCELED' THEN 0 ELSE NULL END $$ language sql; */ EXPLAIN SELECT act_user."Status" status, act."ActivityType" typ, user_."Role" role_, act_user."Id" act_user_id, pg_temp.status_to_int(act_user."Status") tgt, coalesce(SUM(pg_temp.status_to_int(act_user."Status")) OVER w_typ, 0) typ_good, COUNT(*) OVER w_typ typ_all, coalesce(SUM(pg_temp.status_to_int(act_user."Status")) OVER w_role, 0) role_good, COUNT(*) OVER w_role role_all, coalesce(SUM(pg_temp.status_to_int(act_user."Status")) OVER w_typ_role, 0) typ_role_good, COUNT(*) OVER w_typ_role typ_role_all FROM public."ActivityUsers" act_user JOIN public."ActivityRecords" act ON act."Id" = act_user."ActivityRecordId" JOIN public."Users" user_ ON user_."Id" = act_user."UserId" WHERE act_user."Status" IN ('BOOKED', 'COLLECTED', 'DONE', 'CANCELED') WINDOW w_typ AS (PARTITION BY act."ActivityType" ORDER BY act_user."Id" ROWS UNBOUNDED PRECEDING EXCLUDE CURRENT ROW), w_role AS (PARTITION BY user_."Role" ORDER BY act_user."Id" ROWS UNBOUNDED PRECEDING EXCLUDE CURRENT ROW), w_typ_role AS (PARTITION BY act."ActivityType", user_."Role" ORDER BY act_user."Id" ROWS UNBOUNDED PRECEDING EXCLUDE CURRENT ROW) /* WINDOW w_typ AS (PARTITION BY act."ActivityType" ORDER BY act_user."Id"), w_role AS (PARTITION BY user_."Role" ORDER BY act_user."Id"), w_typ_role AS (PARTITION BY act."ActivityType", user_."Role" ORDER BY act_user."Id") */ ORDER BY act_user_id;QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------ Limit (cost=88620.91..88621.16 rows=100 width=118) -> Sort (cost=88620.91..88959.89 rows=135593 width=118) Sort Key: act_user."Id" -> WindowAgg (cost=77675.94..83438.64 rows=135593 width=118) -> Sort (cost=77675.94..78014.92 rows=135593 width=98) Sort Key: act."ActivityType", act_user."Id" -> WindowAgg (cost=56074.11..60819.86 rows=135593 width=98) -> Sort (cost=56074.11..56413.09 rows=135593 width=82) Sort Key: act."ActivityType", user_."Role", act_user."Id" -> WindowAgg (cost=35473.76..39880.54 rows=135593 width=82) -> Sort (cost=35473.76..35812.75 rows=135593 width=66) Sort Key: user_."Role", act_user."Id" -> Hash Join (cost=10583.87..19942.69 rows=135593 width=66) Hash Cond: (act_user."UserId" = user_."Id") -> Hash Join (cost=9754.17..18756.95 rows=135593 width=65) Hash Cond: (act_user."ActivityRecordId" = act."Id") -> Seq Scan on "ActivityUsers" act_user (cost=0.00..5931.84 rows=135593 width=20) Filter: ("Status" = ANY ('{BOOKED,COLLECTED,DONE,CANCELED}'::text[])) -> Hash (cost=7189.63..7189.63 rows=115163 width=53) -> Seq Scan on "ActivityRecords" act (cost=0.00..7189.63 rows=115163 width=53) -> Hash (cost=667.09..667.09 rows=13009 width=9) -> Seq Scan on "Users" user_ (cost=0.00..667.09 rows=13009 width=9)
代替：
ROWS UNBOUNDED PRECEDING EXCLUDE CURRENT ROW與等價物：
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING您將看到與
ROWS UNBOUNDED PRECEDING.
EXCLUDE CURRENT ROW是後來添加到視窗框架（使用 Postgres 11）的，我假設（沒有研究原始碼）它的程式碼路徑沒有優化。該子句與預設的框架結尾結合起來幾乎沒有用處CURRENT ROW，因為可以使用基本的視窗框架語法來覆蓋，如所示。但是 Postgres 目前還不夠聰明（包括 Postgres 14）來辨識和簡化不必要的複雜性。（而且我不會屏住呼吸等待這個角落案例的變化，那裡有一個簡單的修復可用。）此外，預設的視窗框架不是
ROWS UNBOUNDED PRECEDING，而是- 這比您想像的更重要。我昨天在相關答案中發布了詳細資訊：RANGEUNBOUNDED PRECEDING您的查詢中還有很多可以改進的地方，但其餘的感覺就像有償工作。:)