by Hayato Matsuura.

実行計画の作成


PostgreSQLの実行計画は、SQL文の前にexplainを置く事によって取得できます。しかし、1つ重要な制限事項があります。 バインドパラメータを使ったSQL文 ($1$2など)においては、この方法では実行計画を取得できません。まず最初にプリペアド ステートメントとして宣言する必要があるのです。

PREPARE stmt(int) AS SELECT $1

PostgreSQLでは、バインドパラメータに「$n」を使用する事に 注意しましょう。使用しているデータベース抽象化層によってはこれを隠してしまう事があるので、SQL標準で定義されているように、 クエスチョンマークを使いましょう。

プリペアドステートメントは以下のように実行します。

EXPLAIN EXECUTE stmt(1)

PostgreSQL 9.1までは、prepareの呼び出しと同時に作成され、executeで与えられる実際の値は考慮に入れていませんでした。PostgreSQL 9.2からは、実行まで実行計画の作成を遅らせるため、バインドパラメータに割り当てられた実際の値を考慮に入れるようになりました。

注記

バインドパラメータなしの文に関しては、もっと単純な説明になります。

EXPLAIN SELECT 1

この時、オプティマイザはクエリの計画時に実際の値をいつも考慮に入れます。 9.1以前のPostgreSQLを使用していて、プログラム内にバインドパラメータがある場合、バインドパラメータありでexplainを使っても、同じ実行計画を取得できます。

実行計画の出力は次のようになります。

                QUERY PLAN                
------------------------------------------
 Result  (cost=0.00..0.01 rows=1 width=0)

出力は、この本で取り上げているOracleの実行計画とほぼ同じです。 処理名(「Result」)、関連するコスト値、行数の見積、行の長さです。

PostgreSQLは2つのコスト値を表示するのに注意しましょう。 1つ目は初期処理のコスト、2つ目は全行を取り出し終えるまでの処理の全コストです。 Oracleの実行計画では、2つ目の値しか表示されていません。

PostgreSQLのexplainコマンドには 2つのオプションがあります。VERBOSEオプションは、完全なテーブル名など追加の情報も取得するものですが、 通常は特に役立つ情報はありません。

協力してください

この記事が気に入ったら、私の書いた本「SQLパフォーマンス詳解」や私によるトレーニングもきっと気にいるはず。

もう1つのオプションがANALYZEです。 これは広く使われてはいますが、何も考えずにいつもこれを付けるのはお薦めしません。このオプションを付けると、実際に文を実行してしまうからです。 select文の時は影響はあまりないでしょうが、insertupdatedeleteの時はデータを変更してしまいます。 うっかりデータを更新してしまうリスクを回避するために、トランザクション内で実行し、最後にロールバックするようにしましょう。

警告

explain analyzeは、対象が insertupdatedeleteの場合でも文を実際に実行してしまいます。

ANALYZEオプションでは、文を実行し、実際の実行時間と 行数を記録します。この機能は、カーディナリティの見積(行数見積)がおかしい場合の原因調査に役立ちます。

BEGIN
EXPLAIN ANALYZE EXECUTE stmt(1)
                   QUERY PLAN
--------------------------------------------------
 Result  (cost=0.00..0.01 rows=1 width=0)
         (actual time=0.002..0.002 rows=1 loops=1)
 Total runtime: 0.020 ms
ROLLBACK

この表示はページに合うようにフォーマットしてあります。実際には「actual」の値は見積値と同じ行に表示されます。

行数は見積と実際の値、どちらの部分にも表示されています。 これによって、カーディナリティの見積のおかしな部分をすぐに見つけられます。

最後に大事な事ですが、プリペアドステートメントはクローズしておきましょう。

DEALLOCATE stmt

著者について

Markus Winandの写真

Markus Winand氏は、開発者がSQLパフォーマンスを改善するお手伝いをしています。彼は、SQL Performance Explainedの 著者でもあり、出張トレーニングhttp://winand.at/での リモート講義も 行っています。

彼の本

カバー『SQLパフォーマンス詳解』

核心をわかりやすく 解説。

Markusから購入します
(送料無料+PDF)

Amazonで購入
(印刷版のみ)

Do not use offset for pagination

Learn why

Visit my sibling!A lot changed since SQL-92!

Use The Index, Luke のカップは

ステッカー、コースター、本、コーヒーマグ。 学習に必要なものすべて。

今すぐ購入

Connect with Markus Winand

Markus Winand on LinkedInMarkus Winand on XINGMarkus Winand on Twitter
“Use The Index, Luke!” by Markus Winand is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
法律上の通知 | お問い合わせ | 無保証 | 商標 | プライバシーとGDPR | CC-BY-NC-ND 3.0 license