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

[松元 瑛理子]

はじめまして。
松元と申します。

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 が速度劣化の原因となっている様子です。)


以上
宜しくお願い致します。