クエリチューニング〜PostgreSQLのきほん〜

はじめに

この記事では

PostgreSQLの実行計画分析の流れと観点
PostgreSQLのクエリチューニング例

についてまとめました。

性能試験、ユーザからのクレーム、スロークエリ検知などで

問題のクエリをチューニングする際に役に立つ内容です。

クエリチューニングの流れと実行計画分析の観点

クエリチューニングの第一歩として

実行計画を分析する必要があるのですが、

一度クエリチューニングの流れを見てみましょう。

僕のやり方を紹介します。

問題のクエリの全体像をざっくり把握する

実行計画を取得する

コストの高いオペレーションから実行計画を分析する

チューニングして計測する

STEP2~4を繰り返す

STEP1.問題のクエリの全体像を把握する

まず問題のクエリの特徴をざっくりとでも掴むところから始めます。

例えば僕の場合、この辺りをざっとクエリと一部ヒアリングしながら整理します。

クエリに改善の余地があるか
仕様自体が複雑すぎるか
どういう使われ方をするどういう特徴のクエリなのか

実行計画を見るまでもなく、

クエリやヒアリングを通じてざっくりと問題箇所にあたりをつけることはできますし、

仕様の落とし所を探ることも視野に入れるのが重要だと考えています。

クエリをとてつもなく複雑にしてなんとかチューニングできても、

それを保守し続けることができなくなるなら問題は解決できたことにはなりません。

例えば、

ほとんど使われない検索機能のために、

インデックスを張りまくって検索性能だけ向上できたものの、

そのせいで毎日動くバッチ処理の性能や、

日々のオンライン処理の性能が劣化するのであれば、

どちらの性能を優先するでしょうか？？

クエリチューニングは、

問題のクエリをただ性能改善できれば良いのではなく、

このようなトレードオフを考えながら行う必要があると

自分は考えています。

STEP2.実行計画を取得する

STEP1.でクエリに改善の余地がありそう、

もしくは判断がつかない場合は

実行計画を取得してどのようにデータへのアクセスが行われたか確認しましょう。

実行計画は、

列統計情報、PostgreSQLのコスト調整パラメータ

などを元にクエリプランナが作成します。

そのため、

取得した実行計画が最適でない可能性もあります。

詳細はこちらの記事(適切な実行計画が選択されない原因)をご覧ください

2023年12月6日実行計画を取得して解析する〜PostgreSQLのきほん〜

統計情報が最適にならない可能性については、

このタイミングでいくつか妄想できると思います。

STEP3.コストの高いオペレーションから実行計画を確認する

ここからは実行計画を分析していきます。

当たり前ですが基本方針としては、

クエリプランなが算出したコストや実際に実行に時間がかかるオペレーション

から分析と改善をしていきます。

ここでは、

今まで妄想してあたりをつけてきた原因が、

本当にその通りかを確認していきます。

例えば、

列統計情報が実態と乖離しているかも？？

と推測したのであれば、

実行計画のコストのかかっているオペレーションにおいて、

テーブルの実態と列統計情報を比較します。

列統計情報の確認

SELECT * FROM pg_stats WHERE tablename = 'テーブル名' AND attname = '列名';

STEP4.チューニングして計測する

STEP3で特定した原因に対応します。

特定した原因次第では、

PostgreSQLのパラメータチューニングのみ行い、

クエリチューニングが不要なケースもあるでしょう。

また、

チューニングしたら本番環境相当の大量データ環境下で、

性能を計測する必要があります。

特に、インデックスを追加した場合などは、

そのテーブルへの登録、更新時にインデックス更新の

オーバーヘッドがかかるようになるため、

そのテーブルを登録、更新するオンライン処理、バッチ処理の性能が

劣化する可能性もあることに注意しましょう。

場合によっては、

性能試験をやり直す必要もあるかもしれません。

クエリチューニングの例

クエリチューニングの流れが掴めたところで、

ここからはよくあるクエリチューニングの例を見ていきましょう！！

不足しているインデックス

スロークエリの原因でよくあるのはインデックスの不足です。

実行計画を確認してインデックスが不足していたら、

インデックス更新のオーバーヘッドに注意しながらインデックスを追加します。

インデックスを追加したら、

インデックスを想定通り利用できているか？？

利用するだけでなくインデックスオンリースキャンにできないか？？

まで検討してみても良いでしょう。

インデックススキャンの場合、

インデックスデータのリーフ部から

データファイルのポインタを使ってデータファイルへ

アクセスする必要があります。

インデックススキャンイメージ

インデックススキャンの実行計画例

Index Scan using idx_department on employees  (cost=0.15..8.17 rows=1 width=8) (actual time=0.018..0.019 rows=2 loops=1)
  Index Cond: (department = 'HR')
  Heap Fetches: 1
Planning Time: 0.096 ms
Execution Time: 0.050 ms

インデックスオンリースキャンイメージ

インデックスオンリースキャンの実行計画例

Index Only Scan using idx_department on employees  (cost=0.15..8.17 rows=1 width=4) (actual time=0.014..0.015 rows=2 loops=1)
  Index Cond: (department = 'HR')
  Heap Fetches: 0
Planning Time: 0.053 ms
Execution Time: 0.039 ms

インデックスオンリースキャンにできれば、

データファイルへのアクセスなしで該当データが取得できるため

余計なI/Oを減らせて性能向上が期待できます。

しかし、

インデックスを張っているカラム以外をselectすると、

インデックスオンリースキャンの効果を得られません。

その場合は、

カバリングインデックスの利用を検討しましょう。

インデックスのキーとしては利用しないもののselectしたいカラムを

include句に加えることでインデックスファイルの方にカラム

の情報を持たせられるため、

インデックスオンリースキャンにできます。

カバリングインデックス

CREATE INDEX idx_employees_covering ON employees (department) INCLUDE (salary);

他にも、

インデックスには部分インデックスや関数インデックスなどがあり、

状況に応じて使い分けが必要になります。

その辺りは「いろいろなインデックスとその使い分け」

というテーマで別記事にまとめようと思います。

プリペアド文による実行計画再利用

O/R Mapperを利用したアプリケーションの場合、

クエリはプリペアド文として発行され、

クエリとパラメータが分離していると思います。

これにより、

SQLインジェクションを回避でき、

実行計画を使いまわせるため

実行計画生成のコストを削減できます。

jooq(O/RMapper)でのクエリ発行例

-- jooqでの記述
Result<Record> result = dslContext
    .select()
    .from(MY_TABLE)
    .where(MY_TABLE.MY_COLUMN.eq("someValue"))
    .fetch();

-- PostgreSQLに発行されたプリペアドステートメント
PREPARE my_query (text) AS
SELECT *
FROM my_table
WHERE my_column = $1;

EXECUTE my_query('someValue');

パラメータ化されたクエリとパラメータを別々にPostgreSQL内部では取り扱います。

これによって繰り返し同じパラメータ化されたクエリが発行された場合、

生成済み実行計画をメモリ上に保持したまま使い回すことができ、

実行計画作成にかかるオーバヘッドを削減できます。

しかし、生成された実行計画は代表的な1つを使い回すことになるので、

データの偏りなどによりパラメータによっては最適な実行計画にならないことがあります。

その場合は実行計画を都度作るようにオプション指定してクエリを実行するという選択肢も検討しましょう。

実行計画の再利用を防止

SET plan_cache_mode = force_custom_plan;

テーブル・インデックスメンテナンス

PostgreSQLは追記型アーキテクチャのため、

テーブルやインデックスの更新を繰り返すと徐々に、

物理上のデータ配置順がバラついたり、

データ配置順に空きが出たりします。

そうなると、

実行計画は最適でも、無駄なI/Oが発生して

性能低下の原因になります。

そのためテーブルやインデックスを計画的に

メンテナンスする必要があります。

詳細はこちらの記事をご覧ください。

2023年12月3日テーブルをメンテナンスする〜PostgreSQLのきほん〜

2023年12月4日インデックスをメンテナンスする〜PostgreSQLのきほん〜

今回のテーマについては以上です。

現場で役立つデータベースの基礎知識という

まとめ記事を作ったので是非ご覧ください。

2022年3月21日初めてデータベースを触る方に向けて〜新人プログラマー時代の自分に伝えたいこと〜

参考文献

リンク

第16章　インデックスメンテナンス
［改訂3版］内部構造から学ぶPostgreSQL―設計・運用計画の鉄則

第18章　パフォーマンスチューニング
［改訂3版］内部構造から学ぶPostgreSQL―設計・運用計画の鉄則