PostgreSQLの実行計画を読めるようになりたい Part2(番外編)
今日が仕事納めでした。
年末年始は、怠惰の限りを尽くします。
今回もPostgreSQLの実行計画とかいろいろ見ていく予定でしたが、、ある疑問が浮かびそれを調べた内容を書いていきます。
Part2にしていきなり番外編です。
ふと、疑問に思った
この前、PostgreSQLの実行計画で遊んでいたら、ふと疑問に思いました。
まずは次のような、特に何の変哲もないクエリを考えます。
SELECT col1, col2 FROM simple_table01 WHERE col2 = 1 ORDER BY col1 DESC
ここでcol2 = 1がヒットする行数は1000件、col1はPKです。
このクエリを効率化するのであれば、次のようなインデックスがあれば十分だと考えていました。
CREATE INDEX idx_col2 ON simple_table01 (col2)
実際にこのクエリの実行計画を取ると、下記の結果が得られました。
db01=# EXPLAIN ANALYZE SELECT col1, col2 FROM simple_table01 WHERE col2 = 1 ORDER BY col1 DESC; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------- Sort (cost=1543.65..1568.75 rows=10037 width=8) (actual time=8.418..9.196 rows=10000 loops=1) Sort Key: col1 DESC Sort Method: quicksort Memory: 853kB -> Bitmap Heap Scan on simple_table01 (cost=114.08..876.54 rows=10037 width=8) (actual time=0.570..4.078 rows=10000 loops=1) Recheck Cond: (col2 = 1) Heap Blocks: exact=637 -> Bitmap Index Scan on idx_col2 (cost=0.00..111.57 rows=10037 width=0) (actual time=0.475..0.476 rows=10000 loops=1) Index Cond: (col2 = 1) Planning Time: 0.152 ms Execution Time: 9.817 ms
僕はこの実行計画を見た瞬間に、疑問を覚えました。
どこが疑問なのか？ なぜ、疑問なのか？
どこに疑問を覚えたか？それはずばり、次の部分です。
Sort (cost=1543.65..1568.75 rows=10037 width=8) (actual time=8.418..9.196 rows=10000 loops=1) Sort Key: col1 DESC Sort Method: quicksort Memory: 853kB
そう、このクエリではソートが実行されているのです。
なぜこれに対して、疑問を覚えたかというと僕が普段MySQLを使っていたからです。
さきほど、上記のクエリに対して「(col2)というインデックスがあれば十分だと思った」と書きましたが、それはMySQLではセカンダリインデックスにPKカラムが含まれるからです。
つまり、(col2)というインデックスであれば、暗黙的に(col2, col1)というインデックスとして構成されているということです。
インデックスにPKの値が含まれるため、上記のようなクエリではインデックス経由で取得された値はすでにPK値でソートされていたはずなのです！
PostgreSQLのインデックス構造って
てっきりMySQL以外のDBMSでも、インデックスの構造は同じだと思っていましたが違いました。
公式ドキュメントを漁っていたら、次のような記述がありました。
リーフページはツリーの最下階層にあるページです。 各リーフページはテーブルの行を指すタプルを含みます。
またこちらのサイトにも、画像付きでそのことが示されています。
つまり、PostgreSQLはMySQLのようなインデックス構造をしていないので、MySQLで使えた暗黙的なインデックスの使い方はできないということですね。
実験してみる
じゃあ、明示的にそれらのカラムをインデックスに含めれば、ソートが実行されないんでしょうか。
CREATE INDEX idx_col2_col1 ON simple_table01 (col2, col1)
上記のインデックスを作成した上で、再度実行計画を取りました。
db01=# EXPLAIN ANALYZE SELECT col1, col2 FROM simple_table01 WHERE col2 = 1 ORDER BY col1 DESC; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------- ---------- Index Only Scan Backward using idx_col2_col1 on simple_table01 (cost=0.29..287.94 rows=10037 width=8) (actual time=0.039..2.984 rows=10000 loops=1) Index Cond: (col2 = 1) Heap Fetches: 0 Planning Time: 0.238 ms Execution Time: 3.670 ms
期待通り、ソートが実行されずIndex Only Scanが表示されました！
まとめ
というわけで、PostgreSQLのインデックスの構造は、MySQLのそれとは微妙に異なるものだと分かりました。
色々調べているうちに、カバリングインデックス(INCLUDE)や可視性マップなど、知らない概念が出てきたのでこれらも追々調べていきたいと思います。
