kent013回答ポイント 110pt

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秒改善しただけでした。

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

※ 有料アンケート・ポイント付き質問機能は2023年2月28日に終了しました。

ログインして回答する

ベストアンサー

うぃんど2011/03/09 00:41:52
満足72pt

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

　

【１】ANALYZEで情報収集していない（あるいは情報が古い）のではないでしょうか？

１）EXPLAIN を実施して結果をメモる

２）ANALYZE を実施

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

　

【２】更新系ではなく蓄積系であるならば（定期的に）データを物理的に並び替えてみては？

１）CLUSTER を実施

２）並び替えたので再び ANALYZE を実施

３）EXPLAIN を実施して先の結果と比較する

 スター
- kent013 2011/03/12 00:31:54
  ありがとうございます。
  
  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の問題だと思います。
  
  ありがとうございました！

その他の回答

JULY2011/03/08 14:34:52
満足22pt

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

11.2. インデックスの種類

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

ただ、

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

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

スター
- kent013 2011/03/09 16:09:25
  
  ありがとうございます。試してみて報告します。
  
  パーティショニングを使った場合8秒が5秒に改善しました。
  
  正直、サーバーがふた昔前くらいのスペックなので、それを改善するほうが簡単な気もしてきましたが、とりあえずそれは最終手段ということで。
  
  ----
  
  追記
  
  hashインデックスにしてみましたが、速度は特に変わりませんでした…。

pretaroe2011/03/08 15:53:53
満足3pt

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

スター

kent013 2011/03/08 16:22:41

kernel.shmmni = 4096

kernel.shmall = 268435456

kernel.shmmax = 4294967295

でした。

toki-21312011/03/08 18:46:48
0pt

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

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

スター

kent013 2011/03/09 16:07:53

タノシソウデスネ。

chinchin-kozou2011/03/08 17:31:41
満足2pt

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

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

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

スター

kent013 2011/03/09 02:46:14

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

tama2132011/03/09 09:33:15
満足11pt

INDEXのVACUUM、REINDEXを実施する

効果があれば、Concurrent VACUUMを利用するようにすればよいでしょう

9.0だと効果はないと思います

またINDEXのキャッシュヒット率を調べてみてください。

http://lets.postgresql.jp/documents/technical/statistics/2

100％に近くなるように、設定を見直してみてください。

メモリーも4Gありますし、INDEX程度はキャッシュすることは可能だと思います。

100％に近い値でしたら、INDEXは有効に使われていてキャッシュも利いてるということですので

それ以外のところでボトルネックになって点をさがすしてみてください。

スター

kent013 2011/03/09 16:08:23

ありがとうございます。

CLUSTERが終わったらやってみます。

その他の回答を読む

この質問へのコメント

きゃづみぃ2011/03/08 14:19:53

私にはサーバーのスペックをあげることしか思いつかない・・・
ソートしておけば少しは速くなるかも・・・。

六千万件で 6秒なら許容範囲だよねぇって言うしかない。
これが 1秒じゃないと絶対ダメ！？って？
kent0132011/03/08 14:30:55

やっぱりそれしかないですかね…個人なのでできればソフトウェア的な努力でどうにかしたいというところが、本音なのですが。
サーバースペックは
CPU：Intel(R) Pentium(R) 4 CPU 3.00GHz
メモリ：4G
です。

SSDを買えが答えなら、そうします…。

PostgreSQL 9.0を使っています。

ベストアンサー

うぃんど2011/03/09 00:41:52
満足72pt

kent013 2011/03/12 00:31:54

その他の回答