PostgreSQLで全角半角大文字小文字ひらがなカタカナを区別せず検索したい！というよくあるわがままに応える

f:id:piro_suke:20161118011707j:plain

したいしたい！絶対したい！と駄々をこねられたので調査してみた。

こういった区別なし検索を実装する方法としてパッと思いつくのは、

あらかじめ検索対象となるカラムの検索用カラムを用意して、

データ変更時にトリガーで

元カラムの内容を半角小文字英数字カタカナに変換したデータが入るようにしておき、

検索時はその検索用カラムを使用する、という方法。

これはめんどくさそうだ。

SQL Serverは照合順序の設定で制御できるらしい。

照合順序と Unicode のサポート

PostgreSQLも同じことができないかと調べてみたけど、対応してなさそう。

第22章多言語対応

他に方法がないか調べてみると、

「式インデックス」を使って、自作の変換用関数で変換したデータを

インデックスに登録しておく方法を試しているページがあった。

PostgreSQLで全角半角を区別しない問い合わせ

この方法なら少なくとも検索用カラムを作成する必要はなさそう。

試してみる

色々なデータを用意して実際にどのように検索されるか、

試しながら進んでみよう。

環境はCentOS + PostgreSQL 9.5。

まずは下記のような、

商品コードと商品名を持つ商品マスタ風テーブルを作る：

drop table if exists ignore_case_products;
create table ignore_case_products (
    item_cd varchar(100)
    ,name varchar(255)
);

作成したテーブルにいろんな文字種の商品名を持つリンゴデータを入れておく：

insert into ignore_case_products(item_cd, name)
values
('item1', 'りんご')
,('item2', 'リンゴ')
,('item3', 'ﾘﾝｺﾞ')
,('item4', 'APPLE')
,('item5', 'Apple')
,('item6', 'apple')
,('item7', 'ＡＰＰＬＥ')
,('item8', 'ａｐｐｌｅ')
,('item9', 'Ａｐｐｌｅ')
;

この状態で全角半角、大文字小文字が区別されることを確認する。

select * from ignore_case_products where name like 'リンゴ';

 item_cd |  name  
---------+--------
 item2   | リンゴ
(1 行)

select * from ignore_case_products where name like 'ﾘﾝｺﾞ';

 item_cd | name 
---------+------
 item3   | ﾘﾝｺﾞ
(1 行)

カタカナは全角半角しっかり区別されてる。

上記2sqlはlikeをilikeに変更しても結果は同じ。

英字も試してみよう。

select * from ignore_case_products where name like 'apple';

 item_cd | name  
---------+-------
 item6   | apple
(1 行)

上記それぞれlikeを=にしても結果は同じ。

大文字小文字を区別しないilikeで試してみよう。

select * from ignore_case_products where name ilike 'apple';

 item_cd | name  
---------+-------
 item4   | APPLE
 item5   | Apple
 item6   | apple
(3 行)

半角英字のデータが大文字小文字区別なくヒットする。

全角英文字はどうか。

select * from ignore_case_products where name ilike 'ａｐｐｌｅ';

 item_cd |    name    
---------+------------
 item7   | ＡＰＰＬＥ
 item8   | ａｐｐｌｅ
 item9   | Ａｐｐｌｅ
(3 行)

全角の大文字小文字英字データが区別なくヒットした。

ilikeは英数字なら全角でも大文字小文字区別をしないとは知らなかった。

変換用関数を作成する

今回は半角カナ、全角カナ、ひらがなも区別なく検索したいので、

やはり変換関数を作る必要がありそうだ。

テキストを渡すと、

半角カナ、ひらがなを全角カナに変換し、

全角英数字を半角小文字英数字に変換して返す関数を作る。

drop function if exists sf_translate_case(text);
create function sf_translate_case(
    p_src text
) returns text as $$
declare
    text_result text := upper(p_src);
    arr_hankaku_daku_kanas text[] := array['ｳﾞ', 'ｶﾞ', 'ｷﾞ', 'ｸﾞ', 'ｹﾞ', 'ｺﾞ', 'ｻﾞ', 'ｼﾞ', 'ｽﾞ', 'ｾﾞ', 'ｿﾞ', 'ﾀﾞ', 'ﾁﾞ', 'ﾂﾞ', 'ﾃﾞ', 'ﾄﾞ', 'ﾊﾞ', 'ﾋﾞ', 'ﾌﾞ', 'ﾍﾞ', 'ﾎﾞ', 'ﾊﾟ', 'ﾋﾟ', 'ﾌﾟ', 'ﾍﾟ', 'ﾎﾟ'];
    arr_zenkaku_daku_kanas text[] := array['ヴ', 'ガ', 'ギ', 'グ', 'ゲ', 'ゴ', 'ザ', 'ジ', 'ズ', 'ゼ', 'ゾ', 'ダ', 'ヂ', 'ヅ', 'デ', 'ド', 'バ', 'ビ', 'ブ', 'ベ', 'ボ', 'パ', 'ピ', 'プ', 'ペ', 'ポ'];
    text_hankaku_kanas text := 'ｱｲｳｴｵｶｷｸｹｺｻｼｽｾｿﾀﾁﾂﾃﾄﾅﾆﾇﾈﾉﾊﾋﾌﾍﾎﾏﾐﾑﾒﾓﾔﾕﾖﾗﾘﾙﾚﾛﾜｦﾝｧｨｩｪｫｯｬｭｮ';
    text_zenkaku_hiras text := 'あいうえおかきくけこさしすせそたちつてとなにぬねのはひふへほまみむめもやゆよらりるれろわをんぁぃぅぇぉっゃゅょ';
    text_zenkaku_kanas text := 'アイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨラリルレロワヲンァィゥェォッャュョ';
    text_zenkaku_daku_hiras text := 'がぎぐげござじずぜぞだぢづでどばびぶべぼぱぴぷぺぽ';
    text_zenkaku_daku_kanas text := 'ガギグゲゴザジズゼゾダヂヅデドバビブベボパピプペポ';
    text_hankaku_symbols text := '｡｢｣､･ｰ－';
    text_zenkaku_symbols text := '。「」、・ー-';
    text_zenkaku_space text := '　';
    text_hankaku_space text := ' ';
    text_zenkaku_nums text := '０１２３４５６７８９';
    text_hankaku_nums text := '0123456789';
    text_zenkaku_upper_alphabets text := 'ＡＢＣＤＥＦＧＨＩＪＫＬＭＮＯＰＱＲＳＴＵＶＷＸＹＺ';
    text_zenkaku_lower_alphabets text := 'ａｂｃｄｅｆｇｈｉｊｋｌｍｎｏｐｑｒｓｔｕｖｗｘｙｚ';
    text_hankaku_upper_alphabets text := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
begin
    if p_src is null or p_src = '' then
        return p_src;
    end if; 

    for i in 1..array_length(arr_hankaku_daku_kanas, 1) loop
        text_result := replace(text_result, arr_hankaku_daku_kanas[i], arr_zenkaku_daku_kanas[i]);
    end loop;

    text_result := translate(
        text_result
        , text_hankaku_kanas || text_zenkaku_hiras || text_zenkaku_daku_hiras || text_hankaku_symbols || text_zenkaku_space || text_zenkaku_upper_alphabets || text_zenkaku_lower_alphabets
        , text_zenkaku_kanas || text_zenkaku_kanas || text_zenkaku_daku_kanas || text_zenkaku_symbols || text_hankaku_space || text_hankaku_upper_alphabets || text_hankaku_upper_alphabets
    );  

    return text_result;
end;
$$ language plpgsql immutable

immutableをデータ変更を行わない関数であることを宣言してる。

35.6. 関数の変動性分類

式インデックスで指定する関数はimmutableでないといけないようだ。

作った関数が期待通り区別なし検索してくれるか確認してみよう。

select * from ignore_case_products where sf_translate_case(name) = sf_translate_case('りんご');
select * from ignore_case_products where sf_translate_case(name) = sf_translate_case('リンゴ');
select * from ignore_case_products where sf_translate_case(name) = sf_translate_case('ﾘﾝｺﾞ');

-- likeも試す
select * from ignore_case_products where sf_translate_case(name) like '%' || sf_translate_case('リン') || '%';

いずれも下記の結果となった。

期待通り全角半角ひらがなカタカナを区別せず検索できてる。

 item_cd |  name  
---------+--------
 item1   | りんご
 item2   | リンゴ
 item3   | ﾘﾝｺﾞ
(3 行)

続いて英字検索もテスト。

select * from ignore_case_products where sf_translate_case(name) = sf_translate_case('apple');
select * from ignore_case_products where sf_translate_case(name) = sf_translate_case('Apple');
select * from ignore_case_products where sf_translate_case(name) = sf_translate_case('APPLE');
select * from ignore_case_products where sf_translate_case(name) = sf_translate_case('ａｐｐｌｅ');
select * from ignore_case_products where sf_translate_case(name) = sf_translate_case('Ａｐｐｌｅ');
select * from ignore_case_products where sf_translate_case(name) = sf_translate_case('ＡＰＰＬＥ');

こちらも期待通り下記のように大文字小文字全角半角を区別しない結果となる。

 item_cd |    name    
---------+------------
 item4   | APPLE
 item5   | Apple
 item6   | apple
 item7   | ＡＰＰＬＥ
 item8   | ａｐｐｌｅ
 item9   | Ａｐｐｌｅ
(6 行)

式インデックスを使う

大量データから検索する場合はインデックスが効かないと

使い物にならないと思われるので、

インデックスなし、普通のカラムインデックス、式インデックスで

パフォーマンスを確認してみよう。

速度差を確認できるように10万件くらいデータを登録して検証する。

まずはインデックスなしで。

insert into ignore_case_products(item_cd, name)
select
  format('item%s', i)
  ,format('テスト商品%s', i)
from
  generate_series(10, 100000) as i
;

データ登録は2秒程度で完了。

このデータに対してクエリを投げて速度を確認する。

変換関数を使わない一致検索の場合：

explain analyze select * from ignore_case_products where name = 'リンゴ';

Seq Scan on ignore_case_products  (cost=0.00..1986.00 rows=1 width=29) (actual time=0.048..25.562 rows=1 loops=1)
   Filter: ((name)::text = 'リンゴ'::text)
   Rows Removed by Filter: 99999
 Planning time: 0.603 ms
 Execution time: 25.636 ms

25ms。

続いて、変換関数で一致検索：

explain analyze select * from ignore_case_products where sf_translate_case(name) = sf_translate_case('リンゴ');

Seq Scan on ignore_case_products  (cost=0.00..26986.00 rows=500 width=29) (actual time=0.137..7240.018 rows=3 loops=1)
   Filter: (sf_translate_case((name)::text) = 'リンゴ'::text)
   Rows Removed by Filter: 99997
 Planning time: 0.407 ms
 Execution time: 7240.080 ms

7240ms（7.2秒）。めちゃ重。

変換関数 + likeも試してみよう。

explain analyze select * from ignore_case_products where sf_translate_case(name) like '%' || sf_translate_case('リン') || '%';

Seq Scan on ignore_case_products  (cost=0.00..26986.00 rows=32 width=29) (actual time=0.144..7705.297 rows=3 loops=1)
   Filter: (sf_translate_case((name)::text) ~~ '%リン%'::text)
   Rows Removed by Filter: 99997
 Planning time: 5.114 ms
 Execution time: 7705.359 ms

7705ms。一致検索とはそんなに変わらない。

次は一旦データを削除して、

name列に普通のカラムインデックスを貼って試してみる。

create index ignore_case_product_name
on ignore_case_products(name);

先程と同じデータ（約10万件）を再登録する。

データ登録完了まで1〜2分。インデックスを登録する分登録時間は長くなった。

先程と同じく、変換関数なしの一致検索の場合：

explain analyze select * from ignore_case_products where name = 'リンゴ';

Index Scan using ignore_case_product_name on ignore_case_products  (cost=0.42..8.44 rows=1 width=29) (actual time=0.069..0.070 rows=1 loops=1)
   Index Cond: ((name)::text = 'リンゴ'::text)
 Planning time: 0.707 ms
 Execution time: 0.130 ms

インデックスが使われて、0.1ms。

続いて変換関数を使った一致検索：

explain analyze select * from ignore_case_products where sf_translate_case(name) = sf_translate_case('リンゴ');

 Seq Scan on ignore_case_products  (cost=0.00..26986.00 rows=500 width=29) (actual time=0.124..6980.943 rows=3 loops=1)
   Filter: (sf_translate_case((name)::text) = 'リンゴ'::text)
   Rows Removed by Filter: 99997
 Planning time: 3.186 ms
 Execution time: 6980.969 ms

6980ms。インデックスは使われない。

変換関数 + likeも試す。

explain analyze select * from ignore_case_products where sf_translate_case(name) like '%' || sf_translate_case('リン') || '%';

Seq Scan on ignore_case_products  (cost=0.00..26986.00 rows=32 width=29) (actual time=0.108..7269.832 rows=3 loops=1)
   Filter: (sf_translate_case((name)::text) ~~ '%リン%'::text)
   Rows Removed by Filter: 99997
 Planning time: 0.393 ms
 Execution time: 7269.860 ms

7269ms。こちらも予想通りインデックスは使われない。

カラムインデックスしか選択肢がない場合、

変換関数を使った検索はインデックスを使ってくれないので、

あらかじめ検索用カラムに変換関数を通したデータを登録しておく必要があるということか。

最後に式インデックスで試す。

テーブルを作り直して、下記のインデックスを貼る。

変換関数を通したnameカラムにインデックスを貼る。

drop index if exists ignore_case_product_name;
create index ignore_case_product_name
on ignore_case_products(sf_translate_case(name));

また同じデータ（約10万件）を再登録して検証。

データ登録完了まで1〜2分。普通のインデックス登録と変わらない。

変換関数を通さない一致検索の場合：

explain analyze select * from ignore_case_products where name = 'リンゴ';

 Seq Scan on ignore_case_products  (cost=0.00..828.00 rows=37 width=734) (actual time=0.079..34.601 rows=1 loops=1)
   Filter: ((name)::text = 'リンゴ'::text)
   Rows Removed by Filter: 99999
 Planning time: 0.828 ms
 Execution time: 34.657 ms

変換関数を使っていないので、式インデックスは使われず、34ms。

続いて変換関数を使った一致検索：

explain analyze select * from ignore_case_products where sf_translate_case(name) = sf_translate_case('リンゴ');

 Index Scan using ignore_case_product_name on ignore_case_products  (cost=0.42..16.51 rows=7 width=29) (actual time=0.069..0.070 rows=3 loops=1)
   Index Cond: (sf_translate_case((name)::text) = 'リンゴ'::text)
 Planning time: 5.417 ms
 Execution time: 0.121 ms

式インデックスが使われて0.1ms。求めてた結果。

変換関数 + likeも試す。

explain analyze select * from ignore_case_products where sf_translate_case(name) like '%' || sf_translate_case('リン') || '%';

 Seq Scan on ignore_case_products  (cost=0.00..26986.00 rows=17 width=29) (actual time=0.101..7478.995 rows=3 loops=1)
   Filter: (sf_translate_case((name)::text) ~~ '%リン%'::text)
   Rows Removed by Filter: 99997
 Planning time: 0.452 ms
 Execution time: 7479.053 ms

7479ms。こちらは式インデックスが使われない。

元々PostgreSQLの中間一致検索はpg_bigm等を使わないと

インデックスは使われないので、残念だけど予想通り。