非常に高機能なオープンソースのデータベース「PostgreSQL」を使用したシステムでは、高機能であるが故に誤ったクエリーを多用され無駄にパフォーマンスを低下させるケースが多いようです。
このコラムでは、そんな誤ったSQL文とその改善方法をご説明します。
チューニングのポイント
- explain analyzeを使用してSQLのベンチマークを行う。
- インデックスの有効活用。
- max()、count()などの関数に注意。
- limitの活用。
- その他のポイント
explain analyzeを使用してSQLのベンチマークを行う
弊社の経験では、パフォーマンスに問題が生じるのは、運用開始後しばらくたってからのことです。
多くの場合、開発時点ではテスト用データの件数が実運用時の件数よりも圧倒的に少なく、十分にSQL文の検証が行われないまま実運用に入ってしまい、パフォーマンスに問題が生じるというパターンが多いのが実情です。
しかしながら、データ件数の少ない状態でも開発時点でSQL文にベンチマークを行えば、パフォーマンスに関する問題を事前に察知することが可能です。
ベンチマークの方法
ベンチマークは、psqlコマンドでデータベースに接続した後、実際に実行したいSQL文の前にexplain analyzeをつけて実行してやることで可能です。
EXPLAIN ANALYZE select ac_id from tbl_access_log order by ac_id desc limit 1 offset 0;
上記の例では、select以降（小文字の部分）が実際のSQL文です。
結果は、以下の通りとなります。
Limit (cost=0.00..0.52 rows=1 width=4) (actual time=0.490..0.502 rows=1 loops=1)
-> Index Scan Backward using tbl_access_log_pkey on tbl_access_log (cost=0.00..15603.39 rows=30083 width=4) (actual time=0.448..0.448 rows=1 loops=1)
Total runtime: 0.675 ms
(3 rows)
出力内容の全てをご説明することはここでは避けますが、Total runtime: 0.675 msというのが、クエリーを実行する際にかかる時間、Index Scan…という記述が「インデックスが使用されましたよ」という意味です。
このIndex Scanの記述の当たりに、Seq Scanという記述がある場合は、インデックスが使われておらず全てのデータを検索したということであり、実運用に入った際にパフォーマンスが低下する原因となります。
インデックスの有効活用
パフォーマンスの問題が発生するシステムの多くは、インデックスが十分に活用されていません。
せっかく設定してあるのに、SQL文が適切でないためにインデックス自体が使用されていない場合や、インデックスの設定が不十分であるケースが多く見受けられます。
インデックスが適切に設定されたデータベースは、目次から目的のページを探すのと同じようにダイレクトに目的のデータを探すことができます。
一方で、インデックスが適切に設定されていないシステムは、索引の無い辞書から知りたい単語を探すのと同じで、目的のデータであるかどうかを確認するために全てのデータを検索対象とします。
初心者プログラマの多くは、SQL文中のWHERE句に指定するフィールドにのみインデックスを設定しますが、これでは不十分です。
実際には、PostgreSQLは、Order BYに指定する列などに対してもインデックスを使用してくれます。
SQL文例
EXPLAIN ANALYZE select * from tbl_access_log order by ac_os;
インデックスを使っていない場合
Sort (cost=76542.51..76618.37 rows=30345 width=2018) (actual time=3610.709..4261.375 rows=30345 loops=1)
Sort Key: ac_os
-> Seq Scan on tbl_access_log (cost=0.00..1413.45 rows=30345 width=2018) (actual time=0.229..819.466 rows=30345 loops=1)
Total runtime: 5538.514 ms
インデックスを使った場合
Index Scan using tbl_access_log_ac_os on tbl_access_log (cost=0.00..15781.13 rows=30345 width=2018) (actual time=0.771..736.737 rows=30345 loops=1)
Total runtime: 1262.557 ms
上記の結果からクエリーに要する時間が1/4に減ったことがわかって頂けるとおもいます。
ちなみに、この例で使用したSQLでは、＊（アスタリスク）を使用していますが、アスタリスクを使用せず、必要なフィールドのみを指定してやれば、飛躍的なパフォーマンス向上を得られます。
max()、count()などの関数に注意
今回の例で使用しているテーブルは、某地方公共団体向けのCMS内のアクセスログの保存用のテーブルです。
このテーブルには現時点で３万件のデータが保存されていますが、これは実運用下では数日で到達するデータ数です。
アクセスログのようなシステムでは、総アクセス数などのように、しばしばデータの件数が必要となります。
じつは、このSQL文にも多くのプログラマが陥る罠があります。
count()やmax（）は使うべからず
select count(*) from tbl_access_log;
上記のSQL文は、専門書等で普通に解説されているクエリーですが、これは多くの場合間違いです。
ベンチマークの結果を見てみましょう。
Aggregate (cost=1489.32..1489.32 rows=1 width=0) (actual time=1171.802..1171.815 rows=1 loops=1)
-> Seq Scan on tbl_access_log (cost=0.00..1413.45 rows=30345 width=0) (actual time=0.277..624.437 rows=30345 loops=1)
Total runtime: 1172.176 ms
count関数は、全てのデータを単純に見に行きますので、インデックスは一切使われません。
したがって、このままでは、データが増えるに従ってSQLの実行時間も長くなります。
この場合は、このテーブルに主キーフィールドとしてオートナンバーの列を追加してやり、その最大値を求めれば、パフォーマンスが向上します。
しかし、ここにも罠があります。
select max(ac_id) from tbl_access_log;
実は、PostgreSQLはmax()を使用した場合にも、すべてのデータを見にいってしまいます。
Aggregate (cost=1489.32..1489.32 rows=1 width=4) (actual time=1188.998..1189.015 rows=1 loops=1)
-> Seq Scan on tbl_access_log (cost=0.00..1413.45 rows=30345 width=4) (actual time=0.599..632.781 rows=30345 loops=1)
Total runtime: 1189.337 ms
正しいSQL文は以下の通りです。
select ac_id from tbl_access_log order by ac_id desc limit 1 offset 0;
上記の例では、単純に最大値をとるという意味ではなく、ac_idを逆順にソートしてその１行目だけをとるという方法で、総アクセス数を取得しています。
その結果は。。。
Limit (cost=0.00..0.52 rows=1 width=4) (actual time=0.140..0.153 rows=1 loops=1)
-> Index Scan Backward using tbl_access_log_pkey on tbl_access_log (cost=0.00..15710.13 rows=30345 width=4) (actual time=0.110..0.110 rows=1 loops=1)
Total runtime: 0.287 ms
SQLの実行時間が1/6000に減ったことがお分かりいただけましたか？
このように、PostgreSQLでは、一見便利そうに見える関数が、実はパフォーマンスに非常に大きな悪影響を与えることが多いことがお分かりいただけたと思います。
limitの活用
SQL文のパフォーマンスのポイントは、いかに対象とするデータを減らすかです。
そういう意味で、インデックスを活用すると、目次から直接ページを開くようなイメージでデータを取り出すことができますので、とても高速に処理を行うことができます。
そしてもうひとつ、簡単に大きな結果を得られるのがlimitです。
先ほどの例で使用したSQLを再び。
select ac_id from tbl_access_log order by ac_id desc limit 1 offset 0;
上記のSQLにもlimitが使用されています。
しかしながら、単純に最大値をとるだけなら、limit 1 offset 0の部分は無くても同じ結果が得られます。
ところが、このlimit 1 offset 0を消してみると、結果の値は同じでもパフォーマンスには大きな開きが出ます。
EXPLAIN ANALYZE select ac_id from tbl_access_log order by ac_id desc;
実行結果
Sort (cost=3672.51..3748.37 rows=30345 width=4) (actual time=1762.159..2307.664 rows=30345 loops=1)
Sort Key: ac_id
-> Seq Scan on tbl_access_log (cost=0.00..1413.45 rows=30345 width=4) (actual time=2.222..789.455 rows=30345 loops=1)
Total runtime: 2868.611 ms
なんと、インデックスが使用されなくなり、SQLの実行時間が１万倍にはねあがりました。
SQLのパフォーマンス向上のツボは、いかに取得するデータを必要最小限に留めるかにつきます。
WEBアプリケーションでは、１ページあたりに表示するデータの行数には必ず限りがありますので、忘れずにlimitを使用するように心がけましょう。
その他のポイント
上記以外にも書籍等で普通に紹介されている記述が、パフォーマンス上大きな悪影響を及ぼす事例があります。
- ＊（アスタリスク）を使用している。
＊は使わずに必要な列を指定した方が、確実にパフォーマンス向上につながります。 - ORを使用している。
ORを使用すると複合インデックスが使用されませんので、代わりにexists等を使うよう工夫することで劇的な改善が可能です。（PostgreSQL8.xでは改善されています。） - 前後方一致検索を行っている。
可能な限り避けた方がいいのですが、難しいことも多いでしょう。
NamazuとPostgreSQLを連携させることも可能なので、予算に余裕があれば挑戦するといいかもしれません。 - in()の中でのサブクエリーの使用
これは、PostgreSQL7.xの仕様ですが、極端にパフォーマンスが低下します。
PostgreSQL8.xでは改善されています。 - 日付の評価に＜や＞を使っている。
この場合もインデックスが使用されません。期間などの指定にはbetweenを使用した方が遥かに高速です。
以上、PostgreSQLの、SQLのチューニングによるパフォーマンス改善についてご説明しました。
上記であげた事例は、どれも数百〜数千倍単位でパフォーマンスが向上します。
ぜひ、お試しください。
なお、弊社では、既存のシステムのSQLチューニングのご注文も承っておりますので、ご興味があればご相談ください。
