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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

１、ディスクＩＯの原因。

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

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

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

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

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

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

３、ＳＱＬ応答ではなくFETCHに時間がかかっている。

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

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

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

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

遅くなります。

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

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

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

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

５番は関係ないです。３、４も薄くなりますね・・・

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

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

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

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

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

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を

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

（analyzeも一度実施してみてください。）

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

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

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

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

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

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

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

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

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

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

条件に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を使うよりも早いです。