PostgreSQLでpg_bigmを使って中間一致like検索を高速化する

f:id:piro_suke:20161119021025j:plain

昨日書いた下記記事の続き。

blog.honjala.net

全角半角大文字小文字ひらがなカタカナを区別せず高速検索できるようには

なったのだけど、肝心のlike検索でインデックスが使えず

遅いままだったので、pg_bigmを導入してこれを解決する。

pg_bigmは簡単に言うと日本語の中間一致like検索でインデックスを

使えるようにしてくれるPostgreSQL用モジュール。

本家サイトはこちら：

pg_bigm

pg_bigmの導入方法と使い方についてはこちらのSlideShareを参考にした：

PostgreSQLでpg_bigmを使って日本語全文検索（MySQLとPostgreSQLの日本語全文検索勉強会発表資料） from NTT DATA OSS Professional Services

www.slideshare.net

pg_bigmを導入する

pg_bigmはRPM形式で下記ページからダウンロードできる。

ja.osdn.net

今回試した環境は CentOS + PostgreSQL 9.5 なので、

「pg_bigm-1.2.20161011-1.pg95.el6.x86_64.rpm」をダウンロードした。

ダウンロード後、下記のコマンドでインストールする：

# rpm -i pg_bigm-1.2.20161011-1.pg95.el6.x86_64.rpm

その後の作業は上記SlideShareの19ページに書かれている通り。

postgresql.confのshared_preload_librariesに「pg_bigm」を追記し、

一度PostgreSQLを再起動する。

次に、pg_bigmを使いたいデータベースに管理者としてログインし、

create extension pg_bigm;

を実行する。

これでそのデータベースでpg_bigmが利用可能となる。

リンゴ検索でpg_bigmインデックスを使用する

早速昨日の記事で作成したリンゴ検索データベースで

pg_bigmを使った検索を試してみよう。

まずは式インデックスではなく通常のpg_bigmのインデックスで

like検索の速度を確認する。

昨日と同じく下記のテーブルを使用する。

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

name列にpg_bigmインデックスを貼る：

create index ignore_case_product_name_bigm
on ignore_case_products using gin (name gin_bigm_ops);

テーブルに下記のSQLで10万件程度のデータを入れる。

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

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

登録は5秒程度で終わった。

普通にname列にカラムインデックスを貼る場合は1〜2分かかったのと

比較して、大分速い。

日本語中間一致検索を試してみる：

select * from ignore_case_products where name like '%ン%';
-- 下記でも同じ（likequery関数はpg_bigmインストール時に追加される）
select * from ignore_case_products where name like likequery('ン');

結果

 item_cd |  name  
---------+--------
 item2   | リンゴ

explain analyze内容

Bitmap Heap Scan on ignore_case_products  (cost=16.08..52.71 rows=10 width=29) (actual time=0.026..0.027 rows=1 loops=1)
   Recheck Cond: ((name)::text ~~ '%ン%'::text)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on ignore_case_product_name_bigm  (cost=0.00..16.07 rows=10 width=0) (actual time=0.018..0.018 rows=1 loops=1)
         Index Cond: ((name)::text ~~ '%ン%'::text)
 Planning time: 0.188 ms
 Execution time: 0.067 ms

0.06ms。

区別なし検索はされないが、インデックスが効いて速い。

続いて、式インデックスが使えるか試してみよう。

テーブルをcreateしなおし、昨日作成したsf_translate_caseを使って

下記の式インデックスを登録する：

drop index if exists ignore_case_product_name_bigm;
create index ignore_case_product_name_bigm
on ignore_case_products using gin (sf_translate_case(name) gin_bigm_ops);

登録後、先程と同じ10万件データを登録する。

登録にかかった時間は15秒程度。多少伸びたけど速い。

検索してみよう。まずは日本語。

select * from ignore_case_products where sf_translate_case(name) like likequery(sf_translate_case('リン'));

結果はインデックスなしの時と変わらず、

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

explain analyzeは

Bitmap Heap Scan on ignore_case_products  (cost=16.08..55.21 rows=10 width=29) (actual time=0.027..0.028 rows=3 loops=1)
   Recheck Cond: (sf_translate_case((name)::text) ~~ '%リン%'::text)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on ignore_case_product_name_bigm  (cost=0.00..16.07 rows=10 width=0) (actual time=0.020..0.020 rows=3 loops=1)
         Index Cond: (sf_translate_case((name)::text) ~~ '%リン%'::text)
 Planning time: 0.417 ms
 Execution time: 0.082 ms

0.08ms。インデックスがしっかり効いて速い。

英字も試す。

select * from ignore_case_products where sf_translate_case(name) like likequery(sf_translate_case('ppl'));

結果は

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

explain analyzeは

 Bitmap Heap Scan on ignore_case_products  (cost=28.13..92.84 rows=17 width=29) (actual time=0.136..0.640 rows=6 loops=1)
   Recheck Cond: (sf_translate_case((name)::text) ~~ '%PPL%'::text)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on ignore_case_product_name_bigm  (cost=0.00..28.12 rows=17 width=0) (actual time=0.021..0.021 rows=6 loops=1)
         Index Cond: (sf_translate_case((name)::text) ~~ '%PPL%'::text)
 Planning time: 0.361 ms
 Execution time: 0.683 ms

0.68ms。

検索対象文字数が増えると、それに比例して検索にかかる時間は増えるようだ。