[pgsql-jp: 40470] Re: SQLの検索性能について

["鈴木孝征 (Suzuki Takamasa)"]

松元様

(2)の条件では600万件のデータが該当するということを取得するためのSQLとそ
の一部を取得するということにわけてはどうでしょうか。

インデックスを日付につくってあるということは日付での検索が主になるからと
推測します。最初にORDERをつけないで件数を取得し、次に日付の範囲を狭めて
取得件数を少なくしてORDERをつけるという方法でどうでしょうか。

ログを蓄積するというシステムのようなので当日以外のログは増えないと思いま
すので日付ごとの件数を別テーブルでもっておけば任意の順番でとりだせるので
はないでしょうか。

チューニング方法のアドバイスではありませんが、性能アップにはなるかと思い
ます。

あとtimestamp型のデータの検索で日付ごとであればdate_truncとかを使ってイ
ンデックスをつくるとかはどうでしょうか。(これは根拠のない提案です。)

鈴木孝征


> work_memとshared_buffersでチューニングを実施したのですが、
> 思うように検索性能が改善しませんでした。
> アドバイスの程宜しくお願い申し上げます。
> 
> 仕様)
> サーバから出力するメッセージを随時DBに蓄積し、
> ユーザがWeb画面よりログ検索を行う。
> データ総数8,000万件(約600万件ずつ13パーティションに分散)
> 
> 実施環境)
> ＝＝＝
>    OS  CentOS 5.2
>    CPU Intel Xeon 3.16GHz
>    RAM 1GB(実際の運用では2GBを予定)
>    PostgreSQLのバージョンは8.4.4
> ＝＝＝
> 
> TBL定義)
> ＝＝＝
> CREATE TABLE LOG_TBL
> (
>    NODE_NAME VARCHAR(8),
>    PROC_NAME VARCHAR(25),
>    LOG_DATE TIMESTAMP,
>    LOG_MESSAGE TEXT,
>    REC_DATE TIMESTAMP
> )
> ＝＝＝
> →主KEYは無く、IndexをLOG_DATE列に付与しています。
> 
> これまでの調査経緯)
> ＝＝＝
> 以下のSQL(ORDER BYと OFFSET/LIMITを指定したSQL)で検証
> SELECT * FROM LOG_TBL
> WHERE LOG_DATE BETWEEN [下限値] AND [上限値]
> ORDER BY LOG_DATE, REC_DATE
> OFFSET 0 LIMIT 1001;
> 
> LOG_DATEの範囲を変えながら以下２種類の検索を実行
> (1) BETWEEN '2010-08-20 00:00:00' AND '2010-08-20 23:59:59' で
>     200,000件が該当し、LIMITにより直近1001件が返される検索
>     (1パーティションが該当)
> (2) BETWEEN '2010-07-21 00:00:00' AND '2010-08-20 23:59:59' で
>     6,200,000件が該当し、LIMITにより直近1001件が返される検索
>     (2パーティションが該当)
> 
> ■デフォルト(work_mem=1MB, shared_buffers=32MBの認識)でのSQLパフォーマンス
> (1)が873.950ms
> (2)が46,644.844ms
> 
> ■work_mem=10MB, shared_buffers=500MBに変更したSQLパフォーマンス
> (1)が834.125ms
> (2)が43,884.545ms
> 
> 得られたチューニングの効果
> (デフォルトの測定結果)-(変更後の測定結果)
> (1)が39.825ms
> (2)が2,760.299ms
> ＝＝＝
> →半減する程の速度向上を期待していたのですが、前述のとおり性能改善は見られませんでした。
> (ORDER BY が速度劣化の原因となっている様子です。)