はじめに

実行計画を知っていますか？

私はデータベースのパフォーマンス改善を担当することになり、そこで初めて実行計画の存在を知りました。今回はそのとき学んだことをまとめます。

※この記事はPostgreSQLを前提としています。

実行計画とは

実行計画とは、DBがSQL文を「どのように実行するか」を示した計画書です。

PostgreSQLには「プランナー（オプティマイザ）」という仕組みがあります。プランナーはSQL文を受け取ると、複数の実行方法を検討し、最も効率的と判断した方法を選びます。その結果が実行計画です。

実行計画はEXPLAINコマンドで確認できます。

例えば、住民情報を管理するresidentsテーブルがあり、住民IDで検索するケースを考えます。

EXPLAIN SELECT * FROM residents WHERE resident_id = 12345;

実際の実行時間も見たい場合はEXPLAIN ANALYZEを使います。

EXPLAIN ANALYZE SELECT * FROM residents WHERE resident_id = 12345;

実行計画には「cost」という数値が表示されます。

Seq Scan on residents  (cost=0.00..1234.00 rows=100 width=50)

このコストは、PostgreSQLがその処理にどれくらいの負荷がかかるかを見積もった値です。単位は「ディスクページ読み取り1回分」を基準にしています。

コストが大きいほど重い処理です。ただし、あくまで見積もりなので、実際の実行時間とは異なることがあります。そのためEXPLAIN ANALYZEで実測値も確認するのがおすすめです。

実行計画でよく見るスキャン方式は3つあります。

テーブルの全行を先頭から順にスキャンします。インデックスを使わない方式です。データ量が多いと非常に遅くなります。

インデックスを使って対象行を特定し、その後テーブル本体からデータを取得します。Seq Scanより高速です。

インデックスだけでデータを取得できる場合に使われます。テーブル本体へのアクセスが不要なため、Index Scanよりさらに高速です。

実行計画を見てSeq Scanが出ていたら、インデックスが効いていない可能性があります。

複数テーブルをJOINするとき、PostgreSQLは3つの結合方式から選択します。

一方のテーブルを1行ずつ読み、もう一方のテーブルから該当行を探します。片方のテーブルが小さく、もう一方にインデックスがある場合に有効です。ただし、両方のテーブルが大きいと処理回数が爆発的に増えます。

両方のテーブルを結合キーでソートしてから、順番に突き合わせます。大量データ同士の結合で有効ですが、事前のソートにコストがかかります。

一方のテーブルからハッシュテーブルを作り、もう一方のテーブルを1行ずつ照合します。等価結合（=での結合）で有効です。メモリを多く使いますが、大量データの結合に向いています。

どの方式が選ばれるかは、データ量やインデックスの有無によって変わります。想定外の方式が選ばれて遅くなることもあるので、実行計画で確認することが大切です。

プランナーは「統計情報」をもとに実行計画を決めています。この統計情報が古いと、プランナーが誤った判断をすることがあります。

例えば、100万件のデータを投入した直後でも、統計情報が「1000件しかない」と認識していると、Seq Scanが選ばれてしまうことがあります。

統計情報を更新するにはANALYZEコマンドを使います。

ANALYZE residents;

大量のデータを投入した後は、必ずANALYZEを実行する習慣をつけておくとよいです。

実行計画を読めるようになると、「なぜ遅いのか」が見えるようになります。

最初は難しく感じますが、まずはSeq Scanを探すところから始めてみてください。それだけでも改善のヒントが見つかることがあります。