Postgresqlの前方一致でインデックスが使われない場合

右上に表示されてる辞書を作るにあたって，データベースが何か妙に遅いので原因を調べてみた。

キーボードからの入力があるたびにDBで前方一致検索をかけてるわけですが，そのクエリを分析させてみると， 折角インデックスが張ってあるのにシーケンシャルスキャンしてることがわかった。

このマシン，まぁVPSの最安プランで借りてるから，速度なんてそれほど期待できないだろう…とか思ってたんですが， それにしてもちょっと遅すぎる。例えば，”home”で始まる英単語を調べるのに，約150ミリ秒かかってた。（ちなみにテーブルサイズは11Mほど）

だけどそれ以上に，折角作ってあるインデックスが使われてないってことが問題で，何でなのか調べてみることに。

すると，postgresqlではデータベースクラスタを作る際に，ロケールが指定されてるとLIKE/正則表現の前方一致検索でインデックスが使われないってことが判明。ちょっと前のMLだけど，Postgresql8.3でもそうみたい。

そこで，一旦今までのDBを構造とデータの両方ダンプさせてから，

database$ initdb -E UTF-8 --no-locale

すると，今まで150msかかってた検索が，26ms3msでできるように。もちろん，インデックススキャンされてます。（でもフレームワークの制約で，劇的には速くならなかった。）

ここ何年もPostgres使ってるけど，全文検索用には ludia 入れてたりしたんで，あんまり LIKE 演算子のことは考えたことなかった。

というわけで，右上の辞書，何と日本語でも検索可能になりました！

（こちらの都合で，漢字がある単語の場合は漢字でないとマッチしません）

まぁ DB 速くしても，色々あって結局殆ど変わらないんですけどねorz

追記

APC入れてサイト全体の速度を上げて，更にプログラムとSQLの無駄を出来る限り除いてったけど， どうしてもメモリが少ない所為で検索効率が悪くなるみたい。

さらに追記

LIKE演算子ならインデックスが使われてたんだけど，ILIKEだと使われてなかった。でもLIKEにすると，ちょっと使い心地が悪くなる。（本当は辞書に載ってるのに，大文字小文字の違いで出てこない場合がある。）

なので，

CREATE INDEX "インデックス名" ON "テーブル名" USING btree (LOWER("カラム名"::text))

とかして小文字でインデックスを作って，

SELECT * FROM "テーブル名" WHERE LOWER("カラム名") LIKE LOWER('調べる文字列%')

ってすると，インデックスを使い，かつ大文字小文字を無視した検索ができる。