LIKE によるインデックスの使用

 ORDER BY句による順序

ロケール機能が有効になっているデータベースで文字列型に対するORDER BY句を指 定した場合、ロケールに応じた順序で出力されます。以下の表はエンコードUTF-8を使 用した場合の大小関係です。

表 42 ロケール指定による文字列の大小関係

大小関係 ja_JPロケール ロケールなし 備考 小

大

長さ0文字列 長さ0文字列 半角数字 半角数字 半角英字 半角英字 半角カナ ひらがな 全角数字 全角カタカナ 全角英字 全角数字 ひらがな 全角英数 全角カタカナ 半角カナ

NULL NULL

 半角／全角の区別

日本語ロケールが有効なデータベースでも、全角と半角データ別データとして扱われま す。またアルファベットの大文字／小文字も区別されます。

表 43 ロケール指定による大小関係

比較対象１（例） 比較対象２（例） 等しいか 備考 半角アルファベット（A） 全角アルファベット（Ａ） 異なる

半角大文字（A） 半角小文字（a） 異なる 全角カタカナ（ア） 半角カタカナ（ア） 異なる 全角カタカナ（ア） 全角ひらがな（あ） 異なる

例 78 locale使用時のLIKE検索

実行計画がSeq Scanとなり、全件検索が行われていることが確認できます。このよう な事態を避けるためには、CREATE INDEX文実行時にオプションを指定し、バイナリに よるインデックス検索を行うように指定します。

CREATE INDEX文のオプション指定

列のデータ型に応じて以下のオプションを指定することができます。

表 44 バイナリ比較オプション

データ型 オプション 備考 varchar varchar_pattern_ops

char bpchar_pattern_ops text text_pattern_ops name name_pattern_ops

postgres=> CREATE TABLE locale1(c1 varchar(10), c2 varchar(10)) ; CREATE TABLE

postgres=> CREATE INDEX idx1_locale1 ON locale1(c1) ; CREATE INDEX

postgres=> INSERT INTO locale1 VALUES ('ABC', 'DEF') ; INSERT 0 1

…

postgres=> ANALYZE locale1 ; ANALYZE

postgres=> EXPLAIN SELECT C1 FROM locale1 WHERE C1 LIKE 'A%' ; QUERY PLAN

--- Seq Scan on locale1 (cost=0.00..1790.01 rows=10 width=5) Filter: ((c1)::text ~~ 'A%'::text)

Planning time: 1.742 ms (3 rows)

CREATE INDEX インデックス名 ON テーブル名 (列名 オプション)

例 79 オプション指定時のLIKE検索

3.8.3 <> 演算子によるインデックスの使用

LIKE演算子ではオプション指定が必要でしたが、大小を比較する演算子「<」「>」を指 定してインデックスを使用したい場合には、前節のオプションを指定することができませ ん。

postgres=> CREATE INDEX idx2_locale1 ON locale1(c2 varchar_pattern_ops) ; CREATE INDEX

postgres=> \d locale1

Table "public.locale1"

Column | Type | Modifiers ---+---+--- c1 | character varying(10) |

c2 | character varying(10) | Indexes:

"idx1_locale1" btree (c1)

"idx2_locale1" btree (c2 varchar_pattern_ops)

postgres=> EXPLAIN SELECT C2 FROM locale1 WHERE C2 LIKE 'A%' ; QUERY PLAN

--- Index Only Scan using idx2_locale1 on locale1 (cost=0.42..8.44 rows=10 width=5)

Index Cond: ((c2 ~>=~ 'A'::text) AND (c2 ~<~ 'B'::text)) Filter: ((c2)::text ~~ 'A%'::text)

Planning time: 0.541 ms (4 rows)

例 80 オプション指定時の大小比較検索 postgres=> \d locale1

Table "public.locale1"

Column | Type | Modifiers ---+---+--- c1 | character varying(10) |

c2 | character varying(10) | Indexes:

"idx1_locale1" btree (c1)

"idx2_locale1" btree (c2 varchar_pattern_ops)

postgres=> EXPLAIN SELECT c1 FROM locale1 WHERE c1 < '10' ; QUERY PLAN

--- Index Only Scan using idx1_locale1 on locale1 (cost=0.42..334.75 rows=306 width=5)

Index Cond: (c1 < '10'::text) Planning time: 0.210 ms

(3 rows)

postgres=> EXPLAIN SELECT c2 FROM locale1 WHERE c2 < '10' ; QUERY PLAN

--- Seq Scan on locale1 (cost=0.00..1790.01 rows=10 width=5) Filter: ((c2)::text < '10'::text)

Planning time: 0.140 ms (3 rows)