[pgsql-jp: 32971] Re: 問い合わせの速度低下

[Tatsuo Ishii]

石井です．

> PostgreSQL7.4.2を利用しているのですが、
> パフォーマンス面で悩んでいまして・・
> 
> 毎日２４時間、５分おきに５００レコードずつどんどん追加されていくテーブルがあ
> ります。
> 
> TABLE T_RESULT
>   TIME_SMPL   TIMESTAMP
>   CHECK_ID    INTEGER
>   CHECK_SUB   INTEGER
>   以下、データフィールド
> 　　　：
> 
> このようなテーブルで、TIME_SMPL、CHECK_ID、CHECK_SUBの三つのフィールドが
> 重複キーのようになっていて、
> TIME_SMPL の重複インデックス I_RESULT_1
> CHECK_ID ＋ CHECK_SUB の複合重複インデックス I_RESULT_2
> TIME_SMPL ＋ CHECK_ID ＋ CHECK_SUB の複合重複インデックス I_RESULT_3
> この、三つのインデックスを念のため設定してあります。
> 
> なぜインデックスが三つかというと、VACUUMした後におなじSELECT文をEXPLAINする
> と、インデックスがどれか一つだけだと、時々シーケンシャルスキャンになってしま
> うのです。三つあると、必ずどれか一つを使ってくれるようです。
> （主に使われるのは、I_RESULT_1+Filterのパターンと、I_RESULT_3だけのパターン
> のようです）
> 
> そこで、問題なのですが・・ CHECK_IDとCHECK_SUBとTIME_SMPLを条件としてレコー
> ドを取り出すだけの簡単なSELECT文の実行時間が、データがたまるとどんどん遅く
> なってしまうのです。（１日動かすと144000レコード増えて、SELECTの問い合わせに
> かかる時間は20倍くらいになってしまいます）

この程度のデータ量なら，きちっとインデックス経由でアクセスすれば十分実
用的な速度が出るはずです．タプルサイズにもよりますが，Btreeインデック
スなら，10億件のデータでも5回以内程度のディスクアクセスでデータに到達
できるはずですから．

ところで，「どんどん遅くなる」では良く状況がわかりませんので，EXPLAIN
ANALYZEの結果を速いときと遅いときの両方で見せてもらえませんか？

後，データはINSERTのみですか？UPDATEやDELETEの頻度は？

> 遅くなったときに VACUUM ANALYZE するとまた元通り早くなるのですが、またしばら
> くすると遅くなってきますし、データがたまってくるとVACUUM自体がとんでもなく時
> 間かかってしまうので、こまめに実行できる状況ではありません。。
> 
> もしかして、追加されたレコードについてはVACUUMするまでインデックスが有効にな
> らないのでしょうか。。？

そんなことはありません．

> また、このような場合の何かいいパフォーマンス対策はありますでしょうか。。？

「VACUUMをかけると速くなる」という話をたまに聞きますが，理由としては，

1) 統計情報が古くなって問い合わせプランが不適切になっている

2) 実はVACUUMしたためにカーネルやPostgreSQLの共有バッファにデータがキャッ
   シュされ，それが効いているだけ

の2つが考えられます．この2つを切り分けるためには，VACUUMの前後で問い
合わせプランを比較すればよいでしょう．同じようなプランで，かつ検索対象
や取得するデータ量がさほど変わらないのに実行速度が大きく違うのなら2)の
可能性が高いことになります．(ここから先は推測ですが)もし2)なら単に

SELECT * FROM T_RESULT;

を実行しただけでまた速くなるはずです．

ところで，

> 蓄積したデータへの主なアクセスは、直前２４時間分と直前７日分のデータだけ
> なので、よけいな分はプレーンなファイルに書き出すというのを、検討してみま
> す。。

ということならば，partial indexを使うとか，継承やUNIONを使ってテーブル
を1週間分ずつ分割する，という手もありそうですね．
--
Tatsuo Ishii