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秒改善しただけでした。
このクエリの速度を上げる方法、あるいはそれに至る取り掛かりを教えていただけますでしょうか。
No.3
2625
1149
72pt
Pentium4 3GHz搭載マシンで、インデックスが有効利用されていて、結果行数が60程度であれば、もっと早くなりそうですが…
【１】ANALYZEで情報収集していない（あるいは情報が古い）のではないでしょうか？
１）EXPLAIN を実施して結果をメモる
２）ANALYZE を実施
３）再び EXPLAIN を実施して先の結果と比較する
【２】更新系ではなく蓄積系であるならば（定期的に）データを物理的に並び替えてみては？
１）CLUSTER を実施
２）並び替えたので再び ANALYZE を実施
３）EXPLAIN を実施して先の結果と比較する
No.1
966247
22pt
完全一致だけなら、ハッシュインデックスを使う手はあるかもしれません。
上記ページの真ん中ぐらいに、ハッシュインデックスに関して記述があります。
ただ、
パーティショニングしてテーブルサイズをそれぞれ2G程度になるようにしてみたのですが、2-3秒改善しただけでした。
パーティショニングで、5 秒程度の物が 2～3 秒に改善したのであれば、I/O の問題が一番、大きいのかもしれません。だとすれば、SSD にしてしまうのが、お金はかかるけど、簡単な改善方法かもしれません。
ありがとうございます。試してみて報告します。
パーティショニングを使った場合8秒が5秒に改善しました。
正直、サーバーがふた昔前くらいのスペックなので、それを改善するほうが簡単な気もしてきましたが、とりあえずそれは最終手段ということで。
----
追記
hashインデックスにしてみましたが、速度は特に変わりませんでした…。
No.2
53175
3pt
Linuxの共有メモリセグメント（SHMMAX）の値の見直し
kernel.shmmni = 4096
kernel.shmall = 268435456
kernel.shmmax = 4294967295
でした。
No.3
26251149
ここでベストアンサー
72pt
Pentium4 3GHz搭載マシンで、インデックスが有効利用されていて、結果行数が60程度であれば、もっと早くなりそうですが…
【１】ANALYZEで情報収集していない（あるいは情報が古い）のではないでしょうか？
１）EXPLAIN を実施して結果をメモる
２）ANALYZE を実施
３）再び EXPLAIN を実施して先の結果と比較する
【２】更新系ではなく蓄積系であるならば（定期的に）データを物理的に並び替えてみては？
１）CLUSTER を実施
２）並び替えたので再び ANALYZE を実施
３）EXPLAIN を実施して先の結果と比較する
ありがとうございます。
CLUSTERを試してみます。
----
追記
CLUSTERしたところ、テーブルが大きすぎて帰ってきませんでした。
http://www.postgresql.jp/document/9.0/html/sql-cluster.html
にあるように、
19
18
CREATE TABLE words_tmp AS
SELECT * FROM words ORDER BY word;
として、物理的に並べ替えたテーブルを作り、インデックスをはりなおしたところ、1秒以内にレスポンスが帰ってくるようになりました！
これ以上は、ディスクIOの問題だと思います。
ありがとうございました！
No.4
1381
パーティショニングしてテーブルサイズをそれぞれ2G程度になるようにしてみたのですが、2-3秒改善しただけでした。
パーティショニングで、5 秒程度の物が 2～3 秒に改善したのであれば、I/O の問題が一番、大きいのかもしれません。だとすれば、SSD にしてしまうのが、お金はかかるけど、簡単な改善方法かもしれません。
タノシソウデスネ。
No.5
1987
2pt
テーブルを数個に分割する
個別にSELECTしたものをUNIONでつなげば結果は同じ
6個ぐらいのテーブルに分けると効果があるかも。
それをシステム的に実現したのがパーティショニングだと思います。
No.6
48630
11pt
INDEXのVACUUM、REINDEXを実施する
効果があれば、Concurrent VACUUMを利用するようにすればよいでしょう
9.0だと効果はないと思います
またINDEXのキャッシュヒット率を調べてみてください。
http://lets.postgresql.jp/documents/technical/statistics/2
100％に近くなるように、設定を見直してみてください。
メモリーも4Gありますし、INDEX程度はキャッシュすることは可能だと思います。
100％に近い値でしたら、INDEXは有効に使われていてキャッシュも利いてるということですので
それ以外のところでボトルネックになって点をさがすしてみてください。
ありがとうございます。
CLUSTERが終わったらやってみます。
きゃづみぃ
2011/03/08 14:19:53
kent013
1
1
1
ありがとうございます。
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の問題だと思います。
ありがとうございました！