PostgreSQL クエリをどのように最適化する必要があったのか、そしてその結果何が得られたのかについて。
なぜそうしなければならなかったのですか？ そうです、それまでの 4 年間、時計が時を刻むように、すべてが静かに、穏やかに動いていたからです。
碑文として。
実際の出来事に基づいています。
名前はすべて変更されており、偶然の一致もあります。
特定の結果を達成したとき、何がきっかけですべてが始まったのかを思い出すのは常に興味深いことです。
その結果何が起こったのかは、記事「」で簡単に説明されています。'。
以前のイベントの連鎖を再現するのはおそらく興味深いでしょう。
履歴には正確な開始日 - 2018-09-10 18:02:48 が保存されました。
また、物語の中にはすべてが始まった次のような依頼もあります。
問題リクエストSELECT
p.parameter_idとして「PARAMETER_ID」、
pd."PD_NAME" AS pd_name、
pd."CUSTOMER_PARTNUMBER" AS customer_partnumber、
w.「LRM」は LRM として、
w.「LOTID」はロットイドとして、
w.「RTD_VALUE」を RTD_value として、
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」 AS Spent_name、
s.「SPENT_DATE」 AS Spent_date、
extract(year from "SPENT_DATE") AS 年、
(「SPENT_DATE」から月) を月として抽出、
s."REPORT_NAME" AS レポート名、
p.「STPM_NAME」 AS stpm_name、
p.「CUSTOMERPARAM_NAME」 AS customerparam_name
FROM wdata w、
費やした、
午後の午後、
Spent_pd sp、
PDPD
WHERE s.“SPENT_ID” = w.“SPENT_ID”
AND p."PARAMETER_ID" = w."PARAMETER_ID"
かつ 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 から、
wdata w2
WHERE s2.“SPENT_ID” = w2.“SPENT_ID”
かつ w2.“LRM” = w.“LRM”);
問題の説明は予想どおり標準的で、「すべてが悪い。 何が問題なのか教えてください。」
私はすぐに、3 インチ ドライブの時代の逸話を思い出しました。
レーマーがハッカーのところにやって来ます。
-何もうまくいきません。問題がどこにあるのか教えてください。
-DNAでは...
しかし、もちろん、これはパフォーマンスの問題を解決する方法ではありません。 「彼らは私たちのことを理解できないかもしれない" （と）。 それを理解する必要があります。
さて、掘ってみましょう。 その結果何かが溜まっていくのかもしれない。
調査が開始されました
つまり、EXPLAINに頼ることなく、肉眼ですぐに見えるものです。
1) JOIN は使用されません。 これは、特に接続数が複数の場合に問題です。
2) しかし、さらに悪いのは、相関サブクエリ、さらには集計を伴うことです。 これは非常に悪いです。
もちろんこれはダメです。 しかし、これは一方でだけです。 一方で、問題には解決策と改善できる要望が明確にあるため、これは非常に良いことです。
占い師には行かないでください（C）。
クエリ プランはそれほど複雑ではありませんが、非常に参考になります。
実行計画
いつものように、最も興味深く役に立つのは最初と最後です。
ネストされたループ (コスト = 935.84..479763226.18 行 = 3322 幅 = 135) (実際の時間 = 31.536..8220420.295 行 = 8111656 ループ = 1)
計画時間: 3.807 ミリ秒
実行時間: 8222351.640 ミリ秒
完成時間は2時間以上。
時間がかかった誤った仮説
仮説 1 - オプティマイザーが間違いを犯し、間違った計画を構築します。
実行計画を視覚化するには、次のサイトを使用します。 。 ただし、このサイトには興味深いものや役立つものは何も表示されませんでした。 一見しても、二度見しても、本当に役立つものは何もありません。 フルスキャンが最小限である可能性はありますか。 どうぞ。
仮説 2 - 自動バキューム側からのベースへの衝撃、ブレーキを取り除く必要があります。
ただし、自動バキューム デーモンは適切に動作し、長時間ハングするプロセスはありません。 深刻な負荷はありません。 何か別のものを探す必要があります。
仮説 3 - 統計は時代遅れで、すべてを再計算する必要がある
繰り返しますが、そうではありません。 統計は最新のものです。 自動バキュームに問題がないことを考えると、これは驚くべきことではありません。
最適化を始めましょう
メインテーブル「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 222ms (2時間強)
それは次のようになりました。 6 985 431.575 ミリ秒 (ほぼ 2 時間)
一般に、同じリンゴを側面から見たものです。
古典的なものを思い出してみましょう。
「同じものを持っていますが、羽がありませんか？」 求めます」。
原則として、これは良い結果と言えますが、まあ、良くはありませんが、許容範囲内です。 少なくとも、どれだけの作業が行われたのか、そしてなぜその作業が良かったのかを説明する大規模なレポートを顧客に提供してください。
しかし、それでも最終決定はまだ遠い。 非常に遠く。
そして今、最も興味深いのは、最適化を続け、リクエストを磨き上げていくことです
ステップ XNUMX - JOIN を使用する
書き換えられたリクエストは次のようになります (まあ少なくとももっと美しい):
JOINを使用したクエリSELECT
p.parameter_idとして「PARAMETER_ID」、
pd."PD_NAME" AS pd_name、
pd."CUSTOMER_PARTNUMBER" AS customer_partnumber、
w.「LRM」は LRM として、
w.「LOTID」はロットイドとして、
w.「RTD_VALUE」を RTD_value として、
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」 AS Spent_name、
s.「SPENT_DATE」 AS Spent_date、
extract(year from "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 used s ON w.“SPENT_ID”=s.”“SPENT_ID”
INNER JOIN pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
INNER JOIN Spent_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' AND s.“SPENT_DATE” <= '2018-09-30'AND
s.“SPENT_DATE” = (SELECT MAX(s2.“SPENT_DATE”)
FROM wdata w2 INNER JOIN used s2 ON w2.“SPENT_ID”=s2.“SPENT_ID”
INNER JOIN wdata w
ON w2.“LRM” = w.“LRM” );
計画時間: 2.486 ミリ秒
実行時間: 1223680.326 ミリ秒
ということで、最初の結果。
それは次のとおりです。 6 ミリ秒 (ほぼ 985 時間)。
それは次のようになりました。 1 223 680.326 ミリ秒 (20 分強)。
良い結果です。 繰り返しになりますが、原則として、そこで停止することもできます。 でも面白くないからやめられない。
ために
ステップ XNUMX - 相関サブクエリを削除する
変更されたリクエストテキスト:
相関サブクエリなしSELECT
p.parameter_idとして「PARAMETER_ID」、
pd."PD_NAME" AS pd_name、
pd."CUSTOMER_PARTNUMBER" AS customer_partnumber、
w.「LRM」は LRM として、
w.「LOTID」はロットイドとして、
w.「RTD_VALUE」を RTD_value として、
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」 AS Spent_name、
s.「SPENT_DATE」 AS Spent_date、
extract(year from "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 used s ON s.“SPENT_ID” = w.“SPENT_ID”
INNER JOIN pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
INNER JOIN Spent_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 ミリ秒
実行時間: 165021.870 ミリ秒
それは次のとおりです。 1 223 680.326 ミリ秒 (20 分強)。
それは次のようになりました。 165 021.870 ミリ秒 (2 分強)。
これはもうかなり良いです。
しかし、イギリス人が言うように、「しかし、しかし、常にあります」 結果が良すぎると、自動的に疑惑が生じるはずです。 ここは何かがおかしい。
クエリを修正して相関サブクエリを削除するという仮説は正しいです。 ただし、最終結果を正しくするには、少し調整する必要があります。
その結果、最初の中間結果は次のようになります。
相関サブクエリのない編集されたクエリSELECT
p.parameter_idとして「PARAMETER_ID」、
pd."PD_NAME" AS pd_name、
pd."CUSTOMER_PARTNUMBER" AS customer_partnumber、
w.「LRM」は LRM として、
w.「LOTID」はロットイドとして、
w.「RTD_VALUE」を RTD_value として、
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」 AS Spent_name、
s.「SPENT_DATE」 AS Spent_date、
extract(year from s.“SPENT_DATE”) AS 年、
extract(month from s.“SPENT_DATE”) を月として抽出、
s."REPORT_NAME" AS レポート名、
p.「STPM_NAME」 AS stpm_name、
p.「CUSTOMERPARAM_NAME」 AS customerparam_name
FROM wdata w INNER JOIN used s ON s.“SPENT_ID” = w.“SPENT_ID”
INNER JOIN pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
INNER JOIN Spent_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 ミリ秒
実行時間: 208014.134 ミリ秒
したがって、最終的に得られるのは最初に受け入れられる結果であり、顧客に見せるのは恥ずかしいことではありません。
以下から始まりました: 8 222 351.640 ミリ秒 (2 時間以上)
1 ミリ秒 (223 分強) を達成することができました。
結果 (中間): 208 014.134 ミリ秒 (3 分強)。
素晴らしい結果です。
合計
そこで立ち止まることもできたでしょう。
しかし…
食欲は食べるとついてくるものです。 歩く者は道を極める。 どの結果も中間です。 止まって死んでしまった。 等。
最適化を続けましょう。
いい案。 特に顧客が気にしていなかったことを考えると。 そしてそれに対して強くさえ。
そこで、データベースを再設計する時期が来ました。 クエリ構造自体は最適化できなくなりました (ただし、後で判明したように、すべてが実際に失敗することを保証するオプションがあります)。 しかし、データベース設計の最適化と開発を開始することは、すでに非常に有望なアイデアです。 そして最も重要なのは興味深いことです。 もう一度、青春時代を思い出してください。 私はすぐに DBA になったわけではなく、プログラマー (BASIC、アセンブラ、C、ダブルプラス C、Oracle、plsql) として育ちました。 興味深いトピックですが、もちろん別の回想録として取り上げます ;-)。
ただし、気を散らさないようにしましょう。
このように、
それともパーティショニングが役立つでしょうか?
スポイラー - 「はい、パフォーマンスの最適化を含め、役に立ちました。」
しかし、それはまったく別の話です...
つづく…
出所： habr.com