PostgreSQLで全角半角を意識しない検索(PGroonga)

PostgreSQLで全角半角を意識せずに検索できないかという話をもらいました。MySQL等では照合順の変更で対応できそうですがPostgreSQLではできないようです。どうするのがいいのか考えてみました。

対象環境

今回は以下の環境で実施しました。

Ubuntu 16.04
PostgreSQL 9.6.5
PGroonga 2.0.1

考えられる方法

Google先生でPostgreSQL 全角半角で検索すると結構でてきますね。大体こんな感じでしょうか。

正規化するストアドファンクションを作成し、それを通じた検索を行う
更新時にアプリケーションやDB側のトリガで正規化したデータを格納する列を設ける
はこんなイメージですね。

pgroonga_test=# select * from case_test_table ;
 id |   data   | data_for_re
----+----------+-------------
  1 | ﾎﾟｽｸﾞﾚ   | ポスグレ
  2 | ポスグレ | ポスグレ
(2 rows)

アプリケーションからはdata列に投入し、トリガかアプリケーション側で正規化したデータをdata_for_reという列に格納します。これであれば検索時は必ず全角カナを指定すれば良いです。デメリットとしては領域使用量がほぼ2倍になるということ、INSERT,UPDATE時のオーバヘッドがあるという点です。

はPostgreSQLのtranslateを使った関数を自作すればできるでしょう。以下のようなイメージです。

select * from case_test_table where zisaku_func(data) = zisaku_func(検索ワード);

これであれば、whereの両辺ともに全角半角カナは全角に揃えられるので期待した結果を得られます。しかし、この場合はdata列にINDEXがあっても使用することができないため、式インデックスを作成しておく必要があります。

create index idx_zisaku_func_data on case_test_table(zisaku_func(data));

こんな感じでINDEXを作っておくと左辺でファンクションを使ってもINDEXが効いてきます。

問題点

しかし、1.も2.も以下のようなあいまい検索ではINDEXを使わせる方法がありません。

-- 検索用列を設ける場合
select * from case_test_table where data_re like '%ポスグレ%';
-- 関数で変換する場合
select * from case_test_table where zisaku_func(data) like '%ポスグレ%';

ほとんどのRDBMSではlike検索において%ポスグレや%ポスグレ%といった後方一致・中間一致では標準のINDEXを使うことができません。これを実現するためにPGroongaを使ってみます。

PGroongaで全角・半角を意識しない検索

PGroongaをセットアップしてない方は、公式ドキュメントやこちらを御覧ください。PostgreSQLで全文検索エンジンGroongaを使うようにする拡張、~~PGroongaでは正規化した検索をする方法として&~演算子が用意されています。~~
-> @ktou様よりコメント頂きました。全角半角の正規化は&@や&@~でもそもそも有効だそうです。(ますます使い勝手いいですねPGroonga)

さっそく準備してみます。

-- table作成
CREATE TABLE case_groonga_table (
  id integer,
  content text
);

-- index作成
CREATE INDEX idx_case_groonga_table ON case_groonga_table
USING pgroonga (content pgroonga_text_full_text_search_ops_v2);

-- データ投入
insert into case_groonga_table values(1, '今日はﾎﾟｽｸﾞﾚ日和です。');
insert into case_groonga_table values(2, '昨日はポスグレ日和です。');
insert into case_groonga_table values(3, '今日はﾏｲｴｽｷｭｰｴﾙ日和です。');
insert into case_groonga_table values(4, '昨日はマイエスキューエル日和です。');

これだけだとデータが少ないでのかさ増ししておきます。

-- かさ増しデータ投入
insert into case_groonga_table select t, t||'日前は○racle日和です。' from generate_series(5,100000) t;

これで合計100000行のデータが準備できました。

pgroonga_test=# select count(*) from case_groonga_table ;
 count
--------
 100000
(1 row)

pgroonga_test=# select * from case_groonga_table order by 1 limit 4;
 id |              content
----+------------------------------------
  1 | 今日はﾎﾟｽｸﾞﾚ日和です。
  2 | 昨日はポスグレ日和です。
  3 | 今日はﾏｲｴｽｷｭｰｴﾙ日和です。
  4 | 昨日はマイエスキューエル日和です。
(4 rows)

さて、先頭4行にはそれぞれポスグレとマイエスキューエルという単語が全角カナ・半角カナで表記されたデータが中間部分に入っています。これを取り出すにはlike %...%といった方法になりますが、PGroongaでは&~演算子を使うことになります。

pgroonga_test=# select * from case_groonga_table where content &@ 'ポスグレ';
 id |         content
----+--------------------------
  1 | 今日はﾎﾟｽｸﾞﾚ日和です。
  2 | 昨日はポスグレ日和です。
(2 rows)

pgroonga_test=# select * from case_groonga_table where content &@ 'マイエスキューエル';
 id |              content
----+------------------------------------
  3 | 今日はﾏｲｴｽｷｭｰｴﾙ日和です。
  4 | 昨日はマイエスキューエル日和です。
(2 rows)

期待した結果が得られました。検索条件はどちらも全角カナでしたがちゃんと半角カナの表現も取れていますね。検索条件を半角カナにしても同じ結果です。

pgroonga_test=# select * from case_groonga_table where content &@ 'ﾎﾟｽｸﾞﾚ';
 id |         content
----+--------------------------
  1 | 今日はﾎﾟｽｸﾞﾚ日和です。
  2 | 昨日はポスグレ日和です。
(2 rows)

pgroonga_test=# select * from case_groonga_table where content &@ 'ﾏｲｴｽｷｭｰｴﾙ';
 id |              content
----+------------------------------------
  3 | 今日はﾏｲｴｽｷｭｰｴﾙ日和です。
  4 | 昨日はマイエスキューエル日和です。
(2 rows)

ちゃんとうまくいきますね。

パフォーマンス

パフォーマンスはデータやスペック次第ですのであくまで参考程度ですが、LIKE検索との比較結果を見ておきます。

pgroonga_test=# explain analyze select * from case_groonga_table where content &@ 'ポスグレ';
                                                            QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on case_groonga_table  (cost=12.50..998.75 rows=50000 width=41) (actual time=0.320..0.321 rows=2 loops=1)
   Recheck Cond: (content &@ 'ポスグレ'::text)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on idx_case_groonga_table  (cost=0.00..0.00 rows=1000 width=0) (actual time=0.311..0.311 rows=2 loops=1)
         Index Cond: (content &@ 'ポスグレ'::text)
 Planning time: 0.137 ms
 Execution time: 0.504 ms
(7 rows)

PGroongaを使った場合、0.504 msでした。多少ブレるのか、平均すると1.5msくらいでした。

続いてLIKE検索を使ってみます。LIKEでの中間一致では全角半角カナは正規化されないのでOR条件でつなぎます。
※PGroongaのINDEXを削除して検証しています。

pgroonga_test=# explain analyze select * from case_groonga_table where content like '%ポスグレ%' or content like '%ﾎﾟｽｸﾞﾚ%' ;
                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Seq Scan on case_groonga_table  (cost=0.00..2434.00 rows=20 width=41) (actual time=0.009..29.542 rows=2 loops=1)
   Filter: ((content ~~ '%ポスグレ%'::text) OR (content ~~ '%ﾎﾟｽｸﾞﾚ%'::text))
   Rows Removed by Filter: 99998
 Planning time: 0.125 ms
 Execution time: 29.557 ms
(5 rows)

29.557 msでした。20倍くらいかかりました。まぁ大したデータ量ではないので正直FULL SCANでも大丈夫ですがもっとデータが増えてくるとこの差は大きいでしょう。

まとめ

PGroongaは構築さえしてしまえば結構検索周りは融通がきいて楽しいですね。もう少し試してみたいと思います。

[Dd]enzow(ill)? with DB and Python

DBとか資格とかPythonとかの話をつらつらと

PostgreSQLで全角半角を意識しない検索(PGroonga)

対象環境

考えられる方法

問題点

PGroongaで全角・半角を意識しない検索

パフォーマンス

まとめ