人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

(PostgreSQL)検索スピードの向上について

ただ今下記のようなテーブルに100万件のデータが登録されています
no | email | fname | lname | tel | address | postal_code | gender | tall | weight | birth | hobby | class | eyesight | remarks

上のテーブルの各項目がすべて埋まっている100万件の全項目を対象とした検索を行いたいのですが
検索にかかる時間が半端ではなく・・・、どうしたら高速化できるのかと思っています

ヨドバシカメラのオンラインストアなどはあれだけ商品があるにもかかわらず全商品を対象とした検索が早いのはなぜでしょうか?!

わかる方おられましたらよろしくお願い致します


●質問者: jayz
●カテゴリ:インターネット ウェブ制作
✍キーワード: Class Eyesight Gender postgreSQL オンラインストア
○ 状態 :終了
└ 回答数 : 8/8件

▽最新の回答へ

1 ● pahoo
●17ポイント

状況がよく分からないので、テーブルのデータ構造やキーやリレーションの張り方をお知らせください。

また、「検索にかかる時間が半端ではなく」ではなく、具体的に何秒(何分?)かかり、何秒以内におさめたいのか示してください。


ヨドバシカメラのオンラインストアなどはあれだけ商品があるにもかかわらず全商品を対象とした検索が早いのはなぜでしょうか?!

ヨドバシカメラ大百科」によると、店舗で扱っているそうアイテム数は60万とのこと。

また、すべてのカラムに検索がかかっているわけではありません。タイトルに無いキーワードは検索できません。


すべてのカラムを検索対象にするのであれば、むしろ、Googleのような全文検索方式を検討した方が良いと思います。

◎質問者からの返答

すみません、具体的な数値は測っていませんでした

WEBアプリなのですが、タイムアウトしてしまう状態です

全文検索を検討してみたいと思います


2 ● lang_and_engine
●17ポイント

検索条件に使うカラム(where句に出てくるカラム)に対して,インデックスを作成します。

例:誕生日で検索する場合

CREATE INDEX 好きなインデックス名 ON テーブル名 (birth);


PostgreSQL 有効活用術 4.高速化手法

http://www.stackasterisk.jp/tech/dataBase/postgresql02_04.jsp



もしくは,もしアプリケーション作成のためにフレームワークを利用しているのであれば,そのフレームワークでSQL実行結果の「キャッシュ」ができるか調べます。

◎質問者からの返答

インデックスについて検討してみたいと思います

ありがとうございます


3 ● chuken_kenkou
●16ポイント

母体件数が、100万件なのですね?

その内、何件を検索するのでしょうか?

ある条件を見たす「1画面で表示できる件数」を、順次検索していったり、戻ったりするのでしょうか?

まず、条件指定で、100万件を絞り込む場合、例えばC1、C2列の組み合わせで絞り込むなら、(C1、C2)で構成される複合インデクスを定義することを考えます。

次に、検索結果を、どういう順にならべるのでしょうか?

例えば、(C1、C2)の検索条件で絞り込んだものを、(C3、C4、C5)列でソート指定するなら、(C1、C2、C3、C4、C5)で構成する複合インデクスを定義すれば、先に検討した(C1、C2)のインデクスは不要にできる可能性が高くなります。

「ORDER BY」や「GROUP BY」で必要になるソート処理が、作業メモリやファイルを使用して実際に行われると、性能劣化の大きな要因になります。しかし、実際にソートが発生しないようにインデクスを定義して活用できるようにしておけば、ソートのオーバヘッドは殆どなくなります。

これらに、LIMIT句で表示範囲を限定して検索するようにすれば、母体件数が100万件でも、ある程度の性能を出せるはずです。

◎質問者からの返答

母体件数が100万件となります

その中から指定した条件を満たすレコードを抽出するといった感じです

指定できる条件としては上で示した"no"を除くすべての項目が指定できるようになっています

(必ずしもすべての条件を指定するわけではありませんが)

検索結果を並べる順番は"class"カラムの番号(1-10)の少ないレコード順にソートしています

参考までに現在のインデックスです

---

Indexes:

"hoge_pkey" PRIMARY KEY, btree ("no")

"hoge_email_key" UNIQUE, btree (c15)

"hoge_no_key" UNIQUE, btree ("no")


インデックスの張り方とソートについて調べてみようと思います

ありがとうございます


4 ● hirotie
●16ポイント

処理が遅くなる原因にはいくつかあります。

1、ディスクIOの原因。

DBのデータが置かれているディスクのスピード自体が遅い。

=>解決は難しいですね。せいぜいキャッシュを増やすぐらいですか・・・

2、インデックスが無い、もしくは張り方に問題がある。

これに問題があるかどうかを知るためには、時間のかかるとおっしゃるSQL文と

このテーブルのインデックスを全てお教えいただけると、判別しやすいです。

この問題なら、インデックスを正しく張ることで一気に解決すると思います。

3、SQL応答ではなくFETCHに時間がかかっている。

SQL文を実行している環境によって変わってきますが、私の使っている「CSE」という

ツールでは、Fetchで実行クライアントへデータを持ってくる処理に時間がかかります。

4、ODBCなどの設定でトレースログを採るようにしている。

結構盲点です。windowsのODBC設定でトレースを採ってたりなんかしたら、死ぬほど

遅くなります。

5、ネットワークの問題。

先日私も経験したのですが、サーバとクライアントの間にあるハブが死にかけていて、

それを入れ替えたら、目が醒めるほど早くなりました。

等など・・・5項目も挙げてしまいましたが、サーバ上で実行しても遅いのであれば

5番は関係ないです。3、4も薄くなりますね・・・

とりあえずは、PostgreSQLのサーバの環境、クライアントは別マシンなのかどうか

クライアントのソフトは何か、実行したSQL文とテーブルのインデックスを公開

いただけると、判定しやすいです。

◎質問者からの返答

まずインデックスの張り方に問題があるのかもしれません

この処理の際に発行しているSQLは下記になります

---

SELECT * FROM hoge WHERE ( fname ~* 'aaa' OR status ~* 'aaa' OR email ~* 'aaa' )

AND CAST(class AS BIGINT) >= '1'

AND CAST(class AS BIGINT) <= '2'

AND ( status ~* '削除' )

AND ( email ~* 'yahoo.com' )

AND status != '解除'

ORDER BY CAST(class as BIGINT) ASC

この問題は私の運用しているWEBアプリで起きている問題なのですが

数社のレンタルサーバを利用していて、すべて同じ状況が起きます

なのでハードに関するトラブルではないと思っています

サーバ環境:OS:CentOS

インデックスについて検討してみたいと思います

ありがとうございます


5 ● kn1967
●16ポイント

結果として返される件数が多くなる場合は

フロントエンド側の処理のほうが追いつかない場合が殆どですから

PostgreSQL側で出力用の整形まで行うようにするとか

offsetとlimitを用いて結果を小さくしてやるなどの工夫が必要になりますが

なんらかの工夫をなさっておられますか?

(事前に結果件数を数えて、件数が多ければ

絞り込み条件を増やしてやり直せと言うメッセージを出すという手もありますが

PostgreSQLのcountはお世辞にも早いとは言えないので

limitでぶった切るのが早いかと・・・。)


既回答と被りますがインデックスは付けておられますか?

もしもまだであるならばインデックスを付加する事によって

数十倍以上の高速化が可能であるかもしれません。

ただし、全項目にインデックスをつけるとなると

追加・修正時に負担(処理コスト)を強いる事になりますので

追加・修正が日々多数に渡る場合は、頻繁に使う項目に絞り込んで

インデックスを付けるようにしておいたほうがよろしいでしょう。


こちらも既回答と被りますがソート処理(ORDER BYやGROUP BYだけでなく

DISTINCTやJOIN、集約関数の利用でも同様の処理が行われる場合があります)を

発生させないようにインデックスを適宜つけておく必要があります。

それでも速度的に足りないという場合はpostgresql.confにて

sort_mem(バージョン8以降はwork_mem)や

shared_buffersあたりを増やす必要に迫られる可能性がありますが

下手をすると他のデーモンやアプリ、OS自体の動作に悪影響を

及ぼす可能性もありますのでメモリの使用状況をよく把握する必要があり

失礼ながら現時点での手出しはしないほうがよろしいかもしれません。


ところで、、、vacuum fullは実施したことありますか?

日々の修正登録がどのくらいか存知ませんが一日一回程度はvacuumを

週に1回程度はvacuum fullを実施するようにしてみてください。

(analyzeも一度実施してみてください。)

PostgreSQLに限った事ではありませんが

定期的なメンテナンスでパフォーマンスが大幅に変わってきますので

もしも未実施であるならば実施してみてください。

◎質問者からの返答

フロンエンドの処理というと

今回の場合WEBアプリですので

DBからプログラムにデータを渡して

整形するときということでしょうか

またPostgreSQLで出力用の整形というと

htmlまで含めた形で整形してしまうということですか

現状LIMITは使用していないのでそれも検討してみたいと思います

vacuum fullはもしかしたら?と思い実行してみました

しかし、状況が変わらず今に至っています

analyzeですか、調べて試してみたいと思います

ありがとうございます


1-5件表示/8件
4.前の5件|次5件6.
関連質問


●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ