pg_bigmって知ってる?PostgreSQLのpg_trgmの日本語対応版で全文検索をやってみた。

  • このエントリーをはてなブックマークに追加
  • Pocket

ども(=・ω・)ノ

この前ラーメン屋で、麺の硬さに「粉落とし」を選んで食べたら、腹痛になった新川です。美味しかったけど、やっぱちゃんと火が通ってないとダメですねw 

 

さて、今日はデータベースのPostgreSQLで全文検索をやってみようと思います。

 

データベースの全文検索とは?

全文検索はWikipediaでは、以下のように書かれています。

 

全文検索とは、コンピュータにおいて、複数の文書(ファイル)から特定の文字列を検索すること。「ファイル名検索」や「単一ファイル内の文字列検索」と異なり、「複数文書にまたがって、文書に含まれる全文を対象とした検索」という意味で使用される。

 

 

データベースの全文検索は、検索対象が文書ファイルではなくテーブルの文字列データになったものです。

 

以下のSQLの様に、ワイルドカードを使用して文字列を含むレコードを検索すれば、全文検索を使用しなくても同じ結果を得ることができます。

SELECT <列名> FROM <表名> WHERE <列名> LIKE ‘%<キーワード>%’

 

ですがその場合、全データを走査(スキャン)して文字列を探すため、データ量に比例した検索時間が掛かるという問題があります。

そこでデータベースにも全文検索を行うための技術が用意されています。それが今回ご紹介する「pg_trgm」です。

 

 

PostgreSQLの全文検索

PostgreSQLには、もともと pg_trgm という全文検索を行うためのモジュールが付属しています。しかし、日本語で pg_trgm 使用するには以下の問題がありました。

  • 1〜2文字の検索ではインデックスを有効に使えない(全データの走査より遅くなる)。
  • 日本語に対応させるには、pg_trgm のソースコードを変更する必要がある。

 

これらの問題を解消した、pg_bigm というものをNTTデータが開発しました。

 

 

pg_bigm をインストールしてみよう

pg_bigm のダウンロード

では、pg_bigm をインストールし、PostgreSQL で使用できるようにしてみましょう。

 

今回、PostgreSQL は 9.3.5 を使用します。PostgreSQL 9.1~9.3用の pg_bigm 1.1 を、以下からダウンロードしてきます。

 

パッケージ pg_bigm – pg_bigm(ピージーバイグラム) – SourceForge.JP
ダウンロードファイル: pg_bigm-1.1-20131122.tar.gz

 

 

pg_bigm のインストール

圧縮されているファイルを展開し、展開されたディレクトリに移動します。

 

[root]# su – postgres
[postgres]$ tar zxf pg_bigm-1.1-20131122.tar.gz
[postgres]$ cd pg_bigm-1.1-20131122
[postgres]$

 

以下の通り、インストールを行います。

 

 [postgres]$ make USE_PGXS=1 PG_CONFIG=/opt/postgresql/bin/pg_config
: 省略
[postgres]$ make USE_PGXS=1 PG_CONFIG=/opt/postgresql/bin/pg_config install
: 省略
[postgres]$

  • USE_PGXS  : PostgreSQL関連モジュールを追加インストールする際に、USE_PGXS=1の指定が必須となります。
  • PG_CONFIG : PostgreSQLインストール先のbinディレクトリにある、pg_configのパスを指定します。

 

 

PostgreSQL への pg_bigm 登録

pg_bigm はモジュールですので、インストールしただけでは PostgreSQL では使用されません。PostgreSQL にインストールしたモジュールを登録します。

postgresql.conf を編集し、以下の通りパラメータを設定します。

 

[postgres]$ vi $PGDATA/postgresql.conf
shared_preload_libraries = ‘pg_bigm’

PostgreSQL 9.1 以前では、custom_variable_classes パラメータも同様に設定します。
PostgreSQL 9.2 以降は、custom_variable_classes パラメータが存在しないため不要です。

 

データベースに接続し、pg_bigm を登録します。

 

[postgres]$ psql -d db1
psql (9.3.5)
Type “help” for help.
db1=#
db1=#
db1=# CREATE EXTENSION pg_bigm;
CREATE EXTENSION
db1=#

 

 

データベースに pg_bigm が登録されたことを確認します。

 

db1=# \dx pg_bigm
List of installed extensions
Name   | Version | Schema |                           Description
———+———+——–+——————————————————————
pg_bigm | 1.1     | public | text similarity measurement and index searching based on bigrams
(1 row)

 

 

 

全文検索をやってみよう

住所データのダウンロード

では、実際に全文検索をやってみましょう。

全文検索用のデータを自作するのは大変なので、今回は無料で取得できる住所データを使用します。日本全国の住所データを以下からダウンロードします。

 

住所データCSV【住所.jp】
ダウンロードファイル : csv_zenkoku.zip

 

解凍したダウンロードファイル(CSVファイル)を、/tmp に格納しておきます。

 

 

住所データの取り込み、全文検索用インデックスの作成

データベースに住所データ用のテーブルを作成します。

 

db1=# create table JP_NATIONAL_ADDRESS (
db1(#     “住所CD” integer,
db1(#     “都道府県CD” integer,
db1(#     “市区町村CD” integer,
db1(#     “町域CD” integer,
db1(#     “郵便番号” text,
db1(#     “事業所フラグ” integer,
db1(#     “廃止フラグ” integer,
db1(#     “都道府県” text,
db1(#     “都道府県カナ” text,
db1(#     “市区町村” text,
db1(#     “市区町村カナ” text,
db1(#     “町域” text,
db1(#     “町域カナ” text,
db1(#     “町域補足” text,
db1(#     “京都通り名” text,
db1(#     “字丁目” text,
db1(#     “字丁目カナ” text,
db1(#     “補足” text,
db1(#     “事業所名” text,
db1(#     “事業所名カナ” text,
db1(#     “事業所 住所” text,
db1(#     “新住所CD” integer
db1(# );
CREATE TABLE
db1=#

 

 

作成したテーブルに、住所データを取り込みます。

from句には住所データのCSVファイルのパスを指定します。住所データには先頭行にヘッダ情報があるため、\copyでデータを取り込む際には csv と header オプションを指定します。また、住所データの文字コードは SJIS なので、encoding ‘sjis’ オプションも指定します。

 

db1=# \copy JP_NATIONAL_ADDRESS from /tmp/zenkoku.csv with csv header encoding ‘sjis’
db1=#

 

 

全文検索を行うためのインデックスを作成します。インデックスキーには、”市区町村” と “町域” を指定します。

 

db1=# create index JP_NATIONAL_ADDRESS_IDX on JP_NATIONAL_ADDRESS using gin (“市区町村” gin_bigm_ops, “町域” gin_bigm_ops);
CREATE INDEX
db1=#

 

 

全文検索の実行

では、いよいよ全文検索です。

検索条件は以下の通り、適当に自分の名前を使用してみました。

 

条件 : “市区町村” で 新 を含むもの、かつ “町域” で 川 を含むもの。

 

db1=# select “都道府県” || “市区町村” || “町域” 住所 from JP_NATIONAL_ADDRESS where “市区町村” like ‘%新%’ and “町域” li ke ‘%川’%’;
住所
———————————-
北海道石狩郡新篠津村川上
: 省略
長崎県南松浦郡新上五島町有川郷
(118 rows)

 

118件も検索にヒットしましたねw

 

 

全文検索による効果は?

全文検索での実行時間

全文検索をやってみたわけですが、これだけじゃ普通の検索と何が違うのかわかりませんよね。そこで、実際に検索にどれだけの時間が掛かるのか見てみましょう。

 

まずは、全文検索時の実行時間を見てみましょう。

SQLの実行時間を確認するには、explain analyze を使用します。

 

db1=# explain analyze select “都道府県” || “市区町村” || “町域” 住所 from JP_NATIONAL_ADDRESS where “市区町村” like ‘%新 %’ and “町域” like ‘%川%’;
QUERY PLAN
————————————————————————————————————————
Bitmap Heap Scan on jp_national_address  (cost=57.71..606.97 rows=166 width=34) (actual time=5.998..6.340 rows=118 loops=1)
Recheck Cond: ((“市区町村” ~~ ‘%新%’::text) AND (“町域” ~~ ‘%川%’::text))
->  Bitmap Index Scan on jp_national_address_idx  (cost=0.00..57.66 rows=166 width=0) (actual time=5.926..5.926 rows=118 loops=1)
Index Cond: ((“市区町村” ~~ ‘%新%’::text) AND (“町域” ~~ ‘%川%’::text))
Total runtime: 6.474 ms
(5 rows)

 

全文検索では、6.474 ミリ秒掛かったようです。

 

 

通常の検索(全データの走査)での実行時間

以下を実行し、pg_bigm での全文検索(ビットマップスキャン)を行わないように設定します。

 

db1=# set enable_bitmapscan to off;
SET
db1=#

 

 

全文検索の時と同じく、explain analyze を使用し、SQLの実行時間を見てみます。

 

db1=# explain analyze select “都道府県” || “市区町村” || “町域” 住所 from JP_NATIONAL_ADDRESS where “市区町村” like ‘%新 %’ and “町域” like ‘%川%’;
QUERY PLAN
———————————————————————————————————————-
Seq Scan on jp_national_address  (cost=0.00..5688.64 rows=166 width=34) (actual time=3.141..51.322 rows=118 loops=1)
Filter: ((“市区町村” ~~ ‘%新%’::text) AND (“町域” ~~ ‘%川%’::text))
Rows Removed by Filter: 148069
Total runtime: 51.426 ms
(4 rows)

 

通常の検索では 51.426 ミリ秒掛かったようですね。

 

 

検索時間の比較結果は以下のようになりました。

  • 全文検索 : 6.474 ミリ秒
  • 通常検索 : 51.426 ミリ秒

 

今回のデータと検索条件では、検索時間に約8倍の違いがでました。もっと大量のデータだったりする場合は、より顕著に効果が現れるのではないでしょうか。

 

 

まとめ

今回は、PostgreSQLの全文検索について紹介しましたが、他のデータベースでも以下の通り、全文検索の機能が存在します。

  • Oracle Database : Oracle Text
  • MySQL : FULLTEXTインデックス、Mroonga(オープンソースソフトウェア)など

 

データベースの全文検索は、知っている人には当たり前かも知れませんが、知らないという人は結構多い様です。かく言う私も、最近までまったく知りませんでしたw

単純に文字列データを検索しており、SQLチューニングを行ってもなかなかパフォーマンスが改善しない場合など、全文検索という機能を使用してみてはいかがでしょうか。

  • このエントリーをはてなブックマークに追加
  • Pocket

SNSでもご購読できます。

コメントを残す

*