インデックスを作成して，SQLã®é€Ÿåº¦ã‚’ãƒãƒ¥ãƒ¼ãƒ‹ãƒ³ã‚°ã™ã‚‹æ‰‹é †ã€€ï¼ˆPostgreSQLで，EXPLAIN文とCREATE INDEX文によるパフォーマンス改善）

以下の５ステップで，適切なインデックスを作成し，SQLを高速化できる。

• （１） パフォーマンスを改善すべきSQL（もしくはカラム）を特定
  • （１−１） ログを閲覧し，実行秒数の大きいものを抽出する。
  • （１−２） 統計テーブルを閲覧し，よく利用されるテーブルを特定する。

• （２） 該当SQLのプランやコストを確認

• （３） 該当カラムに対してインデックスを作成

• （４） インデックスが作成されたことを確認

• （５） SQLのプランやコストが改善されたことを確認

• 補足

※↑ もくじジェネレータ で自動生成

DBはPostgreSQLを想定。

（１）パフォーマンスを改善すべきSQL（もしくはカラム）を特定

まず，インデックスを作成すべきカラムを見極める。

その方法は２つある。

（１−１）ログを閲覧し，実行秒数の大きいものを抽出する。

SQLの実行ログを閲覧する。

たとえば，Ruby on Railsなら，error_levelを :debug にすれば，実行した全SQLとその実行時間が記録される。

PostgreSQLパフォーマンスチューニングのために　〜重いSQLの見つけ方〜
http://blog.asial.co.jp/383

• postgresql.confの中で，閾値としてlog_min_duration_statementを設定し，重いSQLのみログ出力する

実行時間の大きいSQLや，実行回数の多いSQLを探す。

↑目次へ戻る

（１−２）統計テーブルを閲覧し，よく利用されるテーブルを特定する。

アプリケーションがログを出力しなくても，PostgreSQLには「稼働統計情報ビュー」というものがある。

それを見れば，どのテーブルが頻繁に参照されているか識別できる。

テーブルの稼動統計情報を表示するSQL：

SELECT
	*
FROM
	pg_stat_user_tables
;

PostgreSQLのシステムテーブル入門　（暗記用のSQL集）
http://language-and-engineering.hatenablog.jp/entry/20100220/p1

• 各テーブルがスキャンされた（読み取られた）回数，データが挿入された回数なども合わせて表示される。
• どのテーブルに CREATE INDEX すべきか，否か？といった判断にも活用できるはず。

参照回数の多いテーブルを特定したら，そのテーブル内のカラムに対する高速化を試みるべきだとわかる。

それらのカラムに対して発行されているSQLを，アプリ内から抽出する。

↑目次へ戻る

（２）該当SQLのプランやコストを確認

前項で，高速化すべきSQLが特定できた。

実際の高速化に入る前に，まず，そのSQLに対する「コスト」を算出する。

（高速化を施す前後で「コスト」を比較すれば，高速化作業にどれぐらい効果があったのか分かるから。）

コスト算出のためには，EXPLAIN文（またはEXPLAIN ANALYZE文）を使う。

書式：

EXPLAIN 〜； -- 〜の部分にはSELECT文が入る

EXPLAIN文を実行すると，SQLの「実行プラン」が表示される。

詳細な見方は下記を参照。

Explaining Explain 〜 PostgreSQLの実行計画を読む 〜 (PDF版)
http://lets.postgresql.jp/documents/t...

サンプルとして考える状況：

• 注文テーブルと商品テーブルがある。
• 注文の外部キーとして商品IDがある。（ol_i_id = i_id）
• 注文番号が123であるような注文情報を，関連する商品情報と関連付けて閲覧したい。

--プランを表示する
EXPLAIN
SELECT i_id, i_title, i_publisher, i_cost,
       ol_qty, ol_discount, ol_comments
  FROM item, order_line
 WHERE ol_o_id = 123
   AND ol_i_id = i_id
;


                               QUERY PLAN
-------------------------------------------------------------------------
 Merge Join  (cost=167.41..172.51 rows=7 width=215)
   Merge Cond: ("outer".ol_i_id = "inner".i_id)
   ->  Sort  (cost=22.58..22.59 rows=6 width=102)
         Sort Key: order_line.ol_i_id
         ->  Seq Scan on order_line  (cost=0.00..22.50 rows=6 width=102)
               Filter: (ol_o_id = 123::numeric)
   ->  Sort  (cost=144.83..147.33 rows=1000 width=126)
         Sort Key: item.i_id
         ->  Seq Scan on item  (cost=0.00..95.00 rows=1000 width=126)

読み方：

• 注文テーブルから条件付 (ol_o_id = 123::numeric)でレコードを取得。（５〜６行目）
  • それを，商品ID(order_line.ol_i_id）カラムでソートする。（３〜４行目）・・・A
• 商品テーブルから全レコードを取得。（９行目）
  • それを，商品ID（item.i_id）カラムでソートする。（７〜８行目）・・・B
• AとBをマージして結合する。（１〜２行目）
  • マージ条件は，商品IDの一致("outer".ol_i_id = "inner".i_id)。

SQLと同じで，プランも，ネストの内側から外側に向かって処理が走る。

SQLの出典：

SQLチューニング(2) 〜 実行コスト(EXPLAIN / EXPLAIN ANALYZE）
http://www.postgresql.jp/blog/71

以下は，詳細な読み方。

「Merge Join」とは，テーブルの結合方式のこと。

SQLの中で「JOIN」していなくても，内部的には「JOIN」を使って結合処理される場合がある。

上記のSQLも，「JOIN」と明示的に書いてなくても，内部的にはJoinを使ってデータを処理している。

http://itpro.nikkeibp.co.jp/members/I...

• 入れ子結合方式(nested loop join)は，データが小さい場合に向いている
• マージ・ジョイン(merge join)方式は，データ量が多い場合に向いている
• ソートメモリーが十分にある場合はハッシュ・ジョイン(hash join)が早い

http://d.hatena.ne.jp/tgk/20081225/12...

• IN句やEXISTS句は，実際には内部ではJOINとして処理される場合がある
• ここで出てくるのは，ハッシュ結合による「セミジョイン」

「cost」とは，SQLの実行にかかる手間を表現した数値。

PostgreSQLは，SQLを受け取ったら，いちばん手間の少ない処理方式を自動的に探してくれる。（オプティマイザ）

いろいろな処理方式を考えて列挙し，それらのパターンごとに「手間」を算出し，もっとも「手間」の少ないパターンが最終的に選ばれる。

costの算出方法は，以下のページ参照。

EXPLAINのマニュアル
http://www.postgresql.jp/document/pg8...

• コスト＝（ディスクページ読み取り x seq_page_cost）+（スキャンした行 x cpu_tuple_cost）

「rows」「width」は，返却されるレコードに関する情報。

（スライド）PostgreSQL チューニング SQL編
http://www.slideshare.net/kwappa/2009...

• costの単位は時間ではなく，オプティマイザがプラン選択する際の指標である
• rowsは返却されるレコード数の推定値
• widthは返却されるレコードの長さ（データサイズ）
• seq scan撲滅を目指せ
• 不要ソートを撲滅せよ

「Filter」とは，行に対する条件のこと。

WHERE句は，「どの行を結果に含めるか・含めないか」というフィルタの役割をする。

「EXPLAIN」のかわりに
「EXPLAIN ANALYZE 〜」とした場合，SQLの実行にかかる推定秒数が出る。

もし，インデックス作成後に，EXPLAIN ANALYZEで表示される秒数が少なくなれば，

インデックス作成に効果があった事を客観的に判定できる。

↑目次へ戻る

（３）該当カラムに対してインデックスを作成

特定済みのカラムに対して，インデックスを作成する。

それらのカラムに対する検索処理が，素早くなる。

CREATE INDEX インデックス名 ON テーブル名 ( カラム名 );

WHERE句内に複数のカラムがAND条件で結合している場合，複合インデックス：

CREATE INDEX インデックス名 ON テーブル名 ( カラム名1, カラム名2 );

インデックスの削除方法：

DROP INDEX インデックス名;

PostgreSQL【インデックス】
http://kichon.net/wiki/wiki.cgi?page=...

PostgreSQLチューニング実践テクニック：高度なインデックスの活用
http://thinkit.co.jp/free/marugoto/2/...

↑目次へ戻る

（４）インデックスが作成されたことを確認

前項のCREATE INDEX文が成功したことを確認する。

DB内のインデックスを一覧表示するSQL：

--インデックス名と，インデックス対象のカラム番号を表示

SELECT
	relname, indkey
FROM
	pg_index pgi
		LEFT JOIN
	pg_class pgc
		ON
	pgi.indexrelid = pgc.oid
;

groovyとPostgreSQLでインデックスを一覧表示する
http://groovyarekore.blogspot.com/200...

• psqlにはコマンドがあるが，SQLで簡単に行なうのは難しい

PostgreSQLでのテーブルインデックスの列挙
http://wiki.jyo.jp/index.php?pg_%A5%A...

pg_indexのマニュアル
http://www.postgresql.jp/document/pg8...

• indkeyはこのインデックスがどのテーブル列をインデックスとしているかを示す。
• たとえば、1 3 は 1 番目と 3 番目のテーブル列がインデックスキーとなっていることを示す。

pg_classのマニュアル
http://www.postgresql.jp/document/pg8...

• pg_class カタログはテーブルと、その他に列を保有しているもの、あるいはテーブルに似たすべてのものを目録にしている。

↑目次へ戻る

（５）SQLのプランやコストが改善されたことを確認

インデックスが意図通りに機能していることを確認する。

再度EXPLAIN：

                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..47.08 rows=7 width=215)
   ->  Index Scan using order_line_pkey on order_line  (cost=0.00..17.07 rows=6 width=102)
         Index Cond: (ol_o_id = 123::numeric)
   ->  Index Scan using item_pkey on item  (cost=0.00..4.99 rows=1 width=126)
         Index Cond: ("outer".ol_i_id = item.i_id)

ちゃんとインデックスが利用されている。

また，コストや秒数も，インデックス作成前と比べて減っていることを確認する。

もし減っていなかったら，そのインデックスを削除。

インデックス作成方法を変えたり，対象カラムを変えたりして，再度インデックスを作成し直す。

↑目次へ戻る

補足

その他の高速化方法：

PostgreSQLを高速化する16のポイント
http://neta.ywcafe.net/000960.html

• 100ミリ秒単位でページ表示を遅らせるA/Bテスト（条件を変えて2つのサービスを同時に公開するテスト）で、非常に小さな遅延ですら、収入に大きく響いてくる

運用中にインデックスを再構築したい場合：

CREATE INDEXを使いこなす
http://d.hatena.ne.jp/matsuou1/200903...

• 夜間等、使用されない時間帯が決まっている場合、定期的にメンテナンスの時間が確保できる場合は、reindexを行う

関連する記事：

DBの「トランザクション分離レベル」が必要な理由　 （PostgreSQLで，ファントム・リードを防止すべきサンプル事例）
http://language-and-engineering.hatenablog.jp/entry/20110104/p1

データベースとSQLの業務スキルレベル　判別表　（５段階）
http://language-and-engineering.hatenablog.jp/entry/20110320/p1

PREPARE文と，PL/pgSQL の入門　　（PostgreSQLで「動的に」SQLを実行するために，プリペアド・クエリやストアドファンクションを定義しよう）
http://language-and-engineering.hatenablog.jp/entry/20110218/p1

「相関サブクエリ」とは何かを理解して，複雑なSQLでも読めるようになろう
http://language-and-engineering.hatenablog.jp/entry/20101108/p1