PostgreSQL Query Profiler: プランとクエリを一致させる方法

すでにご利用されている方も多く、 Explain.tensor.ru - 当社の PostgreSQL 計画視覚化サービスは、その超能力の XNUMX つであるサーバー ログの読みにくい部分を認識していない可能性があります...

... 対応するプラン ノードのコンテキスト ヒントを含む、美しく設計されたクエリに変換されます。

彼の第二部の転写では、 PGConf.Russia 2020での報告 どうやってこれを実現できたかをお話します。

典型的なクエリ パフォーマンスの問題とその解決策に特化した最初の部分のトランスクリプトは、次の記事にあります。 「問題のある SQL クエリのレシピ」.

まず、色を付け始めましょう - そして、計画に色を付けることはもうありません、すでに色を付けています、すでに美しくてわかりやすいですが、リクエストがあります。

このようなフォーマットされていない「シート」では、ログから取得したリクエストが非常に見苦しく、不便であるように思えました。


特に、開発者がコード内のリクエストの本文を XNUMX 行に「貼り付ける」場合 (これはもちろんアンチパターンですが、実際には起こります)。 最悪！

これを何とかもっと綺麗に描いてみましょう。


そして、これを美しく描くことができれば、つまりリクエストの本体を分解して元に戻すことができれば、このリクエストの各オブジェクトにヒント、つまり計画の対応する点で何が起こったかを「添付」することができます。

クエリ構文ツリー

これを行うには、まずリクエストを解析する必要があります。


なぜなら私たちは持っているから システムのコアは NodeJS 上で実行されます、それからモジュールを作成しました。 GitHub で見つけてください。 実際、これらは PostgreSQL パーサー自体の内部に拡張された「バインディング」です。 つまり、文法は単純にバイナリでコンパイルされ、NodeJS から文法へのバインディングが作成されます。 私たちは他の人のモジュールを基礎として採用しました - ここには大きな秘密はありません。

リクエストの本文を入力として関数に入力します。出力では、解析された構文ツリーが JSON オブジェクトの形式で取得されます。


これで、このツリーを逆方向に実行し、必要なインデント、色付け、書式設定を使用してリクエストを組み立てることができます。 いいえ、これはカスタマイズできませんが、これは便利だと思われました。

クエリノードとプランノードのマッピング

ここで、最初のステップで分析したプランと XNUMX 番目のステップで分析したクエリをどのように組み合わせることができるかを見てみましょう。

簡単な例を見てみましょう。CTE を生成し、そこから XNUMX 回読み取るクエリがあります。 彼はそのような計画を立てます。

CTE

よく見ると、バージョン 12 まで (またはキーワードから始めます) MATERIALIZED) フォーメーション CTE はプランナーにとって絶対的な障壁です.


これは、リクエスト内のどこかに CTE 生成があり、プラン内のどこかにノードがある場合を意味します。 CTE、その後、これらのノードは間違いなく互いに「戦う」ので、すぐにそれらを結合できます。

アスタリスクの問題: CTE はネストできます。

ネストが非常に不十分なものや、同じ名前のものさえあります。 たとえば、内部では次のことができます。 CTE A 作る CTE X、内部では同じレベルにあります CTE B 再びそれを行う CTE X:

WITH A AS (
  WITH X AS (...)
  SELECT ...
)
, B AS (
  WITH X AS (...)
  SELECT ...
)
...

比較する場合は、このことを理解する必要があります。 これを「目で見て」理解することは、たとえ計画を見ても、リクエストの本文を見ても、非常に困難です。 CTE 生成が複雑で入れ子になっており、リクエストが大きい場合、それは完全に無意識です。

連合

クエリにキーワードがある場合 UNION [ALL] (XNUMX つのサンプルを結合する演算子)、プランでは次のいずれかのノードに対応します。 Append、またはいくつか Recursive Union.


「上」にあるもの UNION - これはノードの最初の子孫であり、XNUMX 番目の「下」にあります。 スルーした場合 UNION いくつかのブロックを一度に「接着」します。 Append- ノードは依然として XNUMX つだけですが、XNUMX つではなく、多くの子がその順序で存在します。

  (...) -- #1
UNION ALL
  (...) -- #2
UNION ALL
  (...) -- #3

Append
  -> ... #1
  -> ... #2
  -> ... #3

アスタリスクの問題: 再帰的サンプリング生成の内部 (WITH RECURSIVE) 複数の場合もあります UNION。 ただし、最後のブロックの後の最後のブロックだけが常に再帰的です UNION。 上記のものはすべて同じですが、異なります UNION:

WITH RECURSIVE T AS(
  (...) -- #1
UNION ALL
  (...) -- #2, тут кончается генерация стартового состояния рекурсии
UNION ALL
  (...) -- #3, только этот блок рекурсивный и может содержать обращение к T
)
...

また、そのような例を「突き止める」ことができる必要もあります。 この例では、次のことがわかります。 UNION-私たちのリクエストには 3 つのセグメントがありました。 したがって、XNUMX つは、 UNION 一致 Append-ノード、そして他のノードへ - Recursive Union.

読み取り/書き込みデータ

すべてがレイアウトされ、リクエストのどの部分が計画のどの部分に対応するかがわかりました。 そして、これらの作品の中で、私たちは「読みやすい」オブジェクトを簡単かつ自然に見つけることができます。

クエリの観点からは、それがテーブルなのか CTE なのかはわかりませんが、それらは同じノードによって指定されています RangeVar。 「可読性」の観点から見ると、これもかなり限定されたノードのセットです。

• Seq Scan on [tbl]
• Bitmap Heap Scan on [tbl]
• Index [Only] Scan [Backward] using [idx] on [tbl]
• CTE Scan on [cte]
• Insert/Update/Delete on [tbl]

私たちはプランとクエリの構造、ブロックの対応関係、オブジェクトの名前を知っており、XNUMX 対 XNUMX の比較を行います。


また タスク「アスタリスク付き」。 リクエストを受け取り、実行します。エイリアスはありません。同じ CTE からリクエストを XNUMX 回読み取るだけです。


計画を見てみましょう - 何が問題ですか? なぜエイリアスがあったのでしょうか? 私たちはそれを注文しませんでした。 彼はそのような「数字」をどこで入手するのでしょうか？

PostgreSQL 自体がそれを追加します。 それを理解する必要があるだけです まさにそのような別名 私たちにとっては、計画との比較を目的としており、意味はなく、単にここに追加されているだけです。 彼に注意を払わないようにしましょう。

2番目 タスク「アスタリスク付き」: パーティション化されたテーブルから読み取りを行っている場合、ノードを取得します。 Append または Merge Append、多数の「子供」で構成され、それぞれが何らかの形で Scanテーブルセクションから: Seq Scan, Bitmap Heap Scan または Index Scan。 ただし、いずれの場合でも、これらの「子」は複雑なクエリではありません。これが、これらのノードを他のノードと区別する方法です。 Append при UNION.


私たちはそのような結び目をも理解し、それらを「一つの山」に集めてこう言います。megatable から読み取ったものはすべてこことツリーの下にあります".

「単純な」データ受信ノード

Values Scan 計画的に対応している VALUES リクエストの中で。

Result なしのリクエストです FROM のような SELECT 1。 または意図的に虚偽の表現をした場合 WHERE-block (属性が表示されます) One-Time Filter):

EXPLAIN ANALYZE
SELECT * FROM pg_class WHERE FALSE; -- или 0 = 1

Result  (cost=0.00..0.00 rows=0 width=230) (actual time=0.000..0.000 rows=0 loops=1)
  One-Time Filter: false

Function Scan 同じ名前の SRF に「マッピング」します。

しかし、ネストされたクエリではすべてがより複雑になります。残念ながら、常に次のようになるとは限りません。 InitPlan/SubPlan。 時々彼らは ... Join または ... Anti Join、特に次のようなものを書くとき WHERE NOT EXISTS ...。 そして、ここではそれらを組み合わせることが常に可能であるとは限りません。計画のテキストには、計画のノードに対応する演算子がありません。

また タスク「アスタリスク付き」： いくつかの VALUES リクエストの中で。 この場合とプランでは、複数のノードを取得します。 Values Scan.


「番号付き」サフィックスは、それらを互いに区別するのに役立ちます。これらは、対応するものが見つかった順序で正確に追加されます。 VALUES- リクエストに沿って上から下にブロックします。

データ処理

私たちのリクエストの内容はすべて解決されたようです。残っているのは Limit.


しかし、ここではすべてが単純です - 次のようなノードです Limit, Sort, Aggregate, WindowAgg, Unique リクエスト内の対応する演算子 (存在する場合) に XNUMX 対 XNUMX で「マッピング」します。 ここには「スター」も困難もありません。

登録

組み合わせたいときに問題が発生する JOIN 自分たちの間で。 これは常に可能であるとは限りませんが、可能です。


クエリパーサーの観点から見ると、次のようなノードがあります。 JoinExpr、これにはちょうど XNUMX つの子 (左右) があります。 したがって、これは、リクエスト内の JOIN の「上」にあるものと、JOIN の「下」に書かれているものです。

計画の観点からすると、これら XNUMX 人はある人の子孫です。 * Loop/* Join-ノード。 Nested Loop, Hash Anti Join、... - そんな感じ。

単純なロジックを使用してみましょう。プラン内で互いに「結合」するテーブル A と B がある場合、リクエスト内でそれらは次のいずれかに配置されます。 A-JOIN-Bまたは B-JOIN-A。 このような組み合わせを試してみましょう、その逆の組み合わせを試してみましょう、というように、そのようなペアがなくなるまで続けます。

構文ツリーを取得し、計画を取得して、それらを見てみましょう...似ていません!


それをグラフの形で再描画しましょう - おお、すでに何かのように見えます。


子 B と C を同時に持つノードがあることに注意してください。順序は気にしません。 それらを組み合わせてノードの絵を裏返してみましょう。


もう一度見てみましょう。 これで、子 A とペア (B + C) を持つノードができました。これもそれらと互換性があります。


素晴らしい！ 私たちはこの二人であることが判明しました JOIN リクエストとプラン ノードが正常に結合されました。

残念ながら、この問題は必ずしも解決されるわけではありません。


たとえば、リクエストの場合、 A JOIN B JOIN C計画では、まず「外側」のノード A と C が接続されましたが、リクエストにはそのような演算子はなく、強調表示するものやヒントを添付するものは何もありません。 書くときの「カンマ」も同様です。 A, B.

しかし、ほとんどの場合、ほぼすべてのノードが「結合されていない」ため、時間内にこの種のプロファイリングを左側で取得できます。文字通り、JavaScript コードを分析するときの Google Chrome のようにです。 各行と各ステートメントの「実行」にどれくらいの時間がかかったかがわかります。


これらすべてをより便利に使用できるように、ストレージを作成しました。 アーカイブを保存し、後で関連するリクエストとともに計画を見つけたり、リンクを誰かと共有したりできます。

判読できないクエリを適切な形式に変換するだけの場合は、次を使用します。 私たちの「ノーマライザー」.

出所： habr.com