[pgsql-jp: 41220] Re: 掲示板テーブルからのデータ取得について

[Tomoaki Sato]

佐藤です。

> はじめまして、高田と申します。
> 
> 現在バージョン8.1で掲示板を稼働させているのですが、効率良くデータを
> 取得する方法について教えていただきたいことがあります。
> テーブル構成は下記のような形となっております。
> 
> テーブル名：bbs
> 項目：message_id（連番のメッセージID）,response_to（返信先のメッセー
> ジID）,name（投稿者名）,title（件名）,message（本文）,created_at（投
> 稿日時）
> 
> 上記のようなかたちで、通常の投稿はresponse_toがNULL、メッセージ
> ID「12」に対する返信投稿であればresponse_toに「12」が入ります。
> このテーブルからresponse_toがNULLのデータ（返信ではない投稿）の一覧
> を取得するのですが、その際の取得順は、返信がなければ投稿日時、返信が
> あれば最新の返信日時、が新しい順としています。
> 現状は下記のようなクエリにより取得しています。
> ※投稿データに加えて返信の件数、最新の返信日時も併せて取得しています
> 
> SELECT
>     bbs.*,
>     ( select count(*) from bbs s1
>         where response_to = bbs.message_id ) as count,
>     case when j2.created_at is not null then j2.created_at
>         else bbs.created_at end as latest_time
> FROM
>     bbs
> LEFT JOIN
>     (
>         SELECT
>             r1.response_to,r1.created_at
>         FROM bbs r1
>         LEFT JOIN bbs r2 ON r1.message_id < r2.message_id
>             and r1.response_to is not null
>             and r2.response_to is not null
>             and r1.response_to = r2.response_to
>         WHERE r1.response_to is not null
>         GROUP BY r1.response_to,r1.created_at
>         HAVING count(r2.*) = 0
>     ) j2 ON bbs.message_id=j2.response_to
> WHERE bbs.response_to is null
> ORDER BY latest_time DESC

上記の SQL だと、直接返信された投稿しか見てないですが、そういう仕様で
OK なのでしょうか。

> 一件の投稿に対する返信の中で最も新しい（メッセージIDの値が大きい）も
> のの日時を取得する箇所で、かなり処理が重くなってしまいます。
> 上記のクエリでは最新の返信日時もSELECTしていますが、その順に並べられ
> れば特に取得する必要はありません。
> 
> インデックスはmessage_id（主キー）とresponse_toの項目に対してそれぞ
> れ設定しておりますが、それ以外には特に設定しておりません。
> 
> 効率の良い取得方法、もしくは高速にするための有効なインデックス設定案
> があればご教示いただけませんでしょうか。
> よろしくお願い致します。

以下の SQL でどうでしょうか。

SELECT b1.message_id,
    b1.response_to,
    b1.name,
    b1.title,
    b1.message,
    b1.created_at,
    count(b2.*) AS count,
    coalesce(max(b2.created_at), b1.created_at) AS latest_time
FROM bbs AS b1
LEFT JOIN (
    SELECT response_to,
        created_at
    FROM bbs
) AS b2
ON b1.message_id = b2.response_to
WHERE b1.response_to IS NULL
GROUP BY b1.message_id,
    b1.response_to,
    b1.name,
    b1.title,
    b1.message,
    b1.created_at
ORDER BY latest_time DESC;

1 万件の投稿に対して 1 万件の返信、それら 2 万件の投稿に対して 2 万件
の返信、というのを繰り返し、合計 8 万件のデータを生成して SQL を実行し
てみました。

PostgreSQL 8.1 のデフォルトの設定のままで実行時間を 2000 ミリ秒から
600 ミリ秒、さらに work_mem を 32768 (32MB) に増やして 300 ミリ秒まで
短縮できました。


----
Tomoaki Sato <sato ＠ sraoss.co.jp>
SRA OSS, Inc. Japan