【豆知識】SQLチューニングアプローチの考え方
PostgreSQLインサイド

SQLチューニングアプローチの考え方として、テーブル定義やSQL文の書き方を見直す考え方と、PostgreSQLのプランナ（オプティマイザ）が作成する実行計画を外部から制御する考え方があります。後者には、SQL文にヒント句を指定してアクセス方法を明示的に指示する手法と、統計情報を固定化して常にその統計情報が利用されるようにする手法があります。
ここでは、SQLチューニングアプローチ手法の概要と、実行計画を外部から制御する手法の使い分けについて解説します。

参考

SQLチューニングの概要や用語については「SQLチューニングの概要」で詳しく解説しています。

SQLチューニングアプローチ手法の紹介

SQLチューニングには、具体的に以下3つのアプローチ手法があります。

1）テーブル定義とSQL文の書き方を見直す

テーブルやインデックスの定義、WHERE句や条件の記載を適切に見直すことにより、PostgreSQLのプランナ（オプティマイザ）が最適な実行計画を作成するように改善します。なお、プランナ（オプティマイザ）は現在の統計情報を元に実行計画を作成するため、運用においては、統計情報が最新化されている必要があります。

2）周辺OSSのpg_hint_planでヒント句を使って実行計画を制御する

複雑なSQLを実行する場合や、テーブルの更新頻度が高くて統計情報を最新化できていない場合、最適な実行計画が作成されないことがあります。その場合は、ユーザーがSQL文にヒント句を追加することにより、意図した実行計画になるよう制御します。

3）周辺OSSのpg_dbms_statsで統計情報を固定化して実行計画を制御する

刻々と統計情報が変化する場合、最適な実行計画が作成されないことがあります。その場合は、ユーザーが統計情報を固定化することにより、間接的に実行計画を制御します。

SQLチューニングが必要になった場合、まず、1）の手法でSQLチューニングを実施することが重要です。それでも解決できない場合には、周辺OSSを利用した2）や3）の手法を実施することを検討します。

参考

各手法の操作例については、PostgreSQL技術インデックスで詳しく解説しています。

• 1）のテーブル定義を見直す操作例については、「SQLチューニングを実施する」
• 2）の操作例については、「pg_hint_planで実行計画を制御する」
• 3）の操作例については、「pg_dbms_statsで統計情報を固定化して、実行計画を制御する」

2）と3）の手法は、どちらもプランナ（オプティマイザ）の作成する実行計画を外部から制御する周辺OSSを使用しますが、使い分けの判断が難しいところがあります。次に、これら2つの手法の仕組みと使い分けについて説明します。

pg_hint_planとpg_dbms_statsの仕組み

ユーザーが入力したSQL文が実行される仕組みと、その過程でpg_hint_planとpg_dbms_statsが作動する仕組みについて、以下に図解します。

pg_hint_planとpg_dbms_statsの使い分け

pg_hint_planとpg_dbms_statsが、それぞれがどのようなケースに適しているかについて解説します。一般的には、業務アプリケーションが改修できるか否か、チューニングの単位を各クエリとするかクエリ全体とするかで使い分けられます。

pg_hint_planの使いどころ

pg_hint_planは、一般的に業務アプリケーションの改修量が少ない、影響箇所が局所的である、改修の工数が確保できるなど、個々にSQL文を改修できる場合に利用されます。クエリ単位にヒント句を指定することで、SQL文やGUCパラメーターを変更せずに、実行計画を制御できます。ヒント句には、スキャン方法や結合順などを記載しますが、SQL文の実行結果には影響しません。そのため、テストはヒント句を付けたSQL文の性能テストだけで済みます。

pg_dbms_statsの使いどころ

pg_dbms_statsは、業務アプリケーションの数が大量である、影響箇所が広範囲に及ぶ、改修の工数が十分に確保できないなど、個々にSQL文を改修できない場合に利用されます。大規模で性能の安定化が求められるシステムなどで利用実績があります。性能テストは統計情報を固定化している際に実行されるすべてのSQL文を対象に行う必要があります。

2020年6月12日公開

本コンテンツに関するお問い合わせ