アシアルブログ

こんばんは、牧野です。
今日は前々回の話題に戻って、PostgreSQLのチューニングの話です。

この前は重いSQLをどうやって見つけるか紹介しました。今回は処理を速くするためのSQLの具体例を紹介します。

１．インデックスを使う
以前も書いたので省略しますが、データ数が多くなってくると（数万件以上とか）インデックスが正しく使えているかどうかで負荷のかかり方が大きく変わってきます。
複数カラムの条件検索の場合は、必要に応じて複合インデックスを作成します。その時は、プログラムの方でWHERE句の順番に気をつけましょう。

２．VACUUMとREINDEX
特にバッチプログラムで頻繁にデータ更新を行うようなテーブルがある時は要注意です。VACUUMをしないままで運用していくと大変なことになる場合があります。バッチ処理の後等定期的にVACUUMするようにしましょう。
自動バキュームを使うのも有効です。8.1、8.2の場合はpostgresql.confのautovacuumをonにします。
8.3だとデフォルトで自動バキュームがオンになっています。あと、8.3にするとvacuumそのものの必要性がかなり低下します。
データ更新が頻繁にあるテーブルでは、定期的にREINDEXもしましょう。
REINDEX TABLE テーブル名;
大量のデータを更新してしまっている場合、VACUUMだけではあまり改善しないことがあります。
そんな時はVACUUM FULLを実行します。

10万件余りのデータを10回INSERT、DELETEした場合の比較

1. test=> explain select count(*) from music;
2.                              QUERY PLAN                             
3. --------------------------------------------------------------------
4.  Aggregate  (cost=10747.35..10747.35 rows=1 width=0)
5.    ->  Seq Scan on music  (cost=0.00..10476.28 rows=108428 width=0)
6. (2 rows)
8. test=> vacuum full music;
9. VACUUM
10. test=> explain select count(*) from music;
11.                             QUERY PLAN                             
12. -------------------------------------------------------------------
13.  Aggregate  (cost=2209.35..2209.35 rows=1 width=0)
14.    ->  Seq Scan on music  (cost=0.00..1938.28 rows=108428 width=0)
15. (2 rows)

VACUUM FULL後は1/5くらいのコストになりました。でも、VACUUM FULL中はそのテーブルがロックされて読み書きともにできなくなります。本番環境で実行する時は気をつけて下さい。
また、テーブルのデータを全部消す時は、TRUNCATEを使った方がいい場合がほとんどです。DELETE時に実行するトリガはTRUNCATE時には実行されませんが、TRUNCATEだとVACUUM、VACUUM FULLの必要がなくDELETEよりも高速です。

３．COUNT()、MAX()、MIN()、DISTINCTに注意
COUNT()、MAX()、MIN()関数やDISTINCTを使うと、対象カラムにインデックスが張ってあってもデータ数が多くなってくると重くなることがあります。
MAX()、MIN()はORDER BYで、DISTINCTはGROUP BYで代用可能です。

1. test=> explain select max(second) from music;
2.                             QUERY PLAN                             
3. -------------------------------------------------------------------
4.  Aggregate  (cost=2209.35..2209.35 rows=1 width=4)
5.    ->  Seq Scan on music  (cost=0.00..1938.28 rows=108428 width=4)
6. (2 rows)
8. test=> explain select second from music order by second desc limit 1;
9.                                               QUERY PLAN                              
10. ---------------------------------------------------------------------------
11.  Limit  (cost=0.00..0.05 rows=1 width=4)
12.    ->  Index Scan Backward using music_second_index on music  (cost=0.00..5263.88 rows=108428 width=4)
13. (2 rows)

1. test=> explain select distinct artist_id from music;
2.                                QUERY PLAN                                
3. -------------------------------------------------------------------------
4.  Unique  (cost=11006.32..11548.46 rows=2143 width=4)
5.    ->  Sort  (cost=11006.32..11277.39 rows=108428 width=4)
6.          Sort Key: artist_id
7.          ->  Seq Scan on music  (cost=0.00..1938.28 rows=108428 width=4)
8. (4 rows)
10. test=> explain select artist_id from music group by artist_id;
11.                             QUERY PLAN                             
12. -------------------------------------------------------------------
13.  HashAggregate  (cost=2209.35..2209.35 rows=2143 width=4)
14.    ->  Seq Scan on music  (cost=0.00..1938.28 rows=108428 width=4)
15. (2 rows)

上の例では処理コストが1/5くらいにはなっていますが、インデックスが張ってないと余計時間がかかります。。。
COUNT()は、あまりいい方法を知りません。重くてどうしようもなかったら、データ数を入れるテーブルを作る、とか。。。何か他にうまい方法を知っている方がいましたらぜひ教えて下さい。

以上、気付いたことから順に挙げました。
MySQLではサブクエリを使うと極端に遅くなることがあった(100倍以上時間がかかった)のでPostgreSQLでも試してみましたが、PostgreSQLではそんなことはないようです。