AQO-PostgreSQL適応クエリの最適化

クエリを実行するとき、最新のDBMSはコスト最適化モデルを使用します。構成ファイルに格納された係数と収集された統計に基づいて、取得の「コスト」と結果の行セットのボリュームが計算されます。クエリが再度実行されると、コストと選択性が再計算されます。クエリを実行してこれらのパラメーターの実際の値を表示できますが、DBMSオプティマイザーは（標準の）再スケジュール中にこの情報を使用しません。



しかし、オプティマイザがクエリの実行に必要なコスト、選択性、およびその他の必要なパラメータの実際の値を保存し、それを再度実行すると、収集された標準の統計だけでなく、前回の実行後に保存された統計によってもガイドされた場合はどうなりますか？



これは適応クエリ最適化と呼ばれ、有望です。一部のDBMSはすでにそのようなテクノロジーを使用しています。Postgres Professional



社は、数年間、PostgreSQLのAQO拡張に取り組んでおり、（何らかの形で）適応最適化を実装しています。作業はまだ進行中ですが、すでにテストする必要があります。 最初に、クエリ最適化の主題領域を詳しく見てみましょう。

プランナーが次善の計画を選択できる理由

SQLクエリは、さまざまな方法で実行できます。たとえば、2つのテーブルの結合がある場合、ネストされたループの使用、マージ、ハッシュなど、いくつかの異なる方法で実行できます。クエリに参加するテーブルが多いほど、結合のバリエーションが多くなります。スケジューラのタスクは、多くのバリエーションから最小コストのクエリ実行プランを選択することです。



すでに述べたように、彼らの仕事では、多くのDBMSのスケジューラは、自動または手動で収集された統計情報を使用します。プランナーは、これらの統計に基づいて見積もりコストを計算します。



一般に、最新のDBMSスケジューラはほとんどの状況でうまく機能します。ただし、場合によっては、選択した計画が最適とはほど遠いことがあります。



例えば、関連する統計情報の欠如は、プランナが結合されたテーブルの行数に関する（おそらく）正しくないデータに焦点を合わせるという事実につながります。カーディナリティの過度の過小評価（または過大評価）は、テーブル内のデータにアクセスするための非最適な方法の選択につながります。



もう1つの重要な理由は、必要なインデックスがないことです。インデックスがない場合、スケジューラーはデータアクセス方法の選択に制限があります。



依存（相関）条件の使用また、DBMSの動作に悪影響を与える可能性があります。スケジューラー（デフォルト）は、クエリ内のすべての条件が互いに独立していることを前提としています。つまり、1つの条件の値が他の条件に影響を与えることはありません。これは常にそうであるとは限りません。依存条件（郵便番号や都市など）が使用されている場合、プランナーは接続の間違ったコストとカーディナリティも計算します。条件での関数



の使用もスケジューラに影響を与える可能性があります。スケジューラーの関数は「ブラックボックス」であり、関数が返す行の数がわからないため、計画の誤ったコストにつながる可能性もあります。

スケジューラーに影響を与える方法

実際の統計は、スケジューラの適切な作業に不可欠な条件です。まず、統計情報を定期的に収集するようにシステムが構成されていることを確認してください。

上記の状況を修正し、プランナがより最適なクエリ実行プランを選択できるようにするには、いくつかの方法があります。



インデックスがないと、プランナーはデータを取得する1つの方法しかありません-順次テーブルスキャン（そしてこれは常に悪くて高価なわけではありません）。場合によっては、必要なインデックスを作成すると、データアクセスを高速化できます。テーブル全体をスキャンする必要はありません。しかし、インデックスの使用（必要なインデックスの検索、作成、保守）は自由な喜びではありません。理想的には、必要な場所で正確に使用する必要があります。そして、必要のない場所-使用しないでください。



クエリで相関結合条件を使用すると、拡張統計を生成できます-条件が互いに関連していることをオプティマイザに明示的に「プロンプト」します。これを行うには、従属列の組み合わせの数を事前に予測することが難しいため、DBA（または開発者）はデータをよく理解し、クエリの従属条件を監視する必要があります。このようなオプションごとに、拡張統計を手動で作成する必要があります。



関数を作成するときに、おおよその実行コストや、関数によって生成される行数の見積もりを指定できます。バージョン12 では、ヘルパー関数を使用して、引数に応じてプランナーの推定を改善することが可能になりました。これも手動の方法であり、常に最適な結果が得られるとは限りません。



他のすべてが失敗した場合は、手動でクエリを書き換えることができますたとえば、マテリアライズドビュー、Common Table Expressions（CTE）を使用します。または、サブジェクト領域の要件を明確にし、場合によっては、クエリロジックを根本的に書き換えます。



適応クエリの最適化（ -とスケジューラに「ヒント」のもう一つの方法がありdaptive Q ueryでO ptimizationが）。この方法の考え方は、クエリが実行された後、実際の統計情報が保存され、指定された（または同様の）クエリが繰り返されたときに、オプティマイザがそれに依存できるということです。 DBMS Postgres Pro Enterpriseは、AQOと呼ばれる適応クエリ最適化の拡張機能です



。この拡張機能はgithubに投稿されています：github.com/postgrespro/aqo、バニラPostgreSQLで試すことができます。

モジュールの仕組み

AQOモジュールは、その作業で機械学習を使用します。動作原理の詳細については、Oleg Ivanovによる記事で機械学習を使用してPostgreSQLのパフォーマンスを向上させること、さらに詳しくは、アダプティブクエリの最適化（YouTubeのレポート）のプレゼンテーションを参照してください。



この方法の本質を以下に簡単に説明します。



コストを見積もるには、プランナがカーディナリティの見積もりを必要とします。これには、条件の選択性の見積もりが必要です。



単純な条件（ "attribute = constant"や "attribute> constant"など）の場合、プランナには選択性を推定するモデルがあります。これを行うために、彼は統計情報を使用します：一意の属性値、ヒストグラムなどの数。

論理接続詞を使用する単純な要素で構成される条件の場合、プランナーは簡単に計算される式を使用します。

• sel（Aではない）= 1-sel（A）
• sel（Aではない）= 1-sel（A）
• sel（AおよびB）= sel（A）* sel（B）
• sel（AまたはB）= sel（not（not Aおよびnot B））= 1-（1-sel（A））*（1-sel（B））

これらの式は、条件AとBの独立性（無相関）を想定しているため、この想定に違反した場合に誤った推定値が得られます。

AQOは式を複雑にします。単純な条件ごとに独自の係数が導入されます。機械学習を使用して（最近傍回帰を使用して）、AQOはこれらの係数を調整して、式によって計算された選択性が、AQOが以前に観察した実際の選択性と最もよく一致するようにします。



このため、モジュールは以下を保存します。

• , ;
• .

その作業では、AQOは定数までの条件を区別します。これにより、解決される問題の複雑さを軽減でき、さらに-ほとんどの場合-情報は依然として失われません。AQOは定数の値を「認識」しませんが、条件の選択性を「認識」します。

損失が発生する状況。これらは、特定の値に関係なく定数によって評価される条件です。たとえば、一部の条件では、スケジューラは妥当な見積もりを行うことができず、デフォルトの定数を選択します（たとえば、条件「式1 =式2」の選択性は常に0.005として評価され、「式1>式2」は1/3として評価されます）。



したがって、AQOは複雑な条件の選択性の見積もりを改善します（その結果、コスト見積もりも改善され、より適切な実行計画の選択につながる可能性があります）。

モジュールのインストール

バニラPostgreSQLでモジュールの機能を試すには、特別なパッチを使用して、ソースコードからシステムをアセンブルする必要があります。詳細については、github のREADMEファイルを参照してください。



Postgres Pro Enterpriseを使用する場合、AQOモジュールのインストールは標準モードで続行されます。



shared_preload_libraries = 'aqo'



その後、必要なデータベースに拡張機能を作成できます。

データベースの準備

AQOモジュールがデモデータベースでどのように機能するかの具体例を見てみましょう。2016年9月から2017年9月までの年間のフライト情報を含む大きなデータベースを使用します。



まず、拡張機能を作成します。

CREATE EXTENSION aqo;

次に、並列クエリの処理をオフにして、並列プランの表示がメインタスクの邪魔にならないようにし



max_parallel_workers_per_gather = 0;



ます。PostgreSQLスケジューラーがテーブルを結合するためのオプションを増やすには、2つのインデックスを作成します。

CREATE INDEX ON flights (scheduled_departure );
CREATE INDEX ON ticket_flights (fare_conditions );

結果を分析するときは、ジョブを実行するために読み取る必要があるページ数として、BUFFERSの値に焦点を当てます。実行時間も見てみましょう（ただし、ロードされたシステムと自宅のラップトップの時間は大きく異なる場合があります）。



バッファキャッシュとwork_memを増やして、すべての作業がRAMで実行されるようにします。



shared_buffers = '256MB';

work_mem = '256MB';

AQOモジュールの使用

リクエストを作成してみましょう。特定の日付からビジネスクラスに飛んで、1時間以内の遅延で到着した乗客を取得する必要があります。

AQOを使用せずにリクエストを実行してみましょう（以下、モジュールの動作の理解に影響しない情報の一部は計画から削除されています）。

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT t.ticket_no
  FROM flights f
   	JOIN ticket_flights tf ON f.flight_id = tf.flight_id
   	JOIN tickets t ON tf.ticket_no = t.ticket_no
 WHERE f.scheduled_departure > '2017-08-01'::timestamptz
   AND f.actual_arrival < f.scheduled_arrival + interval '1 hour'
   AND tf.fare_conditions = 'Business';

そして、結果の計画を見てみましょう 。この場合、スケジューラは最適な計画を検討しました。最初に、ビットマップ（）をスキャンすることにより、フライトテーブルから行のセットを取得します。これは、インデックスを使用して取得したticket_flightsテーブルからの行のセットとハッシュ（ノード）で接続します。スキャン（）。結果の結果は、最後のネストされたループ結合（node ）の外部行セットとして使用されます。この結合の内部セットは、tickets（）テーブルの排他インデックススキャンを使用して取得されます。 最も「かさ高い」操作は、ネストされたループの内部行セットを取得することです-106 205バッファーがそこに読み込まれます。



Nested Loop (rows=33210) (actual rows=31677)

  Buffers: shared hit=116830 read=1

  ->  Hash Join (rows=33210) (actual rows=31677)

        Hash Cond: (tf.flight_id = f.flight_id)

        ->  Index Scan ... on ticket_flights tf  

              Index Cond: fare_conditions = 'Business'

        ->  Hash

              ->  Bitmap Heap Scan on flights f (rows=8331) (actual rows=7673)

                    Recheck Cond: scheduled_departure > '2017-08-01'

                    Filter: actual_arrival < scheduled_arrival + '01:00:00'::interval

                    ->  Bitmap Index Scan on ... [flights]

                          Index Cond: scheduled_departure > '2017-08-01'

                          Buffers: shared hit=44 read=1

  ->   Index Only Scan  ... on tickets t (rows=1 width=14) (actual rows=1 loops=31677)

        Index Cond: (ticket_no = tf.ticket_no)

        Buffers: shared hit=106205

 Planning Time: 9.326 ms

 Execution Time: 675.836 ms



Bitmap Heap Scan on flightsHash JoinIndex Scan ... on ticket_flightsNested LoopIndex Only Scan ... on tickets





ネストされたループが外部セットの比較的少数の行を結合するため、このプランは比較的優れたプランと言えます。



次に、実験を行い、リクエスト内の日付の変更に応じて、提案された計画がどのように変更されるか（変更されないか）を確認します。日付は、条件を満たすフライトテーブルの行の範囲を順次増やすように選択されます。これにより、このテーブルへのアクセスのカーディナリティを評価するときにプランナーエラーが発生します。上記の計画では、最初の日付で、オプティマイザのカーディナリティがほとんど間違えられていないことがわかります（）。 リクエストで次の日付に置き換えます。Bitmap Heap Scan on flights f (rows=8331) (actual rows=7673)

• 2017-04-01
• 2017-01-01
• 2016-08-01

そして結果を見てください：





要約すると、AQOモジュールを使用しない場合、プランナーは次のように機能します。



結果をもう一度見てみましょう。

AQOがオンになっていると、スケジューラはネストされたループ接続から切り替えて、インデックスを使用してハッシュ接続と順次スキャンを行う必要があることをすぐに認識します。

まとめる

アダプティブクエリ最適化にAQOモジュールを使用することには、利点と欠点の両方があります。



モジュールを使用する利点の1つは、クエリの依存条件を追跡する必要がないことです。場合によっては、クエリの実行速度が向上することがあります。また、モジュールの使用にはさまざまなモードがあります。たとえば、AQOを使用して、特定のタイプのクエリのみを最適化できます。



モジュールの欠点のうち、モジュール構造で統計をトレーニングおよび保存するための追加のオーバーヘッドコストを区別できます。また、モジュールによって収集された統計情報はレプリカに送信されません。



AQOモジュールは、スケジューラーの考えられるすべての問題の「特効薬」ではありません。たとえば、一部の状況では、モジュールは拡張統計を置き換えることができます（手動で作成されていない場合）、または無関係な統計に注意を払いません。ただし、モジュールは必要なインデックスを作成せず、さらにクエリテキストを書き換えません。



したがって、すべてのリクエストに対してモジュールを有効にするべきではありません。AQOの理想的な最適化候補は、ノードのカーディナリティーの計算におけるプランナーのエラーが悪い計画につながるクエリです。そして、何らかの理由で、この見積もりの​​精度に影響を与えることはできません。