今回はPostgreSQLでpg_bigmを試してみます。
SQLで全文検索を考えたとき、とりあえずデータ量が多くなければLIKE検索でできます。
select data from small_table where data like '%りんご%';
%りんご%の指定により、文中のどこかでりんごが含まれるデータを検索することができます。%は正規表現でいうところの*+に相当し、0文字以上の何かに相当します。
しかし、この方法ではほとんどのRDBMSではINDEXが使えないためTABLEすべてにアクセスする必要があり、パフォーマンスが出ません。なお、りんご%といった前方一致であればINDEXが使えるケースもあります。
このようにデータのどこかに指定したワードがあるかを探すには全文検索機能が必要になります。
対象環境
今回は以下の環境で実施しました。
- Ubuntu 16.04
- PostgreSQL 9.6.5
- pg_bigm 1.2
pg_bigm
pg_bigmはPostgreSQL本体の追加モジュールとして提供されているpg_trgmを日本語向けにしたものです。
いずれもN-Gramをもとにした実装です。pg_bigmが2-gram、pg_trgmが3-gramといえます。英語の場合は2文字の単語というのは少ないため3-gramが都合が良いのですが、日本語は数多くの二文字の単語があるためpg_trgmでは検索にヒットしないワードがでてしまいます。
そこでpg_bigmを使ってみます。
コンパイル
ソースをここから落とします。末尾がtar.gzとなっているものがソースファイルです。入手したら任意のディレクトリに解凍しておきます。
denzow@denzow-ubuntu:~/work/pg/pg_bigm-1.2-20161011$ pwd /home/denzow/work/pg/pg_bigm-1.2-20161011 denzow@denzow-ubuntu:~/work/pg/pg_bigm-1.2-20161011$ ls -l 合計 80 -rw-rw-r-- 1 denzow denzow 1160 10月 11 2016 LICENSE -rw-rw-r-- 1 denzow denzow 535 10月 11 2016 Makefile -rw-rw-r-- 1 denzow denzow 602 10月 11 2016 README.md -rw-rw-r-- 1 denzow denzow 2064 10月 11 2016 bigm.h -rw-rw-r-- 1 denzow denzow 11181 10月 11 2016 bigm_gin.c -rw-rw-r-- 1 denzow denzow 15077 10月 11 2016 bigm_op.c drwxrwxr-x 2 denzow denzow 4096 10月 11 2016 data drwxrwxr-x 2 denzow denzow 4096 10月 11 2016 expected drwxrwxr-x 2 denzow denzow 4096 10月 11 2016 html -rw-rw-r-- 1 denzow denzow 264 10月 11 2016 pg_bigm--1.0--1.1.sql -rw-rw-r-- 1 denzow denzow 2024 10月 11 2016 pg_bigm--1.1--1.2.sql -rw-rw-r-- 1 denzow denzow 4247 10月 11 2016 pg_bigm--1.2.sql -rw-rw-r-- 1 denzow denzow 176 10月 11 2016 pg_bigm.control drwxrwxr-x 2 denzow denzow 4096 10月 11 2016 sql
以前の(結構前)サードパーティモジュールをコンパイルする際は、PostgreSQL本体にバンドルされている拡張と同じようにcontribディレクトリに配置してからビルドする必要があったりしましたが、現在はUSE_PGXS=1を指定し、PGXSビルドフレームワークを使用するよう指定するだけで良いので楽です。
denzow@denzow-ubuntu:~/work/pg/pg_bigm-1.2-20161011$ make USE_PGXS=1
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I./ -I/home/denzow/posgreHome/pg965/include/postgresql/server -I/home/denzow/posgreHome/pg965/include/postgresql/internal -D_GNU_SOURCE -I/usr/include/libxml2 -c -o bigm_op.o bigm_op.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I./ -I/home/denzow/posgreHome/pg965/include/postgresql/server -I/home/denzow/posgreHome/pg965/include/postgresql/internal -D_GNU_SOURCE -I/usr/include/libxml2 -c -o bigm_gin.o bigm_gin.c
In file included from bigm_gin.c:15:0:
bigm.h:48:19: warning: inline function ‘bigmstrcmp’ declared but never defined
extern inline int bigmstrcmp(char *arg1, int len1, char *arg2, int len2);
^
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -shared -o pg_bigm.so bigm_op.o bigm_gin.o -L/home/denzow/posgreHome/pg965/lib -Wl,--as-needed -Wl,-rpath,'/home/denzow/posgreHome/pg965/lib',--enable-new-dtags
denzow@denzow-ubuntu:~/work/pg/pg_bigm-1.2-20161011$ make USE_PGXS=1 install
/bin/mkdir -p '/home/denzow/posgreHome/pg965/lib/postgresql'
/bin/mkdir -p '/home/denzow/posgreHome/pg965/share/postgresql/extension'
/bin/mkdir -p '/home/denzow/posgreHome/pg965/share/postgresql/extension'
/usr/bin/install -c -m 755 pg_bigm.so '/home/denzow/posgreHome/pg965/lib/postgresql/pg_bigm.so'
/usr/bin/install -c -m 644 .//pg_bigm.control '/home/denzow/posgreHome/pg965/share/postgresql/extension/'
/usr/bin/install -c -m 644 .//pg_bigm--1.2.sql .//pg_bigm--1.1--1.2.sql .//pg_bigm--1.0--1.1.sql '/home/denzow/posgreHome/pg965/share/postgresql/extension/'
なんかちょっとwarning出てますが見なかったことにします。
DBへのロード
インストールはできましたが、使うためにはDB側でも作業が必要です。
- postgresql.confの変更
$ vi $PGDATA/postgresql.conf shared_preload_libraries = 'pg_bigm'
pg_bigmライブラリをDB起動時に読み込むように設定し、再起動します。
denzow@denzow-ubuntu:~/work/pg/pg_bigm-1.2-20161011$ pg_ctl restart waiting for server to shut down....LOG: received fast shutdown request LOG: aborting any active transactions LOG: autovacuum launcher shutting down LOG: shutting down LOG: database system is shut down done server stopped server starting
続いて、DB内で必要な関数群等をロードします。
denzow@denzow-ubuntu:~/work/pg/pg_bigm-1.2-20161011$ psql postgres psql (9.6.5) Type "help" for help. postgres=# CREATE EXTENSION pg_bigm; CREATE EXTENSION postgres=# select * from pg_extension ; extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition ---------+----------+--------------+----------------+------------+-----------+-------------- plpgsql | 10 | 11 | f | 1.0 | | pg_bigm | 10 | 2200 | t | 1.2 | | (2 rows)
ちゃんとpg_bigmが表示されていればロード成功です。
試してみる
では使ってみます。とりあえずマニュアルのデータで試します。
CREATE TABLE pg_tools (tool text, description text); INSERT INTO pg_tools VALUES ('pg_hint_plan', 'PostgreSQLでHINT句を使えるようにするツール'); INSERT INTO pg_tools VALUES ('pg_dbms_stats', 'PostgreSQLの統計情報を固定化するツール'); INSERT INTO pg_tools VALUES ('pg_bigm', 'PostgreSQLで2-gramの全文検索を使えるようにするツール'); INSERT INTO pg_tools VALUES ('pg_trgm', 'PostgreSQLで3-gramの全文検索を使えるようにするツール'); CREATE INDEX pg_tools_idx ON pg_tools USING gin (description gin_bigm_ops);
検索してみます。
postgres=# select * from pg_tools where description like '%全文%'; tool | description ---------+------------------------------------------------------ pg_bigm | PostgreSQLで2-gramの全文検索を使えるようにするツール pg_trgm | PostgreSQLで3-gramの全文検索を使えるようにするツール (2 rows)
結果が帰りました。ただ、別にこれはINDEXなくても同じですね。ちゃんと実行計画を見ておきます。実行計画はexplainで見ることができます。更に実行時間等の詳細を取る場合はanalyzeオプションをつけます。(analyzeを指定すると実際にSQLが実行される点は注意します。)
postgres=# explain analyze select * from pg_tools where description like '%全文%';
QUERY PLAN
---------------------------------------------------------------------------------------------------
Seq Scan on pg_tools (cost=0.00..1.05 rows=1 width=64) (actual time=0.011..0.011 rows=2 loops=1)
Filter: (description ~~ '%全文%'::text)
Rows Removed by Filter: 2
Planning time: 0.072 ms
Execution time: 0.026 ms
(5 rows)
Seq Scan on pg_toolsなのでINDEX使ってないですね・・・
postgres=# set enable_seqscan=off;
SET
postgres=# explain analyze select * from pg_tools where description like '%全文%';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pg_tools (cost=8.00..12.01 rows=1 width=73) (actual time=0.016..0.017 rows=2 loops=1)
Recheck Cond: (description ~~ '%全文%'::text)
Heap Blocks: exact=1
-> Bitmap Index Scan on pg_tools_idx (cost=0.00..8.00 rows=1 width=0) (actual time=0.010..0.010 rows=2 loops=1)
Index Cond: (description ~~ '%全文%'::text)
Planning time: 0.082 ms
Execution time: 0.039 ms
(7 rows)
set enable_seqscan=off;でFULLSCANを選びにくくさせたらINDEX出てきましたね。どうももと表が小さすぎるのかも？
postgres=# set enable_seqscan=on;
SET
postgres=# insert into pg_tools select 'dummy'||t, 'dummy description'||t from generate_series(1,1000000) t;
INSERT 0 1000000
postgres=# explain analyze select * from pg_tools where description like '%全文%';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pg_tools (cost=524.86..933.57 rows=111 width=32) (actual time=0.791..0.792 rows=2 loops=1)
Recheck Cond: (description ~~ '%全文%'::text)
Heap Blocks: exact=1
-> Bitmap Index Scan on pg_tools_idx (cost=0.00..524.83 rows=111 width=0) (actual time=0.768..0.768 rows=2 loops=1)
Index Cond: (description ~~ '%全文%'::text)
Planning time: 0.124 ms
Execution time: 0.813 ms
(7 rows)
1000000行かさ増ししたらちゃんと普通にINDEX使ってくれましたね。中間一致でも0.813 msとかなり高速な結果が出ました。一応この状態でFULLSCANさせたらどうなるかも見ておきます。FULLSCANになるようにset enable_bitmapscan=off;でBitmap Scanを選ばれにくくしておきます。
postgres=# set enable_bitmapscan=off;
SET
postgres=# explain analyze select * from pg_tools where description like '%全文%';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
Seq Scan on pg_tools (cost=0.00..21648.05 rows=100 width=34) (actual time=0.027..127.312 rows=2 loops=1)
Filter: (description ~~ '%全文%'::text)
Rows Removed by Filter: 1000002
Planning time: 0.098 ms
Execution time: 127.330 ms
(5 rows)
127.330 msなのでだいぶ違いますね。これで中間一致のパフォーマンスがだいぶ改善できた気がします。
