PostgreSQLのEXPLAINではどのようにコストを見積もっているのか

新しいクエリを発行する機能をプロダクトに追加する際、クエリの実行計画を確認するのが一般的です。 PostgreSQL では EXPLAIN による実行計画ではクエリに必要なコスト情報を算出してくれます。

今回はこのコストがどういったロジックをもとに算出されているかを調べました。

EXPLAIN 実行計画を表示するコマンド

サンプルとして以下の EXPLAIN コマンドを実行します。

$ explain select * from users
    join user_basic_informations on users.id = user_basic_informations.user_id;

# 実行計画
Hash Join  (cost=4.55..28.62 rows=69 width=274)
  Hash Cond: ((users.id)::text = (user_basic_informations.user_id)::text)
  ->  Seq Scan on users  (cost=0.00..22.17 rows=717 width=110)
  ->  Hash  (cost=3.69..3.69 rows=69 width=164)
        ->  Seq Scan on user_basic_informations  (cost=0.00..3.69 rows=69 width=164)

実行計画では処理する単位のことを「ノード」と呼びます。上記の例では、Hash Join と Seq Scan などがノードです。

各項目の説明

各ノードには、cost, rows, width がそれぞれ表示されます。 以下の実行結果の一つを使って、それぞれの項目が何を表しているか説明します。

Seq Scan on users  (cost=0.00..22.17 rows=717 width=110)
  • cost: ノードで処理するクエリのコスト
  • rows: ノードが返却する行数
  • width: ノードが返却する１行あたりの平均的な行の長さ

このうちクエリのコストを判断するのに最も重要なのは costです。(名前の通りですが) cost を算出する際に、rows, width も計算式内で考慮されているためです。

cost=N.NN..M.MM

cost はN.NN..M.MMのように表示されます。これはそれぞれ始動コストと総コストを表します。

N.NNの部分は始動コストと呼ばれ、１件目のデータを返却できるまでにかかるコストを表します。

M.MMの部分は総コストと呼ばれます。総コストは全てのデータを返却するまでにかかるコストを表します。

cost=0.00..22.17の場合、始動コストは 0 で、総コストは 22.17 です。

シーケンシャルスキャンでは、始動コストは 0 になります。シーケンシャルスキャンはテーブルの先頭から順番にデータを読み込むため、始動コストはかかりません。 インデックススキャンの場合は先に Index を読み込み、その後にテーブルからデータを読み込むため、始動コストは 0 になりません。 また Hash Join や Sort では、データを返却する前にデータをソートしたり、ハッシュテーブルを作成する必要があるため、始動コストが他に比べ大きくなる傾向があります。

始動コストはあくまで１件目のデータを返却できるまでにかかるコストです。始動コストが大きいクエリでも、総コストが小さければ実行時間は短くなります。

したがって、クエリの負荷を判断する上で重要なのは総コストです。実行計画を見て、そのクエリがどれくらいコストがかかるクエリかを判断するためには、総コストをチェックすると良いでしょう。

どのようにコストを見積もっているのか

ディスクからデータを読み出し、返却できるまでにかかるコストが総コストと述べましたが、クエリを処理する際 PostgreSQL 内では複数の処理ステップが存在し、それぞれにコストがかかります。 例えば、ディスクからデータを読み取る処理コスト、データを CPU 上で処理するコスト、WHERE 句といった operator を処理するコストなどです。

これらの処理はそれぞれ必要なコストが異なるため、各処理ごとにコスト調整パラメータが存在します。 ディスクからシーケンシャルアクセスで 8KB のデータを読み込む時間のコストを 1.0 とし、他の処理にかかる時間を相対値として表現されています。

各コスト調整パラメータの詳細は公式ドキュメントに記載されています。20.7.2. プランナコスト定数

例を挙げると以下のようなパラメータがあります。

  • seq_page_cost: ディスクからシーケンシャルアクセスで 1 ページ分(8KB)のデータを読み込むために必要な時間コスト
  • cpu_tuple_cost: ヒープデータ１行あたりの CPU 処理にかかる時間コスト
  • cpu_index_tuple_cost: インデックスデータ１行あたりの CPU 処理にかかる時間コスト

再度、上記の実行計画を見てみましょう。

Seq Scan on users  (cost=0.00..22.17 rows=717 width=110)

シーケンシャルスキャンでデータを読み込むため、ディスク I/O のコスト(seq_page_cost=1.0)がかかります。 PostgreSQL ではページ単位でデータを読み取ります。コストを計算するためには、users テーブルの合計ページ数が必要です。 また読み取る行単位のデータ処理に必要な CPU コスト(cpu_tuple_cost=0.01)の和で算出することができます。

総コスト = (ディスクページ読み取り数 * seq_page_cost) + (スキャンした行 * cpu_tuple_cost)

以下のコマンドから users テーブルのページ数を確認できます。

$ SELECT relpages FROM pg_class WHERE relname = 'users';
relpages
15

relpages が 15 であるため、users テーブルの合計ページ数は 15 です。 上記の式に当てはめてみましょう。

総コスト = (15 * 1.0) + (717 * 0.01) = 22.17

計算結果と実行計画の総コストが一致していることが確認できました。 今回ではシーケンシャルスキャンのみを見てきましたが、インデックススキャンやハッシュジョインなども同様にコストを算出しています。

このように EXPLAIN では、実行されるクエリに必要な処理と、処理される行数を元にコストを算出しています。

まとめ

今回は PostgreSQL の EXPLAIN では、どのようにしてコストを算出しているかを紹介しました。 総コストはクエリに必要な処理と、処理される行数をもとに算出されます。これは当然ですが、運用していくことで読み取り対象の行数が増えるとコストも増えることを意味します。

初回の EXPLAIN の実行結果が安心できるものであったとしても定期的にチェックすることで、コストが増加していないかを確認できる仕組みをつくることが重要です。

参考文献