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

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

PostgreSQLで全文検索(pg_bigmを試してみた)

今回はPostgreSQLでpg_bigmを試してみます。

SQL全文検索を考えたとき、とりあえずデータ量が多くなければLIKE検索でできます。

select data from small_table where data like '%りんご%';

%りんご%の指定により、文中のどこかでりんごが含まれるデータを検索することができます。%正規表現でいうところの*+に相当し、0文字以上の何かに相当します。

しかし、この方法ではほとんどのRDBMSではINDEXが使えないためTABLEすべてにアクセスする必要があり、パフォーマンスが出ません。なお、りんご%といった前方一致であればINDEXが使えるケースもあります。

このようにデータのどこかに指定したワードがあるかを探すには全文検索機能が必要になります。

対象環境

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

pg_bigm

pg_bigmPostgreSQL本体の追加モジュールとして提供されている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側でも作業が必要です。

$ 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なのでだいぶ違いますね。これで中間一致のパフォーマンスがだいぶ改善できた気がします。