Postgreが重いです

質問

PostgreSqlを利用しております。
テーブルAを作成し、テーブルにはURL、HPの簡単な説明をもっております。
HPデータを5万件程度登録したあたりで、
データベースからデータを取り出し、htmlで表示するまで50～80秒
程度時間がかかってしまいます。
プログラム言語はPerl、DBへのアクセスはPgモジュールを使用して
おります。
レンタルサーバであるため、Postgreのバージョン、サーバスペック
等はわかっておりません。
※他のユーザと共用サーバであることは間違いないのですが。

有識者様にご質問ですが、Postgreは5万件程度の登録で動作
が重くなるのでしょうか？
それとも、サーバの設定の問題でしょうか？

回答No.3

chukenkenkou · ベストアンサー

#1、#2回答者です。

>仮にhp_body、hp_urlにindexを指定する場合、
>create index hp_table_ix1 on hp_table(hp_timestamp, hp_body, >hp_url)
>とすればよいのでしょうか？

3列でインデクスを構成したければ、そういう指定になります。

インデクスを利用し性能を出すための検索条件等の書き方と、インデクスの定義方法はお分かりですか？
不必要なインデクスを定義すると、insert、update、deleteでは、オーバヘッドになってしまいます。
なお、primary keyを指定した列には、PostgreSQLがユニークチェックするために内部的にインデクスを定義します。したがって、同じ構成列のインデクスを定義するのは、更新処理等でオーバーヘッドになるだけです。

インデクスの有効利用について、いくつか例を示します。
なお、データ件数が相当に少ない（100件とか）場合は、PostgreSQLに「インデクスを使うより、表データを直接見た方が早い」と判断される場合があります。
また、analyze文等でコスト情報（重複度合いなど）を収集している場合は、以下の説明とは違ってくる場合があります。

＜例1＞単一列でインデクスを構成
(1)インデクスの定義
create index t1ix1 on t1(c1)

(2)インデクスを有効に利用できる検索
　次のような検索で、インデクスを有効利用できます。

select * from t1 where c1='a' -- =条件
select * from t1 where c1 between 'a' and 'z' -- 範囲条件
select * from t1 where c1 like 'a%' -- 前方一致
select * from t1 where c1 in('a','b','c') -- in条件

また、以下のような操作でも、インデクスが活用されます。

select sum(c1) from t1 -- 集合（集計）関数
select * from t1 order by c1 -- ソートやグループの指定
select * from t1 order by c1 limit 100 offset 100 -- limit、offsetは有効利用できるインデクスがないと、表のデータ部を空読みして読み飛ばすことになる。インデクスが利用できれば、インデクス上で読み飛ばしできる。

インデクスを有効利用できる検索条件と、利用できない検索条件があった場合は、インデクスで絞り込んだ後、表の格納データで条件が評価されます。

select * from t1 where c1='a' and c2>100

また、ソート(order by）やグループ指定(group by）、重複排除(distinct）指定時、利用できるインデクスがないと、表データを見てソートが行われることになります。
（検索条件でデータが絞り込まれている場合は、そのデータのみ）

select * from t1 order by c2

＜例2＞複数列でインデクスを構成
(1)インデクスの定義
create index t1ix2 on t1(c1,c2,c3)

(2)インデクスを有効に利用できる検索
　次のような検索で、インデクスを有効利用できます。

select * from t1 where c1='a' -- インデクスを構成する先頭列にインデクスを有効利用できる条件あり。
ただし、この検索の場合はc1だけのインデクスがあればいいので、c1～c3でインデクスを構成していた場合、キー長が長くなる分、1個のインデクスページで管理できる情報数が少なくなり、インデクスのI/Oは多くなります。

select * from t1 where c1='a' and c2=100 -- インデクスを構成する先頭列に加え、他の構成列も、インデクスを有効利用できる条件あり

select * from t1 where c1='a' and c2=100 and c3>50

select * from t1 where c1='a' and c3>50 -- c2の条件がない場合、インデクスの利用効率はc2の条件がある場合比べ悪い

select * from t1 where c1 between 'a' and 'z' order by c1,c2
-- 条件式で絞り込む以外に、ソート指定がインデクス構成列と同じ並び(昇順、降順は、まったく同じor全部逆）であればソート抑止できる。group byやdistinctの背景でもソートが行われるので、その場合も同様。

お礼

このたびはありがとうございました！
おかげさまで動作が軽くなりました！

非常にわかりやすいご解答ありがとうございます。

回答No.2

chukenkenkou

#1回答者です。

>hp_timestamp datetime, // 登録日

datetime型？
PostgreSQLには、datetime型はないようですが？
timestamp型の誤りでしょうか？

>select * from hp_table ORDER BY hp_timestamp DESC LIMIT 20

現在の表＆インデクス定義で、このSQLがどういう動きをするか、お分かりですか？

このselect文を実行するたびに、全件(現在は5万件）をhp_timestamp列の値でソートし、その結果から最新の値を20件だけ取り出すことになります。
毎回、全件のソートが発生しますから、データが増えるごとにどんどん遅くなって当然のSQLになっています。

対策としては、hp_timestamp列にインデクスを定義しましょう。

次のSQLを実行します。

create index hp_table_ix1 on hp_table(hp_timestamp)

hp_table_ix1はインデクス名であり、好きな名前にしてください。

データが格納された状態でインデクスを定義するので、実行に少し時間が掛かると思いますが、その後の今回のselect文の実行は、劇的に早くなり、データ件数が増えても、極端に性能劣化することはないでしょう。

※上記のcreate indexでは、hp_timestamp列の値の昇順でインデクスを作りますが、降順にも活用できます。

また、text型を使用していますが、text型を使用するのは、

「検索時の条件に指定しない」
＆
「長さの上限がどうしても事前に把握できない」

場合だけにし、なるべくvarcharを使用しましょう。

text型にインデクスを定義できなっかたり、定義できても検索時に有効利用できないRDBMSが殆どです。

補足

ご解答ありがとうございます。
>datetime型？
>PostgreSQLには、datetime型はないようですが？
>timestamp型の誤りでしょうか？
timestamp型の間違いです。
申し訳ございません。

もう一点ご教授願います。
仮にhp_body、hp_urlにindexを指定する場合、
create index hp_table_ix1 on hp_table(hp_timestamp, hp_body, hp_url)
とすればよいのでしょうか？

回答No.1

chukenkenkou

PostgreSQLのバージョンは、SQLが入力できる状態で
「select version()」
を入力すれば得られます。

表を構成する各列のデータ型は、何になっていますか？
どういうインデクスを、この表に定義していますか？

>5万件程度登録したあたりで、データベースからデータを取り出し、
>htmlで表示するまで50～80秒程度時間がかかってしまいます

5万件中、何件を取り出そうとしているのですか？
その時の検索条件、ソートの指定(order by）は、インデクスを有効利用できる指定になっていますか？

・表及びインデクスの定義（各列のデータ型も分かるように
・検索時のSQL
・5万件中、何件の検索か？

といった情報がなければ、使い方の問題なのか、サーバ等の問題なのかといった切り分けはできません。

補足

ご解答ありがとうございます。
データの方は以下となっております。
上記ではすこし省略しましたが、全部記載します。
■テーブル：
CREATE TABLE hp_table(
　no serial primary key, // 項番
　Category int4, // カテゴリNo
　hp_name text, // HP名
　hp_body text, // HPの説明
　hp_url text, // URL
　hp_timestamp datetime, // 登録日
　flg int2 // 表示/表示判定フラグ(0、1)
);
　※すいませんインデックスが分かっていないのですが
　　どうやれば使用できるのでしょうか？

■検索時のSQL:
select * from hp_table ORDER BY hp_timestamp DESC LIMIT 20;
上記sqlの通り、5万件中20件の表示となっていります。

申し訳ないのですが宜しくお願い致します。