PostgreSQL ロケールとLIKE述語そしてpgAdminでの注意

2015年6月6日

ごきげんよう。


前回の記事
PostgreSQL B-treeインデックスの動作検証

記事中に、LIKE述語の前方一致でインデックスが動かないという現象がありましたが、
ありがたいことに記事にコメントをいただきました。

ごきげんよう。LIKEの前方一致ですが、
CREATE INDEX index_moji ON index_test(moji text_pattern_ops);
と、列名の後に text_pattern_ops を付けてインデックスを作り直してみて下さい。
こちらでやったら、ロケールが「C」のDBなら初めからIndex Scanで、
ロケールがJapanese_Japan.932のDBでは記事と同様で、…ops付けたらインデックスが使われました。

kenpg / koda さんありがとうございます！

というわけで

データベースのロケールを見てみました。

・・・・・

ご指摘の通り

Japanese_Japan.932

になってました・・・・

で、提示していただいたインデックスを作り直して再度検証してみました。

CREATE INDEX index_moji ON index_test(moji text_pattern_ops);

これで前方一致のクエリを再投入してみます。

EXPLAIN ANALYSE SELECT * FROM index_test WHERE moji LIKE 'hoge12345%';

"Index Scan using index_moji on index_test  (cost=0.42..8.45 rows=100 width=18) (actual time=0.292..0.440 rows=11 loops=1)"

"  Index Cond: ((moji ~>=~ 'hoge12345'::text) AND (moji ~<~ 'hoge12346'::text))"

"  Filter: (moji ~~ 'hoge12345%'::text)"

"Planning time: 4.006 ms"

"Execution time: 0.484 ms"

おおおお！

無事にIndex Scanが使われました！

さて、ここで疑問。

ロケールに

Japanese_Japan.932

なんて指定した覚えがないぞ？

まさか・・・デフォルトがこうなってる？

そこで、新しくデータベース（new_db）を

pgAdminのデフォルトのまま、ほいほい作ってみます。

データベースを作る際に「定義」のタブをみてみると

コーレーションと文字型が空白ですね。

ここでOKボタンを押下して、データベースを作ると・・・

なんと！

コーレーションと文字型がJapanese_Japan.932になってます！

ぐぬぬぬ・・・って感じです。

なので、新しくデータベースをロケールCで作ってみます。

定義タブで

コーレーションと文字型にCを選択します。

ちなみに

ここでOKを押してもエラーがでます。

これはTemplateにtemplate0を選んであげれば動いてくれます。

#　Templateについては

#　ひとまずデータベース作成の際の雛形みたいなものだと認識してください。

#　詳細は各自で検索をお願いします。

で、Templateにtemplate0を選んであげてOKボタンを押下してあげると

コーレーションと文字型がCの新規データベース（testdb_c）が出来上がります。

ここで前回記事と同じように

テーブルとインデックスを作ってあげます。

COPY文で再びデータをindex_testテーブルに入れてあげて

前方一致のLIKE述語のクエリを実行してみます。

EXPLAIN ANALYSE SELECT * FROM index_test WHERE moji LIKE 'hoge12345%';

"Index Scan using index_moji on index_test  (cost=0.42..8.45 rows=100 width=18) (actual time=0.074..0.195 rows=11 loops=1)"

"  Index Cond: ((moji >= 'hoge12345'::text) AND (moji < 'hoge12346'::text))"

"  Filter: (moji ~~ 'hoge12345%'::text)"

"Planning time: 15.047 ms"

"Execution time: 0.225 ms"

おおおお！！

無事にIndex Scanが選ばれました。

というわけで

PostgreSQL9.4.2をpgAdminⅢ（1.20.0）で使用する際に

ロケールも気を付けないといけません。

# 設定や初回起動等によるのでしょうが

ちなみに、今回ご指摘をいただいた

ロケールとtext_pattern_opsについては

Let's Postgresで記事になってました。

ご興味がある方はご参考までに。

Let's Postgres 国際化と地域化

では、今回はこれにて失礼します。

何か補足や「何言ってんだこいつ」ってのがありましたら

コメント欄にご記入をお願いいたします。