クエリ実行計画

はじめに

こんにちは。システム開発一部の大宮です。

皆さんが図書館で本を探す場合どのようにして探すでしょうか。
端から端まで隅々見て本を探すでしょうか。
図書館にある本が3冊とかであればそれでいいですが何万冊もあれば現実的ではないですね。

出版社で探したり、作者名だったり、ジャンルだったりと探したい本や蔵書数などの状況によって最適な探し方があると思います。

SQLでも同じように探し方が悪い場合途方もない時間待つことになるため、
データベースから最適な探し方でデータを取得する必要があります。

実行したSQLが最適であるかはクエリの実行計画を利用することで確認することができます。

今回はそんな実行計画についてPostgreSQLを例にお話ししていこうと思います。

SQLの仕組み

実行計画の詳細は一旦置いておいて、SQLがどのように実行されているのかを抽象的にでも理解しておくことが重要です。おおよその流れは以下の通りです。

  1. クライアントアプリケーション:
    SQLクエリをPostgreSQLデータベースに送信する。
  2. パーサー (Parser):
    SQLクエリを解析し、構文解析を行います。
    具体的には、クエリをトークンに分解し、
    SELECTやWHEREなどの構文要素を理解します。
  3. リライター (Rewriter):
    解析結果を基にクエリを再構築します。
    例えば、ビューを使ったクエリの場合、ビューの定義を元にクエリを書き換えます。
  4. プランナー/オプティマイザ (Planner/Optimizer):
    実行計画を作成します。
    PostgreSQLはテーブルにどのようなデータが入っているかを直接は知りませんが、統計情報を基にして実行計画を作成します。
    この計画は、データを効率的に取得する方法を決定します。
  5. エグゼキューター (Executor):
    実行計画に基づいてSQLを実行します。
  6. クライアントアプリケーション:
    実行結果を受け取り、表示します。

この流れを理解することで、PostgreSQLにおけるSQLの実行プロセスを把握できます。

統計情報について

実行計画は、SQLが実行される際にオプティマイザによって作成されます。

実際のデータベースの情報ではなく、統計情報を基に実行計画が作成されるという点に注意が必要です。
統計情報が最新でない場合、オプティマイザは古い情報を基に実行計画を立てようとします。

そのため、ある時点では最適な実行計画が選ばれていてSQLのパフォーマンスに問題がなかったとしても、
データの登録・更新・削除が頻繁に行われるにつれてSQLのパフォーマンスが劣化していくケースがあります。

これを回避するには、テーブルのデータに大きな変更があった際に、統計情報も最新化する必要があります。
統計情報の収集および更新は、自動バキューム処理の実行時に自動的に行われます。

実行計画の取得

実行計画の取得と各項目については下記の通りになります。

実行計画の取得例

EXPLAIN PLAN FOR
SELECT /*+ USE_HASH(d) */ e.employee_id, e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.department_id = 10;
 
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
 
実行計画の取得結果の例
| Id  | Operation           | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                   |     1 |    55 |     3   (0)| 00:00:01 |
|*  1 |  HASH JOIN          |                   |     1 |    55 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL | DEPARTMENTS       |    27 |   486 |     2   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN  | EMP_DEPT_IDX      |     1 |    20 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
   3 - access("E"."DEPARTMENT_ID"=10)

 

各列の意味

  • Id：操作の識別子。
  • Operation：実行される操作の種類。
  • Name：対象となるテーブルやインデックスの名前。
  • Rows：予測される行数。
  • Bytes：予測されるデータ量（バイト単位）。
  • Cost (%CPU)：操作のコストとCPU使用率の予測値。
  • Time：操作にかかる予測時間。

上記結果を基に想定よりCostやTime、またはRows等が大きい数値となっている箇所を特定しSQLの改善を行っていきます。

SQLの改善案

状況や場合によって、改善すべき方法は異なると思いますが、例として、実行計画で結合部分のパフォーマンスが悪い場合には、
以下の結合方式を基にSQLを変更すると、結合のパフォーマンスが向上することがあります。

  • ネストループ結合 (Nested Loop Join)
    外部テーブルの各行に対して、内部テーブルを繰り返しスキャンする結合方法です。小規模なデータセットやインデックスが効果的に利用できる場合に適しています。
  • マージ結合 (Merge Join)
    両方の入力がソートされている場合に使用される結合方法です。結合条件に基づいて、ソートされたデータを効率的に結合します。
  • ハッシュ結合 (Hash Join)
    一方のテーブルをハッシュテーブルに変換し、もう一方のテーブルをそのハッシュテーブルと照合して結合する方法です。大規模なデータセットに対して効果的です。

また、PostgreSQLでは標準機能としてヒント句をサポートしていませんが、pg_hint_plan拡張モジュールを使用することで、
オプティマイザに対してヒントを与え、実行計画の調整を行うことができます。ヒント句は、SQL文のコメントとして記述され、オプティマイザに対するガイドラインとなります。

pg_hint_planを用いたヒント句の例:

SELECT /*+ HashJoin(table1 table2) */ *
FROM table1
JOIN table2 ON table1.id = table2.id;

 

ヒント句を使用するデメリットとしては下記の通りです。

  1. 依存性の増加:
    ヒント句を使用することで、SQLクエリが特定の実行計画に依存するようになります。
    データベースの状態が変わると、ヒント句が最適でなくなる可能性があります。
    例えば、データ量の増減やインデックスの追加・削除などにより、異なる実行計画が最適となる場合があります。

  2. メンテナンスの負担:
    ヒント句を含むSQLクエリは、データベースの状態に合わせて頻繁に更新する必要がある場合があります。
    これにより、SQLクエリのメンテナンスが複雑化し、運用負担が増加します。

  3. オプティマイザの進化に対する障害:
    PostgreSQLのオプティマイザはバージョンアップにより改善されることが多いですが、
    ヒント句を使用することで、その恩恵を受けにくくなる可能性があります。
    最新のオプティマイザがより優れた実行計画を生成できる場合でも、ヒント句がこれを阻害することがあります。

  4. 移植性の低下:
    ヒント句はデータベースごとに異なるため、他のデータベースシステムへの移植が難しくなります。
    PostgreSQLで使用したヒント句が他のデータベースシステムでサポートされていない場合、SQLクエリを修正する必要があります。

これらのデメリットを考慮しつつ、ヒント句の使用を検討することが重要です。
適切な結合方法を選択し、必要に応じてヒント句を使用することで、SQLのパフォーマンスを改善できますが、過度な依存は避けるべき状況もあります。

最後に

SQLの実行計画は、クエリのパフォーマンスを最適化するための重要なツールです。
実行計画を理解し、適切に最適化することで、データベースの効率を大幅に向上させることができます。
この記事で紹介した基礎知識を活用して、必要あれば実行計画の分析と最適化に取り組んでみてください。

 

人材募集