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

PostgreSQL 9.0を使っています。
wordsというテーブル(テーブルサイズは6G程度)があってそこに6000万行はいっています。
wordsにwordというtext型(非ユニーク、2文字?10文字程度の文字列)のカラムがあるのですが、
SELECT * FROM words where word = 'まめ';
という結果が60件前後返ってくるクエリの実行に5秒前後かかります。

インデックスは
CREATE INDEX words_word_index ON words USING btree(word);
となっています。
中間一致検索などはしません。wordカラムの完全一致のみで考えています。
パーティショニングしてテーブルサイズをそれぞれ2G程度になるようにしてみたのですが、2-3秒改善しただけでした。

このクエリの速度を上げる方法、あるいはそれに至る取り掛かりを教えていただけますでしょうか。

●質問者: kent013
●カテゴリ:コンピュータ ウェブ制作
✍キーワード:2G INDEX ON postgreSQL SELECT
○ 状態 :終了
└ 回答数 : 6/6件

▽最新の回答へ

1 ● JULY
●22ポイント

完全一致だけなら、ハッシュインデックスを使う手はあるかもしれません。

11.2. インデックスの種類

上記ページの真ん中ぐらいに、ハッシュインデックスに関して記述があります。

ただ、

パーティショニングしてテーブルサイズをそれぞれ2G程度になるようにしてみたのですが、2-3秒改善しただけでした。

パーティショニングで、5 秒程度の物が 2?3 秒に改善したのであれば、I/O の問題が一番、大きいのかもしれません。だとすれば、SSD にしてしまうのが、お金はかかるけど、簡単な改善方法かもしれません。

◎質問者からの返答

ありがとうございます。試してみて報告します。

パーティショニングを使った場合8秒が5秒に改善しました。

正直、サーバーがふた昔前くらいのスペックなので、それを改善するほうが簡単な気もしてきましたが、とりあえずそれは最終手段ということで。

----

追記

hashインデックスにしてみましたが、速度は特に変わりませんでした…。


2 ● pretaroe
●3ポイント

Linuxの共有メモリセグメント(SHMMAX)の値の見直し

◎質問者からの返答

kernel.shmmni = 4096

kernel.shmall = 268435456

kernel.shmmax = 4294967295

でした。


3 ● うぃんど
●72ポイント ベストアンサー

Pentium4 3GHz搭載マシンで、インデックスが有効利用されていて、結果行数が60程度であれば、もっと早くなりそうですが…

【1】ANALYZEで情報収集していない(あるいは情報が古い)のではないでしょうか?

1)EXPLAIN を実施して結果をメモる

2)ANALYZE を実施

3)再び EXPLAIN を実施して先の結果と比較する

【2】更新系ではなく蓄積系であるならば(定期的に)データを物理的に並び替えてみては?

1)CLUSTER を実施

2)並び替えたので再び ANALYZE を実施

3)EXPLAIN を実施して先の結果と比較する

◎質問者からの返答

ありがとうございます。

CLUSTERを試してみます。

----

追記

CLUSTERしたところ、テーブルが大きすぎて帰ってきませんでした。

http://www.postgresql.jp/document/9.0/html/sql-cluster.html

にあるように、

CREATE TABLE words_tmp AS
 SELECT * FROM words ORDER BY word;

として、物理的に並べ替えたテーブルを作り、インデックスをはりなおしたところ、1秒以内にレスポンスが帰ってくるようになりました!

これ以上は、ディスクIOの問題だと思います。

ありがとうございました!


4 ● toki-2131
●0ポイント

パーティショニングしてテーブルサイズをそれぞれ2G程度になるようにしてみたのですが、2-3秒改善しただけでした。

パーティショニングで、5 秒程度の物が 2?3 秒に改善したのであれば、I/O の問題が一番、大きいのかもしれません。だとすれば、SSD にしてしまうのが、お金はかかるけど、簡単な改善方法かもしれません。

◎質問者からの返答

タノシソウデスネ。


5 ● chinchin-kozou
●2ポイント

テーブルを数個に分割する

個別にSELECTしたものをUNIONでつなげば結果は同じ

6個ぐらいのテーブルに分けると効果があるかも。

◎質問者からの返答

それをシステム的に実現したのがパーティショニングだと思います。


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


●質問をもっと探す●



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