| [ホーム] -> [Aache + PHP + PostgreSQL 実験室] |
SQL は非常に強力ですが、複雑な SQL 文を実行したときに、想像以上に時間がかかったことがあるかと思います。これは、PostgreSQL が、設計者・開発者の意図したとおりに実行されていないかもしれません。いや、もちろん、結果は正しい結果が返ってきていると思いますが（返っていないとしたらたぶん開発したプログラムのバグですよ）、その結果を求めるまでに、最適な道順を通ってないかもしれません。
大抵チューニングが必要なのは、select 文なので、ここでは select 文を中心に話を進めます。
SQL は、実行時に PostgreSQL サーバに渡され、PostgreSQL サーバがどのようにデータにアクセスしたらよいかを考えます。例えば次のような SQL 文があったとしましょう。
select 社員名称 from 社員マスタ where 年齢 <= 20 and 入社年度 = 2001
こういう場合、年齢と入社年度で絞り込むわけですが、どちらから絞り込んだ方が効率的でしょうか。
例えば、社員が 10,000 人いて、そのうち年齢が 20 才以下の人が 5 人だったとします。2001 年度入社の人は 3,000 人いました。この場合は、まず年齢で絞り込んでから、入社年度で絞り込んだ方が効率的です。
全データを検査するとした場合、年齢で絞り込むまでに 10,000 件のアクセスが必要ですが、そこから入社年度で絞り込むには 5 件アクセスすればいいのです。もし逆だとすると、入社年度を絞り込むまでに 10,000 件で、そこから年齢で絞り込むのに 3,000 件のアクセスが必要になります。10,005 対 13,000 なので、年齢か絞り込んだ方が効率的なのが分かります。
PostgreSQL はこの様に絞り込んでくれているでしょうか。それは分かりません。だって、どちらの条件を先に解釈した方が効率的なのかは、知らないからです。
この絞り込む順序や、テーブルを結合する場合の結合の順序などを合わせて、データベースの世界では一般に「実行計画（アクセスパス）」と呼んでいます。そして、このアクセスパスを決定するプログラム（ロジック）を「プランナ（オプティマイザ）」と呼んでいます。
従って、SQL のチューニングとは、いかにプランナに効率的な実行計画を選択させるかがポイントになります。まずは、PostgreSQL のプランナの動きから見ていきましょう。
ここでは、説明をするためのサンプルとして、SQL 文（create table）とサンプルデータを用意しました（あまりいいサンプルじゃないですが）。サンプルをダウンロードするときに、Excel とかが開いてしまう場合は、リンクをクリックして、「リンク先を保存」とかしてください。
３つを同じディレクトリに置き、psql -f sqlpower.sql とかするとテーブルが作成され、サンプルデータがロードされます。テーブルは、personal（個人情報）と state（県）テーブルの二つですが、それぞれ名前の後ろに、_n, _p, _na, _pa が付いている４種類が作成されるので、すべてで８つのテーブルが作成されます。
_n は、プライマリーキーの付いていないテーブル、_p はプライマリーキーの付いているテーブル、_na はプライマリーキーが付いていないけど ANALYZE されているテーブル、_pa はプライマリーキーが付いていて ANALYZE されているテーブルとなります。
それではテストに入りますが、プランナがどういう実行計画を立てたか調べる方法があります。explain という文を使います。
=> explain select * from personal_n; Seq Scan on personal_n (cost=0.00..20.00 rows=10000 width=53) EXPLAIN
これにより、プランナが personal_n テーブルにアクセスするとき、どのようなアクセスを行うかが分かります。Seq Scan on personal_n は、personal_n テーブルに対して、Seq Scan することが分かります。この Seq Scan とは、テーブルの全データ対して順番に検索していく方法です。また、カッコの中の情報では、SQL を実行するに当たっての大まかなコスト（cost=0.00..20.00 の部分）、結果として返される見積もり行数（rows=10000 の部分）、１行の見積もり幅（width=53 の部分）が分かります。
PostgreSQL（や他の RDBMS）は、普通の状態では、テーブルにどのようなデータが入っているかは関知していません。あるいは、データを入れておくファイルにまだ空きがあるかどうかを把握していますが、そのデータが何件存在しているかまでは把握していないのが普通です。そこで、ANALYZE の出番です。
前に、PostgreSQL では、vacuum analyze 文により、統計情報を採ると説明しました。これにより、テーブルに何件データが入っているか、そのデータの分布の状態（拡散しているかどうかという意味で、例えば男女のカラムは拡散していないと思いますが、名前はある程度拡散しているでしょう）などを調べ、システムカタログに記録しておきます。プランナはこの値を見てコストを決めていますが、この統計情報がないと、正確な予測が出来なくなります。
統計情報が活用された例を見てみましょう。
wwwdb=> explain select * from personal_n where type = 'F'; NOTICE: QUERY PLAN: Index Scan using personal_n_idx_type on personal_n (cost=0.00..158.82 rows=50 width=53) EXPLAIN wwwdb=> explain select * from personal_na where type = 'F'; NOTICE: QUERY PLAN: Seq Scan on personal_na (cost=0.00..214.00 rows=4967 width=30) EXPLAIN
type カラムには、F, M のどちらかが入っていますが、インデックスも張られています。ANALYZE されていない personal_n は、インデックスがあるのでインデックスを使おうとしています（Index Scan using ...）。しかし、実際のデータは、インデックスを使うほど拡散していません。ANALAYZE されている personal_na テーブルは、インデックスを使用しないで全件走査をするようになっています（Seq Scan on ...）。
プランナを賢くするには、統計情報（ANALYZE）が重要だと言うことが分かって頂けたと思います。
ちなみに補足しておくと、インデックスを使うと、まずインデックスを見てから、そのインデックスに書かれている実データのアドレスを取得し、実データを見に行きます。インデックスを使わない場合、実データを直接見ます。したがって、インデックスを使うと、「インデックスを見に行く」という動作が増えます。この例のようにインデックスを使ってもデータを限定できない場合、全件走査した方が速くなります。
もしこの様な結果になっているのであれば、この type カラムに張ったインデックスは完全に不要です。インデックスがあるとデータの更新は遅くなります（実データ以外にインデックスデータも更新しないといけないから）。select で十分インデックスの能力を発揮できないのであれば、インデックスはいりません。
wwwdb=> explain select * from personal_n where name = 'abc'; NOTICE: QUERY PLAN: Index Scan using personal_n_idx_name on personal_n (cost=0.00..158.82 rows=50 width=53) EXPLAIN wwwdb=> explain select * from personal_na where name = 'abc'; NOTICE: QUERY PLAN: Index Scan using personal_na_idx_name on personal_na (cost=0.00..5.99 rows=1 width=30) EXPLAIN
name カラムは、名前が入っていて、このデータは十分拡散しています（このデータはランダムに作ったので、もしかしたら重複は一つもないかもしれません）。したがって、インデックスを有効に使われるようになります。しかしこれが like 検索だったらどうでしょう
wwwdb=> explain select * from personal_na where name like 'abc%'; NOTICE: QUERY PLAN: Seq Scan on personal_na (cost=0.00..214.00 rows=1 width=30)
どうやらインデックスは使わないようですね・・・。ちなみに正規表現検索も使わないようです。つまり、like 検索している SQL 文がどんなに遅くても、インデックスを張っても意味がないと言うことです。この様に、本当にそのインデックスが使われているかを調べてください。PostgreSQL の場合、以外と使ってくれません。
プランナが本当に苦労する点に、テーブル結合があります。とてつもなく遅い SQL は、ほぼ間違いなくテーブル結合に時間がかかっています。
多くのデータベースのプランナにとって、テーブル結合は苦手とする処理です。特に、結合するテーブル数が多かったり、N 対 N の結合であったりした場合です。サンプルテーブルでどのようにプランナが実行計画を建てるか見てみましょう。
サンプルは、personal テーブルと state テーブルの結合で、本来であれば、personal テーブルを中心に結合するのが正しい選択です。でも、これがなかなか選択できないようで、プランナが苦労している様子が分かります。
まずは、インデックス（プライマリーインデックス）の無いテーブル同士の結合です。from で指定するテーブルの順番を変えてみました。
wwwdb=> explain select * from personal_n p, state_n s where p.state = s.statecode;
NOTICE: QUERY PLAN:
Merge Join (cost=1125.16..1777.66 rows=50000 width=105)
-> Sort (cost=1055.33..1055.33 rows=10000 width=55)
-> Seq Scan on personal_n p (cost=0.00..194.00 rows=10000 width=55)
-> Sort (cost=69.83..69.83 rows=1000 width=50)
-> Seq Scan on state_n s (cost=0.00..20.00 rows=1000 width=50)
EXPLAIN
wwwdb=> explain select * from state_n s, personal_n p where p.state = s.statecode;
NOTICE: QUERY PLAN:
Merge Join (cost=1125.16..1777.66 rows=50000 width=105)
-> Sort (cost=69.83..69.83 rows=1000 width=50)
-> Seq Scan on state_n s (cost=0.00..20.00 rows=1000 width=50)
-> Sort (cost=1055.33..1055.33 rows=10000 width=55)
-> Seq Scan on personal_n p (cost=0.00..194.00 rows=10000 width=55)
EXPLAIN
この場合は、from で先に指定したテーブルを中心に結合しようとしているのが分かります。
次は、プライマリーインデックスのあるテーブル同士の結合です。
wwwdb=> explain select * from personal_p p, state_p s where p.state = s.statecode;
NOTICE: QUERY PLAN:
Merge Join (cost=1055.33..1259.83 rows=10000 width=105)
-> Index Scan using state_p_pkey on state_p s (cost=0.00..52.00 rows=1000 width=50)
-> Sort (cost=1055.33..1055.33 rows=10000 width=55)
-> Seq Scan on personal_p p (cost=0.00..194.00 rows=10000 width=55)
EXPLAIN
wwwdb=> explain select * from state_p s, personal_p p where p.state = s.statecode;
NOTICE: QUERY PLAN:
Merge Join (cost=1055.33..1259.83 rows=10000 width=105)
-> Index Scan using state_p_pkey on state_p s (cost=0.00..52.00 rows=1000 width=50)
-> Sort (cost=1055.33..1055.33 rows=10000 width=55)
-> Seq Scan on personal_p p (cost=0.00..194.00 rows=10000 width=55)
EXPLAIN
この場合は、どちらも state テーブルを中心に結合しようとしています。なぜこうしてしまったのか分かりませんが、残念ながら、これでは逆です。これについてもう少し調べて見ます。
wwwdb=> explain select * from personal_n p, state_p s where p.state = s.statecode;
NOTICE: QUERY PLAN:
Merge Join (cost=1055.33..1259.83 rows=10000 width=105)
-> Index Scan using state_p_pkey on state_p s (cost=0.00..52.00 rows=1000 width=50)
-> Sort (cost=1055.33..1055.33 rows=10000 width=55)
-> Seq Scan on personal_n p (cost=0.00..194.00 rows=10000 width=55)
EXPLAIN
wwwdb=> explain select * from personal_p p, state_n s where p.state = s.statecode;
NOTICE: QUERY PLAN:
Merge Join (cost=1125.16..1777.66 rows=50000 width=105)
-> Sort (cost=1055.33..1055.33 rows=10000 width=55)
-> Seq Scan on personal_p p (cost=0.00..194.00 rows=10000 width=55)
-> Sort (cost=69.83..69.83 rows=1000 width=50)
-> Seq Scan on state_n s (cost=0.00..20.00 rows=1000 width=50)
EXPLAIN
wwwdb=> explain select * from state_n s, personal_p p where p.state = s.statecode;
NOTICE: QUERY PLAN:
Merge Join (cost=1125.16..1777.66 rows=50000 width=105)
-> Sort (cost=69.83..69.83 rows=1000 width=50)
-> Seq Scan on state_n s (cost=0.00..20.00 rows=1000 width=50)
-> Sort (cost=1055.33..1055.33 rows=10000 width=55)
-> Seq Scan on personal_p p (cost=0.00..194.00 rows=10000 width=55)
EXPLAIN
wwwdb=> create index personal_p_idx9 on personal_p (state);
CREATE
wwwdb=> explain select * from personal_p p, state_n s where p.state = s.statecode;
NOTICE: QUERY PLAN:
Merge Join (cost=69.83..1232.33 rows=50000 width=105)
-> Index Scan using personal_p_idx9 on personal_p p (cost=0.00..510.00 rows=10000 width=55)
-> Sort (cost=69.83..69.83 rows=1000 width=50)
-> Seq Scan on state_n s (cost=0.00..20.00 rows=1000 width=50)
EXPLAIN
wwwdb=> drop index personal_p_idx9;
DROP
どうやら、where 句の条件で、インデックスがある方のテーブルを中心に結合されるようですね。２番目で、personal を中心に結合したのは、両方ともインデックスがないため、先に指定されたテーブルを中心にしたからでしょう。その証拠に３番目では逆になっています。しかし、４番目でインデックスを personal_p.state に張ると personal テーブルを中心に結合を行います。
次は、ANALYZE されたテーブル同士の結合です。プライマリーインデックスが無いテーブル同士と、プライマリーインデックスがあるテーブル同士を結合させてみました。
wwwdb=> explain select * from personal_na p, state_na s where p.state = s.statecode;
NOTICE: QUERY PLAN:
Hash Join (cost=1.60..370.60 rows=10000 width=44)
-> Seq Scan on personal_na p (cost=0.00..194.00 rows=10000 width=32)
-> Hash (cost=1.48..1.48 rows=48 width=12)
-> Seq Scan on state_na s (cost=0.00..1.48 rows=48 width=12)
EXPLAIN
wwwdb=> explain select * from personal_pa p, state_pa s where p.state = s.statecode;
NOTICE: QUERY PLAN:
Hash Join (cost=1.60..370.60 rows=10000 width=44)
-> Seq Scan on personal_pa p (cost=0.00..194.00 rows=10000 width=32)
-> Hash (cost=1.48..1.48 rows=48 width=12)
-> Seq Scan on state_pa s (cost=0.00..1.48 rows=48 width=12)
EXPLAIN
どちらも personal テーブルを中心に結合しています。ANALYZE により、正しい結合順番が判別できています。
次に、検索条件を追加した場合に、ANALYZE しているかどうかで違ってくるかを見てみます。
wwwdb=> explain select * from personal_p p, state_p s where p.state = s.statecode and name = 'abc';
NOTICE: QUERY PLAN:
Hash Join (cost=160.78..198.91 rows=50 width=105)
-> Seq Scan on state_p s (cost=0.00..20.00 rows=1000 width=50)
-> Hash (cost=160.66..160.66 rows=50 width=55)
-> Index Scan using personal_p_idx_name on personal_p p (cost=0.00..160.66 rows=50 width=55)
EXPLAIN
wwwdb=> explain select * from personal_pa p, state_pa s where p.state = s.statecode and name = 'abc';
NOTICE: QUERY PLAN:
Nested Loop (cost=0.00..8.07 rows=1 width=44)
-> Index Scan using personal_pa_idx_name on personal_pa p (cost=0.00..5.99 rows=1 width=32)
-> Seq Scan on state_pa s (cost=0.00..1.48 rows=48 width=12)
EXPLAIN
ANALYZE をしている方は、適切に実行計画が建てられていますが、ANALYZE してない方は困りものです。name カラムの指定により、返ってくる件数は非常に少ないので、personal テーブルで絞り込んでから state テーブルを結合した方が全然いいはずです。しかし、ANALYZE をしていない方は、state を中心に結合してしまっています。
ちなみに、結合時に state テーブルが全件走査（Seq Scan）されているのは、このテーブルのデータ量が少ないためだと思われます。実際、次のようにデータ量の多いテーブルを結合するときには、インデックスが利用されています。
wwwdb=> explain select * from personal_pa p1, personal_pa p2 where p1.pno = p2.code and p1.name = 'abc'; NOTICE: QUERY PLAN: Nested Loop (cost=0.00..9.01 rows=1 width=64) -> Index Scan using personal_pa_idx_name on personal_pa p1 (cost=0.00..5.99 rows=1 width=32) -> Index Scan using personal_pa_pkey on personal_pa p2 (cost=0.00..3.01 rows=1 width=32) EXPLAIN
一応分かったことをまとめると、次のような感じでしょうか。
=」条件の時利用される
テーブル結合は、ANALYZE していなくても、インデックスを付けることである程度コントロールで着るみたいですが、はっきり言ってそのためにインデックスを作るのはナンセンスです。
インデックスは、複数あるデータの中から、素早く目的のデータを見つけるための仕組みです。それをプランナをコントロールするために作ったり削除したりするべきではありません。不必要なインデックスは、データ更新時に負荷をかけるだけです。
結局、一番重要なのは ANALYZE を実行する ことだと分かります。
もちろん、ANALYZE は定期的に実行しないといけません。データの更新を行うと、ANALYZE によって収集された統計情報と、実際のデータに差が出てきてしまいます。毎晩とか、毎週実行する必要があります。
そのため、運用の負担が増えるとも主張する人もいます。しかし、はっきり言って、たいした手間ではありませんよ。シェルプログラムなら、数行ですみます（少し凝っても 20 - 30 行でしょう）。cron にでも登録しておけばいいだけです。大して時間がかかりません。たぶん、よほど大きいデータベースでも、数十分で終わるでしょうし、その間ロックがかかるわけでもないので、普通にデータベースを使って手問題ありません。確認の手間があるというかもしれませんが、ANALYZE に失敗することはまずないでしょう。ANALYZE に失敗したら、そもそもデータベースに致命的なトラブルが発生しているはずです。
運用の手間より、これを使うことによる恩恵の方が遙かに大きいものです。１時間かかっても終わらなかった処理が、ANALYZE しただけで、３分で終わるようになったこともある、なんて言ったら信じられますか？ 実際この程度のことは起こりえます（何度か経験しています）。テーブル結合した SQL 文が遅かったら、実行計画が予想通りか確認してみてください。そして、ANALYZE してみてください。
ちなみに、PostgreSQL の場合、ANALYZE してもおかしかったら、explain と格闘しながら調整していくしかありません。Oracle にあるオプティマイザヒントのようなものはありません。この場合join を使って、結合順序を明確に指定します。
select * from a cross join b, c, d, e where ... という指定をすれば、まず初めにに a と b を結合されます（c, d, e の順番はプランナが勝手に決める）。a と b の結合によりデータが絞り込まれ、時間が短縮できることが分かっている場合は、とても有効です。
チューニングとは、一部を速くするのが目的ではなく、トータルで速くするのが目的です。一番のネックで、一番効果がありそうなところから手を付けるべきです。もしかしたら、OS のパラメータを変更した方がいいかもしれません。ディスクアクセスが遅いのなら、hdparm -d1 -X66 /dev/hda とかしてみるとか（Linux の場合）。
PostgreSQL には、プランナの動きを変えるためのパラメータが用意されています。postgresql.conf で設定します（あるいは、接続するときのオプションや、set 文で）。
この設定項目について説明することは無理ですが（私の能力的に）、極限までチューニングが必要な場合は、これらの値を変更してみてください。でも、そこまでする前に、本当にその SQL が正しいか、必要かどうかを検討する方がいいような気もします。確かにこの手のチューニングをすると、全体的なパフォーマンスが上がりますが、「劇的」に上がることはまずありません。それより、SQL 文を組み立て直した方が、遙かに「劇的」に速くなる場合があります。
一つだけ、今までの説明で関連する部分があるので補足しておきます。
テーブル結合の際に、11 個以上のテーブルを結合させようとすると、実行計画の精度が下がります。これは、プランナが結合の組み合わせを検討するのに、無視できないほど時間がかかってくるため、精度を下げて実行時時間を短くしようとしているからです。この 11 という闘値は、geqo_threshold で設定されています。
例えば、a テーブルを中心に結合した方がよいと分かっている場合は、select * from a,b,c where a.id = b.id and b.ref = c.id という書き方より、select * from a join (b join c on (b.ref = c.id)) on (a.id = b.id) という書き方の方が実行計画を建てるのに、時間がかからないとマニュアルにあります。３つ程度では無視できる時間の差かもしれませんが、たくさん結合するときは、覚えておくといいかもしれません。