最近PostgreSQLを触る機会が増えてきたので、PostgreSQLの実行計画を読めるようになりたい・・・

まずはシンプルなクエリから実行計画を取得して、一つずつ理解していきたいと思います。

最終目標はPostgreSQLのクエリチューニングができるレベルまで。

準備
実行計画を見ていく
検証① インデックスを追加してみる
検証② 複数のインデックスを使う場合
- 赤枠の部分
- 青枠の部分
検証③ 複合インデックスを使ってみる
最後に
参考にさせていただいたサイト

準備

テーブル作成

今回使うのは、シンプルなテーブルです。

CREATE TABLE s01.simple_table01(
  col1 int not null,
  col2 int not null,
  col3 int not null,
  col4 int not null,
  col5 int not null,
  PRIMARY KEY(col1)
);

最低限、col1は主キーとします。

インデックスはまだ作成しません。

データの準備

あまりにも小さいテーブルだと、インデックスの恩恵が分かりにくいのでとりあえず10万件のデータを準備します。

INSERT INTO
  simple_table01(col1, col2, col3, col4, col5)
SELECT
  generate_series(1, 100000),
  generate_series(1, 100000)%10,
  generate_series(1, 100000)%100,
  generate_series(1, 100000)%1000,
  generate_series(1, 100000)%10000
;

かなり作為的なデータですが、そこはご愛嬌。

ところで、PostgreSQLのgenerate_series()めちゃくちゃ便利ですよね、MySQLにも欲しい。（MySQLでも似たようなことできるけど、シンプルにできるのっていいよね）

使用するクエリ

今回使用するクエリは下記の通りです。

SELECT COUNT(*) FROM simple_table01 WHERE col2 = 1 AND col3 = 11;

分かりやすくしたいので、非常にシンプルな形にしました。

実行計画を見ていく

それではさっそく上記のクエリを使って、実行計画を見ていきます。

EXPLAINとEXPLAIN ANALYZE

MySQLもそうですが、PostgreSQLにはEXPLAINとEXPLAIN ANALYZEがあります。

両者の違いは実際にそのクエリを実行するかどうかです。

通常のEXPLAINはあくまで、その選択をするであろう実行計画です。

一方でEXPLAIN ANALYZEは実際にそのクエリを実行して、その時に選ばれた実行計画を示します。

EXPLAIN ANALYZEは実際にクエリを実行するので、更新系のクエリの実行計画を取る時は注意が必要です。

両者のメリット・デメリットを考えてみます。

EXPLAIN
- メリット：手軽に実行できる、結果がすぐわかる
- デメリット：統計値に影響されやすい、実際にその実行計画が選ばれているとは限らない
EXPLAIN ANALYZE
- メリット：実際にクエリを実行させて実行計画を得るので正しい結果が取得できる*1
- 実行に時間のかかるクエリの実行計画の取得が大変。更新系のクエリに対して取得する際は、ひと工夫が必要(少なくとも本番環境では使えないよね…)*2

実行計画を取得する

今回は正確な内容を知りたいので、EXPLAIN ANALYZEを使って実行計画を取得します。

db01=# EXPLAIN ANALYZE SELECT COUNT(*) FROM simple_table01 WHERE col2 = 1 AND col3 = 11;
                                                      QUERY PLAN                                                      
----------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=2137.24..2137.25 rows=1 width=8) ①(actual time=21.700..21.701 rows=1 loops=1) ②
   ->  Seq Scan on simple_table01  (cost=0.00..2137.00 rows=97 width=0) ①(actual time=0.030..21.527 rows=1000 loops=1) ②
         Filter: ((col2 = 1) AND (col3 = 11)) ③
         Rows Removed by Filter: 99000④
 Planning Time: 0.077 ms ⑤
 Execution Time: 21.743 ms ⑥
(6 行)

時間: 22.350 ミリ秒

PostgreSQLの実行計画はツリー形式で表現されます。

僕はMySQLを最初に触ったので、個人的にこのツリー形式は慣れませんｗ(一般的にツリー形式の方が分かりやすいはずなんだけど…)

EXPLAINの見方

実行計画を取ったところで、意味が分からなければ意味がありません。

上記の実行計画を例に、何が書いてあるのか確認してみます。

①

cost

その名の通り、その操作に必要なコスト。左側の数値が初期コスト、右側の数値が実行コストというように分かれているらしい。とりあえず小さいほど望ましいという認識でいいのかな？

rows

読み込む行数の推定値。推定値なので実際の値とかけ離れていることがある。その場合はVACUUM/ANALYZEする必要がある。MySQLのrowsとほぼ同じものだという認識。

width

取得される行の平均サイズ。あまり重要なものではないらしい。

②

actual time

そのクエリを実行するのにかかった実際の時間。

当たり前だけど、通常のEXPLAINでは表示されない。

また、ここにあるrowsは推定値ではなく実際に取得された行数となる。

loops

処理の繰り返し実行回数。

はて？繰り返し実行回数？？？

よく分からないので、とりあえず1以外の数字が出てきたら、詳しく調べてみようと思います。

JOINとかサブクエリを実行すると変ってくるのかな？

③

Filter

インデックスによるデータ取得後に、評価される条件。

今回の例だとcol2 = 1 AND col3 = 11という条件は、テーブルをフルスキャンした後に評価されます。

④

Removed by Filter

上述したFilterによってどれくらいの行が捨てられたかを表す。

この数字が大きければ大きいほど、インデックスが非効率的、無駄な行を読み込んでいるということが言えそう。ただし、EXPLAINでは表示されません。

MySQLだとRows_sentとRows_examinedの比率を考えて、そのクエリが効率的か考えてたけど、PostgreSQLではここを見るとよさそう。

⑤

Planning Time

実行計画を作成するのにかかった時間。

通常のEXPLAINでは表示されない。

⑥

Execution Time

クエリの実行にかかった時間。

通常のEXPLAINでは表示されない。

先ほどの実行計画を読み解く

今までの事を踏まえて、さきほどの実行計画を再度眺めてみます。

まず赤枠の部分に注目します。

Seq Scan on simple_table01とありますね。これはこのテーブルに対して、インデックスを用いず全件走査を行っているということです。

すなわち、テーブルフルスキャンですね。MySQLの実行計画では、type列にALLと表示されます。

この操作は一般的にコストが大きく、望ましくない実行計画です。

次に青枠の部分を見てみましょう。

まずは青枠内の1行目、Filter: ((col2 = 1) AND (col3 = 11)とあります。

先ほども言ったとおり、これはインデックスによるデータ取得後に評価される条件です。

続いて2行目を見るとRows Removed by Filter: 99000とあります。

つまり、この条件により99000行が捨てられたということです。

これらをまとめてみると、このクエリは

simple_table01に対してフルスキャンを実行
1で取得した結果に対して、col2 = 1 AND col3 = 11を評価
2で評価した結果をクライアントに返す

という処理をされているということが分かりました。

検証① インデックスを追加してみる

それではここでインデックスを追加して、実行計画がどのように変わるか観察してみましょう。

CREATE INDEX idx_col2 ON simple_table01 (col2)

インデックスが追加されました。

db01=# \d simple_table01;
              テーブル"s01.simple_table01"
  列  | タイプ  | 照合順序 | Null 値を許容 | デフォルト 
------+---------+----------+---------------+------------
 col1 | integer |          | not null      | 
 col2 | integer |          | not null      | 
 col3 | integer |          | not null      | 
 col4 | integer |          | not null      | 
 col5 | integer |          | not null      | 
インデックス:
    "simple_table01_pkey" PRIMARY KEY, btree (col1)
    "idx_col2" btree (col2)

この状態で同じクエリに対して、実行計画を取得してみます。

 Aggregate  (cost=897.88..897.89 rows=1 width=8) (actual time=4.085..4.085 rows=1 loops=1)
   ->  Bitmap Heap Scan on simple_table01  (cost=111.09..897.64 rows=97 width=0) (actual time=0.657..3.924 rows=1000 loops=1)
         Recheck Cond: (col2 = 1)
         Filter: (col3 = 11)
         Rows Removed by Filter: 9000
         Heap Blocks: exact=637
         ->  Bitmap Index Scan on idx_col2  (cost=0.00..111.07 rows=9970 width=0) (actual time=0.437..0.437 rows=10000 loops=1)
               Index Cond: (col2 = 1)
 Planning Time: 0.081 ms
 Execution Time: 4.111 ms

お、結果が変わりましたね。

とりあえず実行時間は22msから4msほどになりました。

一番深い場所から順番に見てみましょう。

赤枠の部分

Bitmap Index Scan on idx_col2およびIndex Cond: (col2 = 1)と表示されていますね。

とりあえず作成したidx_col2が利用されていることが分かります。

このインデックスにより、1万行読み込まれているということが(actual time... rows=10000)から分かりました。

ちなみにPostgreSQLではビットマップインデックス自体を、ユーザー側で作成することはできません。*3あくまでもクエリ実行時に最適化手法として、内部でビットマップが作られるだけのようです。

青枠の部分

Heap Blocks: exact=637とありますが、ここではとりあえずスルーして次に行きましょう。

緑枠の部分

Filter: (col3 = 11)とRows Removed by Filter: 9000の部分はもう大丈夫です。

インデックスを使ってcol2 = 1という条件でデータ取得した後に、col3 = 11という条件を評価して、その結果9000行が捨てられたということですね。

exactモードとlossyモード

一番上にRecheck Cond: (col2 = 1)ってありますね。前のステップでcol2 = 1の評価は行われているはずなのに再度チェックする必要があるのでしょうか。

この部分と青枠の部分はセットで考える必要があるそうです。ちょっと寄り道になりますが、簡単に調べてみました。

PostgreSQLではBitmap Index Scanを実行すると、そこで取得した結果をビットマップに格納します。その際に、exactとlossyの2つのモードが用いられます。（exactモードが優先されます）

exact = TID*4をビットマップに格納する
lossy = 該当するタプルが格納されているページ番号をビットマップに格納する

exactモードの場合は、TIDがビットマップに格納されています。この場合、Recheck処理は不要です。

その一方でlossyモードはページ単位の情報という大雑把な情報しか含まれていないので、そのページからさらにどの行が条件を満たすのか再度チェックする必要があります。この処理がRecheck処理に該当します。

この2つのモードはどのように使い分けられるかというと、ずばり作成されたビットマップがすべてwork_memに乗り切るかどうかで決まります。

作成されたビットマップがwork_memよりも大きい場合は、exactモードからlossyモードへ移行されます。*5

lossyモードはページ番号をビットマップに格納するため、使用するメモリ容量が少ない代わりに、Recheck処理が走るためexactモードよりも性能が劣ります。

実際にexactモードかlossyモード、どちらが使用されているかは青枠部分を見れば分かります。(実際に使われたモードを確認するので、EXPALIN ANALYZEでしかこの情報は表示されない)今回の場合は、exactと表示されているのでexactモードが使用されていることが分かりました。*6

橙枠の部分

Bitmap Heap Scan on simple_table01とありますね。先ほどはSeq Scanでした。

ビットマップは複数のインデックスを利用するときのみに使われていると思ったのですが、今回のような単一の条件であってもビットマップを作成するんですね。

検証② 複数のインデックスを使う場合

さらなる検証のために、次のようなインデックスを追加してみます。

CREATE INDEX idx_col3 ON simple_table01 (col3)

これでsimple_table01には2つのインデックスができました。

db01=# \d simple_table01;
              テーブル"s01.simple_table01"
  列  | タイプ  | 照合順序 | Null 値を許容 | デフォルト 
------+---------+----------+---------------+------------
 col1 | integer |          | not null      | 
 col2 | integer |          | not null      | 
 col3 | integer |          | not null      | 
 col4 | integer |          | not null      | 
 col5 | integer |          | not null      | 
インデックス:
    "simple_table01_pkey" PRIMARY KEY, btree (col1)
    "idx_col2" btree (col2)
    "idx_col3" btree (col3)

この状態で再度実行計画を取ってみましょう。

予想すると、idx_col2とidx_col3それぞれのインデックスを使ってデータを取得し、その後に作成されたビットマップに対してANDが行われそうです。

次のような実行計画が取得できました。

db01=# EXPLAIN ANALYZE SELECT COUNT(*) FROM simple_table01 WHERE col2 = 1 AND col3 = 11;
                                                              QUERY PLAN                                                              
--------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=385.50..385.51 rows=1 width=8) (actual time=0.907..0.908 rows=1 loops=1)
   ->  Bitmap Heap Scan on simple_table01  (cost=122.99..385.26 rows=97 width=0) (actual time=0.557..0.809 rows=1000 loops=1)
         Recheck Cond: ((col3 = 11) AND (col2 = 1))
         Heap Blocks: exact=637
         ->  BitmapAnd  (cost=122.99..122.99 rows=97 width=0) (actual time=0.413..0.413 rows=0 loops=1)
               ->  Bitmap Index Scan on idx_col3  (cost=0.00..11.62 rows=977 width=0) (actual time=0.114..0.114 rows=1000 loops=1)
                     Index Cond: (col3 = 11)
               ->  Bitmap Index Scan on idx_col2  (cost=0.00..111.07 rows=9970 width=0) (actual time=0.282..0.282 rows=10000 loops=1)
                     Index Cond: (col2 = 1)
 Planning Time: 0.121 ms
 Execution Time: 0.945 ms
(11 行)

時間: 1.560 ミリ秒

実行時間は1.5msとさらに高速化されています。

赤枠の部分

先ほどと同じく、部分ごとに内容を確認していきます。

まずは一番深い場所にある、赤枠の部分から。

Bitmap Index Scanが2行あることから、予想通り作成した2つのインデックスがそれぞれ使われていることが分かります。今までMySQLを触っていた身としては、なかなか新鮮です。*7

それぞれのインデックスを使って取得されたデータに対して、上の行でBitmapAndとあることから、それらをANDしていることが分かります。

青枠の部分

2行目にRecheck Cond:とありますが、3行目にexactとあるので今回はRecheck処理は走ってないようです。

最終的に赤枠の部分で作成されたビットマップから、必要なデータをスキャンします。

検証③ 複合インデックスを使ってみる

検証の最後に、複合インデックスを使ってみます。（結果は想像できるけど）

念のため先ほど作成した2つのインデックスは削除しておきましょう。（PostgreSQLってinvisible index使えないのね…）

DROP INDEX idx_col2
DROP INDEX idx_col3
CREATE INDEX idx_col2_col3 ON simple_table01 (col2, col3)

複合インデックスのみ、ある状態となりました。

db01=# \d simple_table01;
              テーブル"s01.simple_table01"
  列  | タイプ  | 照合順序 | Null 値を許容 | デフォルト 
------+---------+----------+---------------+------------
 col1 | integer |          | not null      | 
 col2 | integer |          | not null      | 
 col3 | integer |          | not null      | 
 col4 | integer |          | not null      | 
 col5 | integer |          | not null      | 
インデックス:
    "simple_table01_pkey" PRIMARY KEY, btree (col1)
    "idx_col2_col3" btree (col2, col3)

この状態で再度実行計画を取ってみます。

db01=# EXPLAIN ANALYZE SELECT COUNT(*) FROM simple_table01 WHERE col2 = 1 AND col3 = 11;
                                                           QUERY PLAN                                                            
---------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=290.67..290.68 rows=1 width=8) (actual time=0.965..0.966 rows=1 loops=1)
   ->  Bitmap Heap Scan on simple_table01  (cost=5.41..290.39 rows=109 width=0) (actual time=0.195..0.867 rows=1000 loops=1)
         Recheck Cond: ((col2 = 1) AND (col3 = 11))
         Heap Blocks: exact=637
         ->  Bitmap Index Scan on idx_col2_col3  (cost=0.00..5.38 rows=109 width=0) (actual time=0.060..0.060 rows=1000 loops=1)
               Index Cond: ((col2 = 1) AND (col3 = 11))
 Planning Time: 0.315 ms
 Execution Time: 0.986 ms

想像通り、idx_col2_col3が使われたシンプルなビットマップスキャンのクエリになりました。

最後に

というわけで、PostgreSQLのEXPLAINに慣れるため、最初はシンプルなクエリで実行計画を取得してみました。

次はもう少し複雑な条件だったり、JOINやサブクエリがあるクエリを実行してみようと思います。

参考にさせていただいたサイト

今回のブログ執筆にあたり、以下のサイトを参考にさせていただきました。

ありがとうございます。

SQLチューニング入門入門編

PostgreSQLクエリ実行の基礎知識～Explainを読み解こう～

CLUSTERによる性能改善 - 夜は寝る

Bitmap Index Scan の後の Bitmap Heap Scan でRecheck処理が行われることの解説 - ぱと隊長日誌

*1:ただし、その内容はあくまでも、その時実行計画を取得した時の結果である。データの変更、インデックスの変更等で実行計画は常に変わる恐れがある

*2:更新クエリを参照クエリに変更する、スナップショットを取得し別環境に復元するなど方法がある

*3:Oracleではビットマップインデックスを作成できる。MySQLには無い

*4:タプルの識別子

*5:この移行はページ単位で行われるため、exactモードとlossyモードは共存する

*6:Recheckは動的に行われるためexactモードでも表示されるが、今回の場合実際にRecheckは実行されていない

*7:MySQLでは原則的に1テーブルに対して使われるインデックスは1つのみ

三流エンジニアの落書き帳

さあ、今日も未知という扉を開けてみよう

PostgreSQLの実行計画を読めるようになりたい Part1

準備

テーブル作成

データの準備

使用するクエリ

実行計画を見ていく

EXPLAINとEXPLAIN ANALYZE

実行計画を取得する

EXPLAINの見方

先ほどの実行計画を読み解く

検証① インデックスを追加してみる

赤枠の部分

青枠の部分

緑枠の部分

exactモードとlossyモード

橙枠の部分

検証② 複数のインデックスを使う場合

赤枠の部分

青枠の部分

検証③ 複合インデックスを使ってみる

最後に

参考にさせていただいたサイト