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

高田哲郎 takada @ hngrm.com
2012年 9月 26日 (水) 18:48:23 JST


中井様
佐藤様

ご教示いただきありがとうございます。

中井様にご指摘いただいた点につきましては、既存のデータにアップデートをかける必要があるため、今後の構築時に参考にさせていただきます。ありがとうございました。

今回、佐藤様にご指摘いただいた形で実装してみましたところ、明らかな改善がみられました。

直近の返信を取得するために、無理に全体の中での順位を取得することに固執しておりましたが、単純なJOINにしてGROUP句で絞るという点と、coalesce関数のこういった使い方、ともに大変勉強させていただきました。

返信件数を取得する箇所については、
 count(b2.*) AS count
 ↓
 count(b2.response_to) AS count,

としなければ、返信ゼロの投稿でも「1」という値になってしまうため、変更いたしました。

変更前と比べて約20分の1程度のコストでSELECTが完了するようになり、大変助かりました。
ご教示いただきありがとうございました。


高田
takada @ hngrm.com



2012年9月21日 16:30 H.Nakai <nakai @ shinko.co.jp>:
> 中井と申します。
>
> 私の理解が間違っているかもしれませんが、私なら「通常の投稿」の
> response_toはNULLにせずにmessage_idと同じ番号を入れるようにします。
> そうすれば、response_toの同じレコードの中で最大のmessage_idを
> 検索すればいいことになります。検索条件が簡単なだけ速くなりそうな
> 気がします。
> 外していたらごめんなさい。
>
> (2012/09/20 12:26), 高田哲郎 wrote:
>> はじめまして、高田と申します。
>>
>> 現在バージョン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
>>
>>
>> 一件の投稿に対する返信の中で最も新しい(メッセージIDの値が大きい)ものの日時を取得する箇所で、かなり処理が重くなってしまいます。
>> 上記のクエリでは最新の返信日時もSELECTしていますが、その順に並べられれば特に取得する必要はありません。
>>
>> インデックスはmessage_id(主キー)とresponse_toの項目に対してそれぞれ設定しておりますが、それ以外には特に設定しておりません。
>>
>> 効率の良い取得方法、もしくは高速にするための有効なインデックス設定案があればご教示いただけませんでしょうか。
>> よろしくお願い致します。
>>
>
>
> --
> -=-=-=-=  SHINKO ELECTRIC INDUSTRIES CO., LTD.           =-=-=-=-
> =-=-=-=-    Research & Development Div.                  -=-=-=-=
> -=-=-=-=      Designing Technology Development Dept.     =-=-=-=-
> =-=-=-=-  Name:Hisakazu Nakai          TEL:026-263-3922  -=-=-=-=
> -=-=-=-=  Mail:nakai @ shinko.co.jp      FAX:026-263-4562  =-=-=-=-
>
>



-- 
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━‥‥‥・・・
オフィス高田   高田 哲郎

〒661-0976 兵庫県尼崎市潮江1丁目3-12-1514
TEL : 090-1674-6220
E-mail: takada @ hngrm.com
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━‥‥‥・・・


pgsql-jp メーリングリストの案内