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

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

回答の条件
  • 1人2回まで
  • 13歳以上
  • 登録:2011/03/08 14:11:49
  • 終了:2011/03/12 00:34:46

ベストアンサー

id:windofjuly No.3

うぃんど回答回数2625ベストアンサー獲得回数11492011/03/09 00:41:52

ポイント72pt

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

 

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

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

2)ANALYZE を実施

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

 

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

1)CLUSTER を実施

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

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

id:kent013

ありがとうございます。

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の問題だと思います。

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

2011/03/12 00:31:54

その他の回答(5件)

id:JULY No.1

JULY回答回数966ベストアンサー獲得回数2472011/03/08 14:34:52

ポイント22pt

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

11.2. インデックスの種類

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

ただ、

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

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

id:kent013

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

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

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

----

追記

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

2011/03/09 16:09:25
id:pretaroe No.2

pretaroe回答回数531ベストアンサー獲得回数752011/03/08 15:53:53

ポイント3pt

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

id:kent013

kernel.shmmni = 4096

kernel.shmall = 268435456

kernel.shmmax = 4294967295

でした。

2011/03/08 16:22:41
id:windofjuly No.3

うぃんど回答回数2625ベストアンサー獲得回数11492011/03/09 00:41:52ここでベストアンサー

ポイント72pt

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

 

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

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

2)ANALYZE を実施

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

 

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

1)CLUSTER を実施

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

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

id:kent013

ありがとうございます。

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の問題だと思います。

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

2011/03/12 00:31:54
id:toki-2131 No.4

toki-2131回答回数138ベストアンサー獲得回数12011/03/08 18:46:48

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

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

id:kent013

タノシソウデスネ。

2011/03/09 16:07:53
id:chinchin-kozou No.5

chinchin-kozou回答回数198ベストアンサー獲得回数72011/03/08 17:31:41

ポイント2pt

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

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

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

id:kent013

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

2011/03/09 02:46:14
id:tama213 No.6

tama213回答回数486ベストアンサー獲得回数302011/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は有効に使われていてキャッシュも利いてるということですので

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

id:kent013

ありがとうございます。

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

2011/03/09 16:08:23
  • id:taknt
    私には サーバーのスペックをあげることしか 思いつかない・・・
    ソートしておけば 少しは 速くなるかも・・・。


    六千万件で 6秒なら 許容範囲だよねぇって 言うしかない。
    これが 1秒じゃないと 絶対ダメ!?って?
  • id:kent013
    やっぱりそれしかないですかね…個人なのでできればソフトウェア的な努力でどうにかしたいというところが、本音なのですが。
    サーバースペックは
    CPU:Intel(R) Pentium(R) 4 CPU 3.00GHz
    メモリ:4G
    です。

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

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

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

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

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