關於我們如何最佳化 PostgreSQL 查詢以及由此產生的結果。
為什麼你必須這樣做？ 是的，因為在過去的四年裡，一切都安靜、平靜地進行，就像時鐘滴答作響。
作為銘文。
根據真實事件改編。
所有名字都已更改，巧合純屬偶然。
當你取得某種成果時，記住開始的動力是什麼，一切是從哪裡開始的，總是很有趣的。
因此，文章中簡要描述了結果發生的情況““。
重新創建先前的事件鏈可能會很有趣。
歷史記錄保存了確切的開始日期 - 2018-09-10 18:02:48。
此外，故事中還有一個請求，這一切都是從這個請求開始的：
問題請求選擇
p.“PARAMETER_ID”作為parameter_id，
pd."PD_NAME" AS pd_name,
pd.「CUSTOMER_PARTNUMBER」AS customer_partnumber，
w.「LRM」作為 LRM，
w.“LOTID”作為lotid，
w.「RTD_VALUE」作為RTD_值，
w.“LOWER_SPEC_LIMIT” AS lower_spec_limit，
w.“UPPER_SPEC_LIMIT” AS upper_spec_limit，
p.“TYPE_CALCUL”AS type_calcul，
s.“SPENT_NAME”作為花名，
s.“SPENT_DATE”作為花費日期，
提取（“SPENT_DATE”中的年份）AS年份，
提取（“SPENT_DATE”中的月份）作為月份，
s."REPORT_NAME" AS 報告名稱，
p.“STPM_NAME”AS stpm_name，
p.“CUSTOMERPARAM_NAME” AS customerparam_name
來自 wdata w,
花了 s,
時間點 p,
花費_pd sp，
PD PD
其中 s.“SPENT_ID” = w.“SPENT_ID”
AND p."PARAMETER_ID" = w."PARAMETER_ID"
AND s.“SPENT_ID” = sp.“SPENT_ID”
AND pd."PD_ID" = sp."PD_ID"
AND s.“SPENT_DATE” >= '2018-07-01' AND s.“SPENT_DATE” <= '2018-09-30'
和 s.“SPENT_DATE” = (SELECT MAX(s2.“SPENT_DATE”)
從花費的 s2 開始，
數據w2
其中 s2.“SPENT_ID” = w2.“SPENT_ID”
且 w2.“LRM” = w.“LRM”);
問題的描述不出所料是標準的——「一切都很糟糕。 告訴我問題是什麼。”
我立刻想起了3吋半驅動器時代的軼事：
拉默來找駭客。
- 對我來說沒有任何作用，請告訴我問題出在哪裡。
-DNA中...
但當然，這不是解決效能事件的方法。 “他們可能不理解我們「 （和）。 我們需要弄清楚。
好吧，讓我們來挖掘一下。 也許結果會累積一些東西。
調查開始
所以，用肉眼就能立即看到什麼，甚至不需要解釋。
1) 不使用 JOIN。 這很糟糕，尤其是當連接數超過一個時。
2）但更糟的是關聯子查詢和聚合。 這真是太糟糕了。
這當然是不好的。 但這只是一方面。 另一方面，這很好，因為問題顯然有解決方案和可以改進的要求。
不要去找算命先生 (C)。
查詢計劃並不複雜，但很有指示性：
執行計劃
像往常一樣，最有趣和最有用的是在開頭和結尾。
巢狀循環（成本=935.84..479763226.18行=3322寬度=135）（實際時間=31.536..8220420.295行=8111656循環=1）
規劃時間：3.807 ms
執行時間：8222351.640 ms
完成時間超過2小時。
需要時間的錯誤假設
假設 1 - 優化器犯了錯誤並製定了錯誤的計劃。
為了可視化執行計劃，我們將使用該網站 。 然而，該網站沒有顯示任何有趣或有用的內容。 乍看和第二眼，沒有什麼能真正有幫助的。 全面掃描是否可能是最小的。 前進。
假設2-從自動真空側對底座的衝擊，需要擺脫煞車。
但 autovacuum 守護程式表現良好，沒有長期掛起的進程。 沒有嚴重的負載。 我們需要尋找其他東西。
假設3－統計數據已經過時，一切都需要重新計算
再說一次，不是那樣。 統計數據是最新的。 考慮到 autovacuum 不存在問題，這並不奇怪。
讓我們開始優化
主表'wdata'當然不小，將近3萬筆記錄。
全盤掃描遵循的就是這張表。
哈希條件：((w."SPENT_ID" = s."SPENT_ID") AND ((SubPlan 1) = s."SPENT_DATE"))
-> 順序掃描 在wdata w上（成本= 0.00..574151.49行= 26886249寬度= 46）（實際時間= 0.005..8153.565行= 26873950循環= 1）
我們做標準的事情：“來吧，讓我們做一個索引，一切都會順利。”
在「SPENT_ID」欄位上建立索引
因此：
使用索引查詢執行計劃
嗯，有幫助嗎？
曾是： 8 222 351.640 毫秒 （2小時多一點）
變成： 6 985 431.575 毫秒（近 2 小時）
一般來說，同一個蘋果，側面看。
讓我們記住經典：
「你有同樣的，但沒有翅膀嗎？ 會尋求」。
原則上，這可以稱得上是一個好的結果，嗯，不好，但可以接受。 至少，向客戶提供一份大型報告，描述已經完成了多少工作以及為什麼所做的事情是好的。
但距離最終決定仍然遙遠。 非常遠。
現在最有趣的事情 - 我們繼續優化，我們將完善請求
第一步 - 使用 JOIN
重寫的請求現在看起來像這樣（好吧，至少更漂亮):
使用 JOIN 進行查詢選擇
p.“PARAMETER_ID”作為parameter_id，
pd."PD_NAME" AS pd_name,
pd.「CUSTOMER_PARTNUMBER」AS customer_partnumber，
w.「LRM」作為 LRM，
w.“LOTID”作為lotid，
w.「RTD_VALUE」作為RTD_值，
w.“LOWER_SPEC_LIMIT” AS lower_spec_limit，
w.“UPPER_SPEC_LIMIT” AS upper_spec_limit，
p.“TYPE_CALCUL”AS type_calcul，
s.“SPENT_NAME”作為花名，
s.“SPENT_DATE”作為花費日期，
提取（“SPENT_DATE”中的年份）AS年份，
提取（“SPENT_DATE”中的月份）作為月份，
s."REPORT_NAME" AS 報告名稱，
p.“STPM_NAME”AS stpm_name，
p.“CUSTOMERPARAM_NAME” AS customerparam_name
FROM wdata w INNER JOIN 花了 s ON w.“SPENT_ID”=s.”“SPENT_ID”
INNER JOIN pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
INNER JOIN花費_pd sp ON s.“SPENT_ID”= sp.“SPENT_ID”
INNER JOIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
WHERE
s.“SPENT_DATE”>=“2018-07-01”且 s.“SPENT_DATE”<=“2018-09-30”且
s.“SPENT_DATE” = (SELECT MAX(s2.“SPENT_DATE”)
FROM wdata w2 INNER JOIN 在 w2.“SPENT_ID”=s2.“SPENT_ID”上花了 s2
內連接 wdata w
ON w2.“LRM” = w.“LRM” );
規劃時間：2.486 ms
執行時間：1223680.326 ms
所以，第一個結果。
曾是： 6 毫秒（近 985 小時）。
變成： 1 223 680.326 毫秒（僅 20 多分鐘）。
好結果。 原則上，我們再次可以就此打住。 但這太無趣了，你根本停不下來。
為了
第二步 - 刪除相關子查詢
更改後的請求文字：
沒有相關子查詢選擇
p.“PARAMETER_ID”作為parameter_id，
pd."PD_NAME" AS pd_name,
pd.「CUSTOMER_PARTNUMBER」AS customer_partnumber，
w.「LRM」作為 LRM，
w.“LOTID”作為lotid，
w.「RTD_VALUE」作為RTD_值，
w.“LOWER_SPEC_LIMIT” AS lower_spec_limit，
w.“UPPER_SPEC_LIMIT” AS upper_spec_limit，
p.“TYPE_CALCUL”AS type_calcul，
s.“SPENT_NAME”作為花名，
s.“SPENT_DATE”作為花費日期，
提取（“SPENT_DATE”中的年份）AS年份，
提取（“SPENT_DATE”中的月份）作為月份，
s."REPORT_NAME" AS 報告名稱，
p.“STPM_NAME”AS stpm_name，
p.“CUSTOMERPARAM_NAME” AS customerparam_name
FROM wdata w INNER JOIN 花費 s ON s.“SPENT_ID” = w.“SPENT_ID”
INNER JOIN pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
INNER JOIN花費_pd sp ON s.“SPENT_ID”= sp.“SPENT_ID”
INNER JOIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
INNER JOIN (SELECT w2.“LRM”, MAX(s2.“SPENT_DATE”)
FROM 花費的 s2 INNER JOIN wdata w2 ON s2.“SPENT_ID” = w2.“SPENT_ID”
按 w2.“LRM”分組
) md on w.“LRM” = md.“LRM”
WHERE
s."SPENT_DATE" >= '2018-07-01' 且 s."SPENT_DATE" <= '2018-09-30';
規劃時間：2.291 ms
執行時間：165021.870 ms
曾是： 1 223 680.326 毫秒（僅 20 多分鐘）。
變成： 165 021.870 毫秒（僅 2 分鐘多一點）。
這已經很好了。
然而，正如英國人所說“但是，總是有一個但是」 太好的結果自然會引起懷疑。 這裡不對勁。
關於修正查詢以消除相關子查詢的假設是正確的。 但您需要稍微調整它才能使最終結果正確。
結果，第一個中間結果：
沒有相關子查詢的編輯查詢選擇
p.“PARAMETER_ID”作為parameter_id，
pd."PD_NAME" AS pd_name,
pd.「CUSTOMER_PARTNUMBER」AS customer_partnumber，
w.「LRM」作為 LRM，
w.“LOTID”作為lotid，
w.「RTD_VALUE」作為RTD_值，
w.“LOWER_SPEC_LIMIT” AS lower_spec_limit，
w.“UPPER_SPEC_LIMIT” AS upper_spec_limit，
p.“TYPE_CALCUL”AS type_calcul，
s.“SPENT_NAME”作為花名，
s.“SPENT_DATE”作為花費日期，
提取（s.“SPENT_DATE”中的年份）AS年份，
提取（s.“SPENT_DATE”中的月份）作為月份，
s."REPORT_NAME" AS 報告名稱，
p.“STPM_NAME”AS stpm_name，
p.“CUSTOMERPARAM_NAME” AS customerparam_name
FROM wdata w INNER JOIN 花費 s ON s.“SPENT_ID” = w.“SPENT_ID”
INNER JOIN pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
INNER JOIN花費_pd sp ON s.“SPENT_ID”= sp.“SPENT_ID”
INNER JOIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
INNER JOIN ( SELECT w2.“LRM”, MAX(s2.“SPENT_DATE”) AS “SPENT_DATE”
FROM 花費的 s2 INNER JOIN wdata w2 ON s2.“SPENT_ID” = w2.“SPENT_ID”
按 w2.“LRM”分組
) md ON md.“SPENT_DATE” = s.“SPENT_DATE” AND md.“LRM” = w.“LRM”
WHERE
s."SPENT_DATE" >= '2018-07-01' 且 s."SPENT_DATE" <= '2018-09-30';
規劃時間：3.192 ms
執行時間：208014.134 ms
所以，我們最後得到的是第一個可以接受的結果，向客戶展示這並不丟臉：
開始於： 8 222 351.640 ms（超過2小時）
我們設法達到：1 毫秒（略多於 223 分鐘）。
結果（臨時）： 208 014.134 毫秒（僅 3 分鐘多一點）。
非常好的結果。
總
我們本來可以停在那裡。
但…
食慾是隨著吃東西而來的。 行走的人，才能掌握道路。 任何結果都是中間的。 停下來就死了。 ETC。
我們繼續優化。
好想法。 特別是考慮到客戶甚至不介意。 甚至強烈支持它。
因此，是時候重新設計資料庫了。 查詢結構本身無法再優化（儘管後來發現，有一個選項可以確保一切實際上都會失敗）。 但開始優化和開發資料庫設計已經是一個非常有前途的想法。 最重要的是有趣。 再一次，記住你的青春。 我並沒有立即成為 DBA，我是作為一名程式設計師（BASIC、彙編程式、C、雙加 C、Oracle、plsql）長大的。 當然，這是一個有趣的話題，適合單獨的回憶錄;-)。
不過，我們不要分心。
因此，
或者也許分區會對我們有幫助？
劇透 - “是的，它有幫助，包括優化性能。”
但這是一個完全不同的故事......
待續…
來源： www.habr.com