実行計画を取得して解析する〜PostgreSQLのきほん〜

はじめに

この記事では

発行したクエリは内部でどのように実行されるのか？？
実行計画をどのように取得してどう解析するのか？？
最適な実行計画が選択されないよくある原因は？？

についてまとめました。

PostgreSQLのスロークエリを現場でクエリチューニングする場面で役に立つ内容です。

発行したクエリは内部でどのように処理されるのか？？

発行したクエリが内部で処理されるイメージ

上の図において、

特に統計情報からプランナが実行計画を生成する箇所を押さえておきましょう！！

統計情報は具体的に以下の情報を保持しています。

列のデータのばらつき(分布)、列の最大・最小値・最頻値
行数、行データの平均サイズ

例えば、

行数の少ないテーブルへのクエリではインデックスを利用せず、

行数が多く列のデータがバラついている場合にはインデックスを利用するなど

プランナは統計情報を元に最適なデータアクセスを導き出します。

そのためクエリチューニングでは、

統計情報とテーブルの実態が乖離していないか？？

実行計画は思い通りのインデックスを利用しているのか？？

という観点でスロークエリの原因を特定していくことが多いです。

実行計画を取得して実行計画の構造を知る

実行計画は以下のコマンドで取得できます。

実行計画取得コマンド

-- クエリからプランナが作成した実行計画を表示
EXPLAIN SELECT * FROM ・・・・

-- 実際にクエリを発行して実行計画と実処理時間の両方を表示
EXPLAIN ANALYZE SELECT * FROM ・・・・

取得した実行計画例は以下です。

実行計画

-- 実行計画取得コマンド
EXPLAIN ANALYZE SELECT u.name, p.amount FROM users u JOIN purchases p ON u.id = p.user_id WHERE p.amount >= 100 ORDER BY u.name, p.amount;

-- 取得できる結果例
Sort  (cost=1000.00..1000.00 rows=1 width=100) (actual time=5.123..5.789 rows=1000 loops=1)
  Sort Key: u.name, p.amount
  Sort Method: quicksort  Memory: 103kB
  ->  Hash Join  (cost=100.00..900.00 rows=1 width=100) (actual time=0.123..3.789 rows=1000 loops=1)
        Hash Cond: (u.id = p.user_id)
        ->  Seq Scan on users u  (cost=0.00..10.00 rows=1000 width=50) (actual time=0.009..0.789 rows=1000 loops=1)
        ->  Hash  (cost=50.00..50.00 rows=1000 width=50) (actual time=1.234..1.234 rows=1000 loops=1)
              Buckets: 1024  Batches: 1  Memory Usage: 49kB
              ->  Index Scan using idx_purchases_amount on purchases p  (cost=0.13..50.00 rows=1000 width=50) (actual time=0.123..0.789 rows=1000 loops=1)
                    Index Cond: (amount >= 100.00)
Planning time: 0.123 ms
Execution time: 6.789 ms

上で取得した実行計画を解析していきます。

こんな感じでツリー構造の下から解析していきます。

実行計画の解析イメージ

実行計画の個々のノードについて詳しくみていきます。

スキャンノード

テーブルからデータを取り出すノードです。

シーケンシャルスキャン(Seq Scan)

③のようなSeq Scanはテーブルからデータを取り出す際、

テーブル全体を順番にスキャンします。

インデックススキャン(Index Scan)

①のようなIndex Scanはインデックスを利用して該当データを探して

該当のテーブルデータのみをスキャンします。

テーブルデータが少ない場合はどちらのスキャンを利用してもあまり差はないですが

(むしろインデックスを利用するオーバーヘッドの方が時間がかかるかも)

テーブルデータが多い場合はインデックスを利用した方が

目的のデータを探す際のディスクI/Oが減り高速になります。

ちなみに・・・

WHERE句でデータを絞り込む場合はスキャン系ノードでデータを取り出してからフィルタするイメージ

なのでシーケンシャルスキャンの時点で全テーブルデータへのアクセスが行われます

データ量が多いテーブルへ
シーケンシャルスキャン

データ量が多いテーブルへ
インデックススキャン

結合ノード

2つのテーブルのスキャン結果をインプットとして

それらの集合を求めて1つの結果を出力するノードです。

ハッシュ結合(Hash Join)

④のようなHash Joinは、

片方のテーブル(内側テーブル)をスキャンして結合キーでハッシュを作成し、

もう片方のテーブル(外側テーブル)の結合キーをスキャンしながら評価します。

ハッシュを作成する分のオーバーヘッドが発生するものの、

その後は高速に結合できます。

今回の例ではpurchasesテーブルの方が内側テーブルになっていますが、

内側テーブルはハッシュを作成してメモリ上に収まる小さなテーブルで、

外側テーブルが大きなテーブルの場合に有効です。

他にも結合ノードには種類があります。

入れ子ループ結合(Nested Loop)

片方のテーブル(外側テーブル)の1行ごとに

もう片方のテーブル(内側テーブル)を全てJoin Filterで評価します。

外側テーブルの行数が少なく、

内側テーブルにインデックスを張っている場合には高速です。

逆に外側テーブルの行数が多いほどループの回数が増え効率が悪く遅くなります。

マージ結合(Merge Join)

2つのテーブルをそれぞれ結合キーでソートしてから、

順番に付き合わせて評価します。

ソートがオーバーヘッドになりますが、

PostgreSQLでは主キーには必ずB-treeインデックスを貼るため、

結合キーが主キーやインデックスが貼られていれば事前にソート済みの結果が得られます。

大きなテーブル同士の結合で、

ハッシュがメモリに収まらないような場合に特に有効です。

それぞれの結合の特徴を理解して、

実行計画を確認してなぜそれが採用されたのか？？

実際のデータ量的にその選択は適切なのか？？

といった観点で実行計画を見れると良いと思います。

その他のノード

その他のノードとしては⑤のようなSortがあります。

ORDER BYや先ほど説明したMerge Joinの際にソートが行われます。

ソートはバックエンドプロセス毎に確保される作業メモリ上で行われます。

今回の例のようにメモリ上で実行できる場合(クイックソート)は問題ないのですが、

データ量が多くそれができない場合(外部ソート)はファイルに書き出すため

クエリが遅くなります。

この場合は作業メモリサイズのチューニングが必要になります。

実行計画の処理コストの見方を知る

ここまで見てきた実行計画はクエリを実行すると

統計情報をもとにいくつか作成されて、

処理コストの小さなものが採用されています。

ではコストについてみていきましょう。

表示項目	項目名	説明
cost=N.NN..M.MM	始動コスト..総コスト	1件目のデータを返却できるようになるまでにかかるコスト.. 全てのデータを返却するまでにかかるコスト
width=N	行長	ノードが返却する1行あたりの平均の行の長さ
rows=N	行数	ノードが返却する行数
actual time=N.NNN..M.MMM	始動時間..総実行時間	1件目のデータを取得するまでにかかった時間(ミリ秒).. 全てのデータを取得するまでにかかった時間
actual rows=N	行数	ノードが返却した行数
actual loops=N	ノード実行回数	ノードの実行回数

実行計画の表示項目

シーケンシャルスキャン

        ->  Seq Scan on users u  (cost=0.00..10.00 rows=1000 width=50) (actual time=0.009..0.789 rows=1000 loops=1)

シーケンシャルスキャンのようにノード実行のための事前準備が不要な場合始動コストは0です。

インデックススキャン

              ->  Index Scan using idx_purchases_amount on purchases p  (cost=0.13..50.00 rows=1000 width=50) (actual time=0.123..0.789 rows=1000 loops=1)

逆にインデックススキャンは事前にインデックスを検索する必要があり始動コストがかかります。

ソートやハッシュなどのノードも事前に全てのデータを読む必要があるため始動コストがかかります。

実行計画は階層構造なので、

下位ノードのコストは自動的に上位ノードに加算されます。

コストを見る際、

どのノードでどれくらいコストが増えたのか？？

(どのノードで一番コストがかかっているのか？？)

を見てスロークエリの原因を特定していったりします。

始動コスト、総コストはノード毎に計算式があり、

計算式に利用するコスト調整パラメータを変更することで

コスト見積もりをチューニングできます。

また、コスト算出には統計情報が利用されるため、

統計情報の精度が重要になります。

統計情報はテーブルデータをサンプリングして作成していますが、

サンプリング数を増やして精度を上げることもできます。

適切な実行計画が選択されない原因

スロークエリを調査していると適切な実行計画が選択されていない場合があります。

いくつか原因を見ていきましょう。

SSD利用時のコスト調整パラメータチューニング

先ほど少し触れましたがコスト調整パラメータの設定が必要なケースがあります。

PostgreSQLでコスト算出する際、

デフォルト設定だとディスクがHDDの前提で

ディスクをシーケンシャルにアクセスするときに1ページ分(8kb)を読み込むためのコストと

ディスクをランダムにアクセスするときに1ページ分を読み込むためのコストの比は

1:4で設定されています。

しかし、SSDの場合はコストの差がほぼなく実質1:2程度になります。

SSDを利用しているのに設定をデフォルトのままにしていると、

本来インデックスを利用した方がコストが低いのに、

インデックスを利用してランダムアクセスが発生するためコストが高くなり、

インデックスを利用しない実行計画が採用されたりします。

統計情報の取得頻度と粒度をチューニング

バッチ処理などで短時間に大量のデータが追加されたり、

大量のトランザクションを処理した後は統計情報と実データに

乖離が発生する可能性があります。

また、統計情報のサンプリング数がデフォルトで3万件で、

仮に数億件のデータ量だとサンプリング数が小さすぎて、

実データとの乖離が発生する可能性があります。

統計情報の精度を上げたり、

手動で統計情報を取得したりすることで

統計情報を実態に近づけ最適な実行計画が選ばれるようにする必要があります。

また、

スロークエリを報告するPostgreSQLの機構を利用して、

監視ソフトでデータベースの状態を把握することが重要です。

PREPAREコマンド

アプリケーションでjooqなどのORMを利用して以下のようにクエリを発行すると、

jooqは内部でパラメータ化されたクエリを生成して発行します。

(WHERE句に条件を指定する際にプリペアドステートメントを使用するともいう)

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

Result<Record> result = dslContext
    .select()
    .from(MY_TABLE)
    .where(MY_TABLE.MY_COLUMN.eq("someValue"))
    .fetch();

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

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

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

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

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

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

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

今回の内容は以上です。

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

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

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

参考文献

リンク

第17章　実行計画の取得／解析
［改訂3版］内部構造から学ぶPostgreSQL―設計・運用計画の鉄則