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


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

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

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

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

回答の条件
  • 1人2回まで
  • 登録:2008/11/10 21:11:01
  • 終了:2008/11/15 01:41:29

ベストアンサー

id:kn1967 No.7

kn1967回答回数2915ベストアンサー獲得回数3012008/11/13 23:03:49

ポイント100pt

部分一致を用いているのでインデックスの効果は得られません。

さらに、正規表現を使っているので、こちらもネックになります。


(1)まずは、正規表現ではなくLIKEやILIKE(大文字小文字を区別しない)を

用いる事が出来ないかを検討してみてください。


(2)次に、苗字や名前は完全一致(インデックスの効果が期待できる)

あるいは前方一致(検索が単純なので高速)で行えないか

emailアドレスのドメインならば後方一致で行えないかといったように

出来る限り単純な形で検索することは出来ないかも検討してみてください。


(3)phpやjavaなど・・・どのような言語環境を使っているにしても

データベースから受け取るフィールドの数が多ければ多いほど

受け取りや受け取った後の処理で手間が増えますので

htmlタグまで付けさせるかどうかはさておき、ある程度結合させたほうが

速度を稼げる場合がありますので検討してください。


(4)すでにやっておられるかと思いますが

1つのSQLで全てをカバーしようとするのではなく

必要に応じてSQLをプログラム側で動的に生成するとか

プログラム内に、いくつかのSQLを用意しておいて

必要に応じて使い分けるといったような事を抜かりなく

行っているかどうかを再確認してみてください。


(補足)

PostgreSQLと言う事ですから、独特の高速化手法があったりもしますが

データの使われ方(使い方)に関して、ツッコンでいかなければなりませんし

レンタルサーバであるという事(どこまで設定を弄れるか?)を踏まえますと

前回及び今回回答させていただいたような

PostgrSQLに特化したものではなく他DBでも通用するような所から

見直しをはじめていただくのが良いと考えて回答しております。

id:jayz

具体的な方法について何点も示していただきありがとうございます

まずは正規表現を用いないSQLを使って検証してみたいと思います

2008/11/15 01:38:37

その他の回答(7件)

id:pahoo No.1

pahoo回答回数5960ベストアンサー獲得回数6332008/11/10 21:42:30

ポイント17pt

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

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


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

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

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


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

id:jayz

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

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

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

2008/11/13 20:19:40
id:language_and_engineering No.2

lang_and_engine回答回数170ベストアンサー獲得回数632008/11/10 21:38:05

ポイント17pt

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

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

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


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

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



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

id:jayz

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

ありがとうございます

2008/11/13 20:18:25
id:chuken_kenkou No.3

chuken_kenkou回答回数722ベストアンサー獲得回数542008/11/11 02:55:20

ポイント16pt

母体件数が、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万件でも、ある程度の性能を出せるはずです。

id:jayz

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

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

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

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

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

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

---

Indexes:

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

"hoge_email_key" UNIQUE, btree (c15)

"hoge_no_key" UNIQUE, btree ("no")


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

ありがとうございます

2008/11/13 20:07:00
id:hirotie No.4

hirotie回答回数25ベストアンサー獲得回数12008/11/11 22:03:54

ポイント16pt

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

1、ディスクIOの原因。

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

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

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

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

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

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

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

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

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

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

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

遅くなります。

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

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

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

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

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

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

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

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

id:jayz

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

この処理の際に発行している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

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

ありがとうございます

2008/11/13 20:17:16
id:kn1967 No.5

kn1967回答回数2915ベストアンサー獲得回数3012008/11/11 23:34:01

ポイント16pt

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

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

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に限った事ではありませんが

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

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

id:jayz

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

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

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

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

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

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

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

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

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

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

ありがとうございます

2008/11/13 20:41:12
id:Nekomajin No.6

ねこまじん回答回数4ベストアンサー獲得回数02008/11/12 22:59:44

ポイント16pt

1.検索に使うカラムで複合インデックスを作成する。

2.頻繁に更新するテーブルに対して検索する場合は、夜間バッチなどでvacuumeを定期的に呼ぶようにする。

4.大量の更新がかかったときは統計情報を収集しなおしておく。

3.頻繁に更新がかかるテーブルがあるデータベースの場合、定期的にドライブのデフラグをおこなう。

半年ほど前に、案件で利用できるかPostgreSQLの検証をしたことがありますが、2000万~3000万レコードあるテーブルに対して文字列5カラムをandでつなげて検索したことありますが、1秒以内に結果が帰ってきていました。

(PCは5年ほど前のWindowsサーバーなので、それほど高スペックではありません)

id:jayz

検証例を示していただきありがとうございます

わかりやすくて助かりました

2008/11/15 01:36:28
id:kn1967 No.7

kn1967回答回数2915ベストアンサー獲得回数3012008/11/13 23:03:49ここでベストアンサー

ポイント100pt

部分一致を用いているのでインデックスの効果は得られません。

さらに、正規表現を使っているので、こちらもネックになります。


(1)まずは、正規表現ではなくLIKEやILIKE(大文字小文字を区別しない)を

用いる事が出来ないかを検討してみてください。


(2)次に、苗字や名前は完全一致(インデックスの効果が期待できる)

あるいは前方一致(検索が単純なので高速)で行えないか

emailアドレスのドメインならば後方一致で行えないかといったように

出来る限り単純な形で検索することは出来ないかも検討してみてください。


(3)phpやjavaなど・・・どのような言語環境を使っているにしても

データベースから受け取るフィールドの数が多ければ多いほど

受け取りや受け取った後の処理で手間が増えますので

htmlタグまで付けさせるかどうかはさておき、ある程度結合させたほうが

速度を稼げる場合がありますので検討してください。


(4)すでにやっておられるかと思いますが

1つのSQLで全てをカバーしようとするのではなく

必要に応じてSQLをプログラム側で動的に生成するとか

プログラム内に、いくつかのSQLを用意しておいて

必要に応じて使い分けるといったような事を抜かりなく

行っているかどうかを再確認してみてください。


(補足)

PostgreSQLと言う事ですから、独特の高速化手法があったりもしますが

データの使われ方(使い方)に関して、ツッコンでいかなければなりませんし

レンタルサーバであるという事(どこまで設定を弄れるか?)を踏まえますと

前回及び今回回答させていただいたような

PostgrSQLに特化したものではなく他DBでも通用するような所から

見直しをはじめていただくのが良いと考えて回答しております。

id:jayz

具体的な方法について何点も示していただきありがとうございます

まずは正規表現を用いないSQLを使って検証してみたいと思います

2008/11/15 01:38:37
id:Nekomajin No.8

ねこまじん回答回数4ベストアンサー獲得回数02008/11/14 12:40:16

ポイント100pt

他の回答者さんの補足だけ。

条件にORを使うと複合インデックスは使われません。

複合インデックスを使わないと結果が返らないようなテーブルではORを使わないようにします。

私がよくやる方法はUNIONでSELECTを繋げます。

例えば、

select a, b from hoge where a = 'AAA' or b = 'bbb'

select a, b from hoge where a = 'AAA'

union all select a, b from where b = 'BBB'

のようにします。

SQLは長くなりますがORを使うよりも早いです。

id:jayz

細くありがとうございます

今回の場合、インデックスのみでは解決できないことがわかりました

ORは使用せずにUNIONを使用して検証してみたいと思います

2008/11/15 01:40:05
  • id:kn1967
    全文検索は長文が主な対象ですから
    ご質問にあるようなフィールド構成(各項目の内容は主に単語か数値)では
    負荷増大以外に意味を成すとは思えず
    どうして全文検索などという発想に繋がるのか皆目検討が付きませんが
    この件に関して、補足していただければ嬉しいですね。

    即回答で状況が判らないだとか
    環境や状況を詳しく教えろ的なことばかり書く人がいますが
    急を要するという意味が含まれていない質問であるならば
    状況を読める人が現れるまで暫く待つべきだと思うのだけど
    (hatenaなら、深夜を除けば遅くても1時間程度で回答が来るはず)
    こちらに関しても、ご当人から補足していただければ嬉しいですねぇ。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません