PostgreSQLデータベースでのクエリプランナーの調査

チャート、レポート、および分析-これらはすべて、非常に小さな企業のバックオフィスに何らかの形で存在します。 Excel / Numbers / Libreの通常のテーブルで混雑しているが、データがまだそれほど大きくない場合、社内のニーズに対する従来のソリューションは、PostgreSQL、MySQL、MariaDBなどのリレーショナルデータベースを使用して構築されることがよくあります。



これらのデータベースは無料で、SQLのおかげでシステム内の他のコンポーネントと簡単に統合でき、人気があり、ほとんどの開発者とアナリストがそれらを使用できます。 彼らは、会社が分析とレポートのためのより複雑な（そして高価な）ソリューションを購入できるようになるまで、簡単に耐えられるように十分な量（トラフィックとボリューム）を消化できます。

開始位置

しかし、繰り返し研究されてきた技術であっても、常にさまざまなニュアンスがあり、それが突然エンジニアの心配に加わる可能性があります。 信頼性に加えて、データベースで最も頻繁に言及される問題は、そのパフォーマンスです。 明らかに、データ量の増加に伴い、DBの応答率は低下しますが、これが予測どおりに発生し、負荷の増加と一致する場合、これはそれほど悪くありません。 データベースが注意を必要とし、基本的に異なるデータベースへのアップグレードまたは移行を計画し始めるときはいつでも事前に確認できます。 データベースのパフォーマンスが予測できないほど低下すると、さらに悪化します。



データベースのパフォーマンスを向上させるというトピックは、世界と同じくらい古く、非常に広範囲にわたるものであり、この記事では、一方向のみに焦点を当てたいと思います。 つまり、PostgreSQLデータベースでのクエリプランの有効性を評価し、この効率を経時的に変更して、データベーススケジューラの動作をより予測可能にします。



議論されることの多くはこのデータベースのすべての最新バージョンに適用可能であるという事実にもかかわらず、以下の例は現時点では後者のバージョン11.2を意味します。

詳細を説明する前に、リレーショナルデータベースのパフォーマンスの問題がどこから発生する可能性があるかについて少し話をするのは理にかなっています。 「スローダウン」するとき、データベースは正確に何でビジーですか？ メモリ不足（多数のディスクまたはネットワークアクセス）、弱いプロセッサ、これらはすべて明確な解決策の明らかな問題ですが、クエリ実行速度に影響するものは他にありますか？

思い出を一新

データベースがSQLクエリに応答するには、クエリプランを作成する必要があります（どのテーブルと列でどのインデックスが必要か、そこから何を選択するか、何を比較するか、どのくらいのメモリが必要かなど）。 この計画はツリーの形で形成され、そのノードは計算の複雑さの異なる典型的な操作のほんの一部です。 以下に例を示します（Nは操作を実行する行の数です）。

運営	何をする	費用
SELECT ... WHERE ...データ取得操作
シーケンススキャン	テーブルから各行をロードし、条件を確認します。	O（N）
インデックススキャン （Bツリーインデックス）	データは直接インデックスにあるため、インデックスの必要な要素を条件で検索し、そこからデータを取得します。	O（ログ（N））、ソートされたツリー内のアイテムを検索します。
インデックススキャン （ハッシュインデックス）	データは直接インデックスにあるため、インデックスの必要な要素を条件で検索し、そこからデータを取得します。	O（1）、ハッシュの作成コストを除く、ハッシュテーブル内のアイテムの検索
ビットマップヒープスキャン	必要な行の番号をインデックスで選択し、必要な行のみをロードして追加のチェックを実行します。	インデックススキャン+シーケンススキャン（M）、 ここで、Mはインデックススキャン後に見つかった行の数です。 M << N、つまり インデックスはSeqスキャンよりも便利です。
結合操作（複数のテーブルからの結合、選択）
入れ子ループ	左の表の各行について、右の表で適切な行を探します。	O（N 2 ）。 ただし、テーブルの1つが他のテーブル（辞書）よりもはるかに小さく、実際には時間とともに成長しない場合、実際のコストはO（N）に減少する可能性があります。
ハッシュ結合	左右のテーブルの各行について、ハッシュを考慮します。これにより、可能な接続オプションの検索回数が減少します。	O（N）。ただし、非常に非効率的なハッシュ関数または接続の多数の同一フィールドの場合、O（N 2 ）
結合を結合	条件ごとに、左右のテーブルを並べ替え、その後、2つの並べ替えられたリストを結合します	O（N *ログ（N）） 並べ替えコスト+リストを確認します。
集約操作（GROUP BY、DISTINCT）
グループ集合	集計条件でテーブルを並べ替え、並べ替えられたリストで隣接する行をグループ化します。	O（N *ログ（N））
ハッシュ集計	各行の集約条件のハッシュを考慮します。 同じハッシュを持つ行の場合、集計を実行します。	O（N）

ご覧のとおり、リクエストのコストは、データがテーブルにどのように配置されているか、およびこの順序が使用されるハッシュ操作にどのように対応するかに大きく依存します。 ネストされたループは、O（N ² ）のコストにもかかわらず、結合テーブルの1つが1つまたは複数の行に縮退している場合、ハッシュ結合またはマージ結合よりも収益性が高くなります。



CPUリソースに加えて、コストにはメモリ使用量が含まれます。 これらは両方ともリソースが限られているため、クエリプランナーは妥協する必要があります。 2つのテーブルがハッシュ結合を介して結合する方が数学的により有益であるが、そのような大きなハッシュテーブル用のメモリに単純に場所がない場合、たとえば、データベースはマージ結合の使用を強制される場合があります。 「遅い」ネストループは通常、追加のメモリを必要とせず、起動直後に結果を生成する準備ができています。



これらの操作の相対的なコストは、グラフでより明確に示されています。 これらは絶対数ではなく、さまざまな操作のおおよその比率です。







ネストされたループチャートは、以下で「開始」されます。 追加の計算やメモリの割り当て、中間データのコピーは必要ありませんが、O（N ² ）コストがかかります。 結合の結合とハッシュの結合は初期コストが高くなりますが、いくつかのN値の後、それらは時間内にネストされたループに勝ち始めます。 スケジューラは、コストが最も低いプランを選択しようとします。上記のチャートでは、異なるN（緑の破線の矢印）で異なる操作を順守しています。 N1までの行数では、Nested Loopを使用する方が収益性が高く、N1からN2までは結合の方が収益性が高く、N2後はハッシュ結合の方が収益性が高くなりますが、ハッシュ結合ではハッシュテーブルを作成するためにメモリが必要です。 また、N3に達すると、このメモリが不足し、Merge Joinが強制的に使用されます。



計画を選択するとき、スケジューラは、データベース内の一部の「アトミック」操作の相対コストのセットを使用して、計画内の各操作のコストを推定します。 たとえば、計算、比較、メモリへのページのロードなど。 以下に、デフォルト構成からのこれらのパラメーターの一部のリストを示しますが、多くはありません。

相対コスト定数	デフォルト値
seq_page_cost	1.0
random_page_cost	4.0
cpu_tuple_cost	0.01
cpu_index_tuple_cost	0.005
cpu_operator_cost	0.0025
parallel_tuple_cost	0.1
parallel_setup_cost	1000.0

確かに、これらの定数だけではほとんどありません。「N」、つまり、各操作で前の結果から処理する必要がある行数を正確に知る必要があります。 ここでの上限は明らかです。データベースはどのテーブルにどれだけのデータがあるかを「知って」おり、常に「最大」まで計算できます。 たとえば、それぞれが100行の2つのテーブルがある場合、それらを結合すると、出力に0〜10,000行が生成されます。 したがって、次の入力操作には最大10,000行を含めることができます。



ただし、テーブル内のデータの性質について少しでも知っていれば、この行数をより正確に予測できます。 たとえば、上記の例の100行の2つのテーブルの場合、結合によって10,000行は生成されず、同じ100行が生成されることが事前にわかっている場合、次の操作の推定コストは大幅に削減されます。 この場合、この計画は他の計画よりも効果的です。

すぐに使える最適化

スケジューラーが中間結果のサイズをより正確に予測できるようにするために、PostgreSQLはテーブルの統計収集を使用します。統計収集はpg_statisticまたはより読みやすいバージョン（pg_stats）に蓄積されます。 バキュームの開始時に自動的に更新されるか、ANALYZEコマンドで明示的に更新されます。 このテーブルには、テーブル内のデータと種類に関するさまざまな情報が格納されます。 特に、値のヒストグラム、空のフィールドの割合、およびその他の情報。 計画者はこれらすべてを使用して、計画ツリー内の各操作のデータ量をより正確に予測し、したがって操作と計画全体のコストをより正確に計算します。



クエリを例に取ります：

SELECT t1.important_value FROM t1 WHERE t1.a > 100
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

「t1.a」列の値のヒストグラムにより、テーブルの行の約1％に100を超える値が見つかったことが判明したと仮定します。 次に、このようなサンプルがテーブル「t1」のすべての行の約100分の1を返すと予測できます。

データベースでは、EXPLAIN ANALYZEを使用して、EXPLAINコマンドを介して計画の予測コストとその操作の実際の時間を確認することができます。



自動統計ではすべてがうまくいくように見えますが、問題が発生する可能性があります。 これについてはCitus Dataから 、CREATE STATISTICS（PG 10.0で利用可能）を使用した自動統計の非効率性と追加の統計の収集の例に関する良い記事があります。



そのため、スケジューラの場合、コストの計算には2つのエラーの原因があります。

1. プリミティブ操作の相対コスト（seq_page_cost、cpu_operator_costなど）は、デフォルトでは実際とは大きく異なる場合があります（cpuコスト0.01、srqページロードコスト-ランダムページロードの場合は1または4）。 100回の比較が1ページの読み込みに等しいという事実からはほど遠い。
2. 中間操作の行数の予測エラー。 この場合の運用の実際のコストは、予測とは大きく異なる場合があります。

複雑なクエリでは、考えられるすべての計画を作成して予測するのに、単独で多くの時間がかかります。 データベースがわずかなリクエストのみを計画している場合、1秒でデータを返すのはどのような用途ですか？ PostgreSQLには、この状況に対応するGeqoオプティマイザーがあります。これは、可能なすべてのプランのバリエーションを構築するのではなく、いくつかのランダムなプランから始めて最良のプランを完成させ、コストを削減する方法を予測するスケジューラーです。 これはすべて、予測の精度を向上させるものではありませんが、少なくともある程度の最適な計画の検索を高速化します。

突然の計画-競合他社

すべてがうまくいけば、リクエストは可能な限り迅速に処理されます。 データ量が増加するにつれて、データベースでのクエリ実行の速度が徐々に増加し、しばらくして、それを観察すると、メモリまたはCPUコアの数を増やすか、クラスターを拡張するなどが必要になる時期をおおよそ予測できます。



しかし、最適な計画には実行コストが近い競合他社が存在するという事実を考慮する必要がありますが、それはわかりません。 また、データベースでクエリプランが突然変更された場合、これは驚きです。 データベースがより効率的な計画にジャンプするのは良いことです。 そうでない場合は？ たとえば、写真を見てみましょう。 これは、2つの計画（赤と緑）の実装の予測コストとリアルタイムです。







ここでは、1つの計画が緑で表示され、最も近い「競合他社」が赤で表示されます。 点線は予測コストのグラフを示し、実線はリアルタイムです。 灰色の破線矢印は、プランナーの選択を示しています。



ある金曜日の夜、ある中間操作で予測された行数がN1に達し、「赤」の予測が「緑」の予測を上回り始めたとします。 スケジューラーが使用を開始します。 実際のクエリ実行時間はすぐにジャンプします（緑の実線から赤の実線に切り替わります）。つまり、データベースの劣化スケジュールはステップ（または「壁」）の形をとります。 実際には、このような「壁」は、クエリの実行時間を1桁以上増加させる可能性があります。



この状況は、フロントエンドよりもバックオフィスと分析の方がおそらく一般的であることに注意してください。後者は通常、より多くの同時クエリに適合しているため、計画予測のエラーが少ないデータベースでより単純なクエリを使用するためです。 これがレポートまたは分析用のデータベースである場合、クエリは任意に複雑になる可能性があります。

それと一緒に暮らすには？

疑問が生じます。そのような「水中」の目に見えない計画をどうにか予見することができましたか？ 結局のところ、問題はそれらが最適ではないということではなく、別の計画への切り替えが予期せずに発生する可能性があり、平均の法則によれば、このための最も不幸な瞬間です。



残念ながら、それらを直接見ることはできませんが、選択される実際の重みを変更することにより、代替プランを探すことができます。 このアプローチの意味は、最も近い競合者の1人が最適になるようにスケジューラが最適と見なす現在の計画を視野から削除することです。したがって、EXPLAINチームを通して彼を見ることができます。 このような「競合他社」とメインプランのコストの変化を定期的にチェックすることで、データベースがすぐに別のプランに「ジャンプ」する可能性を評価できます。



代替計画の予測に関するデータを収集するだけでなく、それらを実行してそのパフォーマンスを測定することもできます。これにより、データベースの内部「幸福」の概念もわかります。

そのような実験のために私たちが持っているツールを見てみましょう。



まず、セッション変数を使用して特定の操作を明示的に「禁止」できます。 便利なことに、設定で変更する必要はなく、データベースがリロードされます。値は現在開いているセッションでのみ変更され、他のセッションには影響しないため、実際のデータを直接試すことができます。 以下にそれらのリストとデフォルト値を示します。 ほとんどすべての操作が含まれます。

使用される操作	デフォルト値
enable_bitmapscan enable_hashagg enable_hashjoin enable_indexscan enable_indexonlyscan enable_material enable_mergejoin enable_nestloop enable_parallel_append enable_seqscan enable_sort enable_tidscan enable_parallel_hash enable_partition_pruning	に
enable_partitionwise_join enable_partitionwise_aggregate	オフ

特定の操作を禁止または許可することにより、同じEXPLAINコマンドで確認できる他のプランをスケジューラーに強制的に選択させます。 実際、操作の「禁止」は使用を禁止するものではなく、単にコストを大幅に増加させます。 PostgreSQLでは、「禁止」操作ごとに100億従来単位のコストが自動的に積み上げられます。 さらに、EXPLAINでは、計画の総重量が非常に高くなることがありますが、これらの数百億を背景に、残りの操作の重量は、通常は小さい注文に収まるため、はっきりと見えます。



特に興味深いのは、次の2つの操作です。

• ハッシュ結合。 その複雑さはO（N）ですが、結果の量の予測にエラーがあるため、メモリに収まらず、O（N * log（N））のコストで結合結合を実行する必要があります。
• ネストされたループ。 その複雑さはO（N ² ）であるため、サイズ予測の誤差はそのような接続の速度に二次的に影響します。

たとえば、クエリから実際の数値を取得してみましょう。クエリの最適化は、当社で行っていました。



計画1.許可されたすべての操作で、最適な計画の総コストは274962.09ユニットでした。



計画2。 「禁止」ネストループを使用すると、コストが40000534153.85に増加します。 費用の大部分を占めるこれらの400億は、禁止にもかかわらず、使用されるネストされたループの4倍です。 残りの534153.85-これは、計画内の他のすべての操作のコストの正確な予測です。 ご覧のとおり、これは最適なプランのコストの約2倍です。つまり、非常に近い値です。



計画3。 「禁止」ハッシュ結合では、コストは383253.77でした。 この計画は、何十億も見られないため、ハッシュ結合操作を使用せずに実際に作成されました。 ただし、そのコストは最適なコストよりも30％高く、これも非常に近い値です。



実際には、クエリの実行時間は次のとおりでした。



プラン1 （すべての操作が許可されています）は、約9分で完了しました。

計画2 （「禁止」のネストされたループを使用）は1.5秒で完了しました。

プラン3 （「禁止」ハッシュ結合を使用）は、約5分で完了しました。



ご覧のように、理由はネストされたループのコストの誤った予測です。 実際、EXPLAINとEXPLAIN ANALYZEを比較すると、中間操作での不運なNの定義でエラーが検出されます。 予測された単一の行ではなく、ネストされたループが数千の行に遭遇したため、クエリの実行時間が数桁増加しました。



「禁止」ハッシュ結合による節約は、ハッシュをソートとマージ結合に置き換えることに関連しています。この場合、ハッシュ結合よりも速く動作しました。 この計画2は、実際には「最適な」計画1のほぼ2倍の速さであることに注意してください。



実際には、リクエストが突然（DBのアップグレード後または単独で）以前よりも長く実行され始めた場合、まずハッシュ結合またはネストされたループのいずれかを拒否し、これがクエリの速度にどのように影響するかを確認してください。 成功した場合、少なくとも新しい最適でない計画を禁止し、以前の速い計画に戻ることができます。



これを行うために、データベースの再起動でPostgreSQL構成ファイルを変更する必要はありません。どのコンソールでも、データベースから開いているセッションの目的の変数の値を変更するのは非常に簡単です。 残りのセッションは影響を受けず、現在のセッションの構成のみが変更されます。 たとえば、次のように：

 SET enable_hashjoin='on'; SET enable_nestloop='off'; SELECT … FROM … (    )
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

計画の選択に影響を与える2番目の方法は、低レベル操作の重みを変更することです。 ここには普遍的なレシピはありませんが、たとえば、「ウォームアップ」キャッシュを備えたデータベースがあり、データ全体がメモリに格納されている場合、シーケンシャルページロードのコストはランダムページのロードのコストと変わらない可能性があります。 一方、デフォルトの構成では、randomは順次の4倍の費用がかかります。



または、別の例では、並列処理を実行する条件付きコストはデフォルトで1000ですが、ページをロードするコストは1.0です。 一度に1つのパラメーターのみを変更して、それが計画の選択に影響するかどうかを判別することから始めるのは理にかなっています。 最も単純な方法は、パラメーターを0または高い値（100万）に設定して開始することです。



ただし、あるリクエストのパフォーマンスを改善することで、別のリクエストのパフォーマンスを低下させる可能性があることを覚えておく必要があります。 一般的に、実験には幅広い分野があります。 順番に1つずつ変更してみてください。

代替治療オプション

スケジューラに関する話は、少なくとも2つのPostgreSQL拡張について言及しなければ不完全です。



1つ目はSR_PLANで、計算されたプランを保存し、それ以上使用することを強制します。 これにより、プランの選択に関してデータベースの動作をより予測しやすくなります。



2つ目は、 クエリのリアルタイム実行からスケジューラーへのフィードバックを実装するAdaptive Query Optimizerです。つまり、スケジューラーは、実行されたクエリの実際の結果を測定し、これを念頭に置いて将来の計画を調整します。 したがって、データベースは特定のデータとクエリに対して「自己調整」されます。

データベースの速度が低下した場合、他に何をしますか？

クエリプランを多かれ少なかれ整理したので、データベース自体と、それを使用して最大のパフォーマンスを得るためにそれを使用するアプリケーションの両方で、他に改善できるものを確認します。



クエリプランが既に最適であるとします。 最も明らかな問題（メモリ不足または遅いディスク/ネットワーク）を除外した場合、ハッシュの計算には依然としてコストがかかります。 PostgreSQLの将来の改善（おそらくGPUまたはCPUのSSE2 / SSE3 / AVX命令を使用）の大きな機会がおそらくありますが、これはまだ行われておらず、ハッシュ計算はハードウェアのハードウェア機能をほとんど使用していません。 このデータベースで少し助けてください。



気が付くと、デフォルトでPostgreSQLのインデックスはbツリーとして作成されます。 それらの有用性は、非常に用途が広いことです。 このようなインデックスは、等価条件と比較条件（多かれ少なかれ）の両方で使用できます。 このようなインデックスでアイテムを見つけることは対数コストです。 ただし、クエリに等値条件のみが含まれる場合、インデックスはハッシュインデックスとして作成することもできますが、そのコストは一定です。



さらに、リクエストを修正して、並列実行を使用することもできます。 書き換え方法を正確に理解するには、スケジューラによって同時実行が自動的に禁止されている場合のリストをよく理解し、そのような状況を回避するのが最善です。 このトピックのマニュアルでは、すべての状況について簡単に説明しているため、ここでそれらを繰り返すことは意味がありません。



リクエストの並行性がまだ不十分な場合はどうすればよいですか？ あなたが唯一のクライアントであり、1つのコアが100％占有され、他のすべてのカーネルがそれを見るだけである、強力なマルチコアデータベースで見るのは非常に悲しいことです。 この場合、アプリケーションからデータベースを支援する必要があります。 各セッションには独自のコアが割り当てられているため、それらのいくつかを開いて、一般的なクエリを部分に分割し、より短い選択とより速い選択を行い、それらをアプリケーション内の既に共通の結果に結合できます。 これにより、PostgreSQLデータベースで使用可能な最大CPUリソースが使用されます。



結論として、上記の診断および最適化機能は氷山の一角にすぎませんが、それらは非常に使いやすく、構成を台無しにしたり、他のアプリケーションの動作を中断したりすることなく、運用データ上で問題を直接迅速に特定するのに役立ちます。



正確で短い計画で、あなたのリクエストに幸運を。