PostgreSQLの全文検索モジュールpg_bigmの最新バージョン1.1がリリースされました。
今回は、この最新バージョンを使って、PostgreSQLに格納した様々な日本語データを高速に日本語検索してみます！
(PostgreSQL Advent Calendar 2013 5日目の記事と内容かぶって申し訳ないです。。)
検索対象データ
今回は、以下の日本語データで全文検索を試しました。
- 青空文庫の書籍一覧データ
- 住所データ
- 日本版Wikipediaのタイトル一覧データ
pg_bigmのインストール
ここからpg_bigm 1.1をダウンロードして、インストールします。
$ tar zxf pg_bigm-1.1-20131122.tar.gz
$ cd pg_bigm-1.1-20131122
$ make USE_PGXS=1 PG_CONFIG=$HOME/pgsql-9.3.2/bin/pg_config
$ make USE_PGXS=1 PG_CONFIG=$HOME/pgsql-9.3.2/bin/pg_config install
※上記は、PostgreSQLのインストール先が$HOME/pgsql-9.3.2の前提です。
psqlでPostgreSQLにログインして、pg_bigmをデータベースに登録します。
CREATE EXTENSION pg_bigm;
青空文庫の書籍一覧データの検索
青空文庫の書籍一覧データ(aozora_word_list_utf8.csv.gz)をここからダウンロードします。
$ cd /tmp
$ wget http://aozora-word.hahasoha.net/aozora_word_list_utf8.csv.gz
$ gunzip -d aozora_word_list_utf8.csv.gz
書籍一覧を格納するためのテーブルを作成します。
CREATE TABLE aozora (
"作品id" text,
"作品名" text,
"作品名読み" text,
"ソート用読み" text,
"副題" text,
"副題読み" text,
"原題" text,
"初出" text,
"分類番号" text,
"文字遣い種別" text,
"作品著作権フラグ" text,
"公開日" text,
"最終更新日" text,
"図書カードurl" text,
"人物id" text,
"姓" text,
"名" text,
"姓読み" text,
"名読み" text,
"姓読みソート用" text,
"名読みソート用" text,
"姓ローマ字" text,
"名ローマ字" text,
"役割フラグ" text,
"生年月日" text,
"没年月日" text,
"人物著作権フラグ" text,
"底本名1" text,
"底本出版社名1" text,
"底本初版発行年1" text,
"入力に使用した版1" text,
"校正に使用した版1" text,
"底本の親本名1" text,
"底本の親本出版社名1" text,
"底本の親本初版発行年1" text,
"底本名2" text,
"底本出版社名2" text,
"底本初版発行年2" text,
"入力に使用した版2" text,
"校正に使用した版2" text,
"底本の親本名2" text,
"底本の親本出版社名2" text,
"底本の親本初版発行年2" text,
"入力者" text,
"校正者" text,
"テキストファイルurl" text,
"テキストファイル最終更新日" text,
"テキストファイル符号化方式" text,
"テキストファイル文字集合" text,
"テキストファイル修正回数" text,
"XHTML/HTMLファイルURL" text,
"XHTML/HTMLファイル最終更新日" text,
"XHTML/HTMLファイル符号化方式" text,
"XHTML/HTMLファイル文字集合" text,
"XHTML/HTMLファイル修正回数" text,
"file" text);
書籍一覧をPostgreSQLに取り込んで、作品名に対してpg_bigmの全文検索インデックスを作成します。書籍一覧は、CSV形式で先頭行がヘッダ情報なので、\copyでデータを取り込む際にはcsvとheaderオプションを指定します。
\copy aozora from /tmp/aozora_word_list_utf8.csv with csv header
CREATE INDEX aozora_title ON aozora USING gin ("作品名" gin_bigm_ops);
書籍一覧データには、11,818件のデータが含まれています。
SELECT count(*) FROM aozora ;
count
-------
11818
(1 row)
作品名に「ぞう」が含まれている書籍について作品名、著者の姓と名を検索します。どうやら、そんな書籍が2件あるようです。
SELECT "作品名", "姓", "名" FROM aozora WHERE "作品名" LIKE likequery('ぞう');
作品名 | 姓 | 名
------------------------+------+------
こぞうさんの おきょう | 新美 | 南吉
お月さまと ぞう | 小川 | 未明
(2 rows)
全文検索インデックスを使った検索とシーケンシャルな検索の性能差を確認してみます。
まずは全文検索インデックスを使った場合です。11,818件から2件検索するのに0.074 msかかってます。
EXPLAIN ANALYZE SELECT "作品名", "姓", "名" FROM aozora WHERE "作品名" LIKE likequery('ぞう');
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on aozora (cost=12.01..16.02 rows=1 width=33) (actual time=0.033..0.037 rows=2 loops=1)
Recheck Cond: ("作品名" ~~ '%ぞう%'::text)
-> Bitmap Index Scan on aozora_title (cost=0.00..12.01 rows=1 width=0) (actual time=0.023..0.023 rows=2 loops=1)
Index Cond: ("作品名" ~~ '%ぞう%'::text)
Total runtime: 0.074 ms
(5 rows)
次に、シーケンシャルスキャンで検索した場合で、6.673 msかかってます。
SET enable_bitmapscan TO off;
EXPLAIN ANALYZE SELECT "作品名", "姓", "名" FROM aozora WHERE "作品名" LIKE likequery('ぞう');
QUERY PLAN
----------------------------------------------------------------------------------------------------
Seq Scan on aozora (cost=0.00..1361.72 rows=1 width=33) (actual time=2.287..6.646 rows=2 loops=1)
Filter: ("作品名" ~~ '%ぞう%'::text)
Rows Removed by Filter: 11816
Total runtime: 6.673 ms
(4 rows)
SET enable_bitmapscan TO on;
というわけで、pg_bigmの全文検索インデックスを使うことで、大幅に検索レスポンスが高くなることが確認できました！
住所データの検索
日本全国の住所データ(csv_zenkoku.zip)をここからダウンロードします。
$ cd /tmp
$ wget http://jusyo.jp/downloads/new/csv/csv_zenkoku.zip
$ unzip csv_zenkoku.zip
住所データを格納するためのテーブルを作成します。
CREATE TABLE zenkoku (
"住所CD" text,
"都道府県CD" text,
"市区町村CD" text,
"町域CD" text,
"郵便番号" text,
"事業所フラグ" text,
"廃止フラグ" text,
"都道府県" text,
"都道府県カナ" text,
"市区町村" text,
"市区町村カナ" text,
"町域" text,
"町域カナ" text,
"町域補足" text,
"京都通り名" text,
"字丁目" text,
"字丁目カナ" text,
"補足" text,
"事業所名" text,
"事業所名カナ" text,
"事業所住所" text,
"新住所CD" text);
住所データをPostgreSQLに取り込んで、市区町村と町域に対してpg_bigmの全文検索インデックスを作成します。住所データは、CSV形式で先頭行がヘッダ情報なので、\copyでデータを取り込む際にはcsvとheaderオプションを指定します。また、住所データのエンコーディングはSJISなので、encoding 'sjis'のオプションも指定が必要です。
\copy zenkoku from /tmp/zenkoku.csv with csv header encoding 'sjis'
CREATE INDEX zenkoku_idx ON zenkoku USING gin ("市区町村" gin_bigm_ops, "町域" gin_bigm_ops);
住所データには、147,769件のデータが含まれています。
SELECT count(*) FROM zenkoku ;
count
--------
147769
(1 row)
市区町村に「青」、町域に「赤」が含まれている住所を検索します。どうやら、そんな住所が2件あるようです。
SELECT "都道府県" || "市区町村" || "町域" 住所 from zenkoku where "市区町村" like '%青%' AND "町域" like '%赤%';
住所
------------------------
青森県青森市赤坂
宮城県仙台市青葉区赤坂
(2 rows)
全文検索インデックスを使った検索とシーケンシャルな検索の性能差を確認してみます。
まずは全文検索インデックスを使った場合です。147,769件から2件検索するのに0.863 msかかってます。
EXPLAIN ANALYZE SELECT "都道府県" || "市区町村" || "町域" 住所 from zenkoku where "市区町村" like '%青%' AND "町域" like '%赤%';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on zenkoku (cost=60.01..64.03 rows=1 width=34) (actual time=0.807..0.812 rows=2 loops=1)
Recheck Cond: (("市区町村" ~~ '%青%'::text) AND ("町域" ~~ '%赤%'::text))
-> Bitmap Index Scan on zenkoku_idx (cost=0.00..60.01 rows=1 width=0) (actual time=0.791..0.791 rows=2 loops=1)
Index Cond: (("市区町村" ~~ '%青%'::text) AND ("町域" ~~ '%赤%'::text))
Total runtime: 0.863 ms
(5 rows)
次に、シーケンシャルスキャンで検索した場合で、70.684 msかかってます。
SET enable_bitmapscan TO off;
EXPLAIN ANALYZE SELECT "都道府県" || "市区町村" || "町域" 住所 from zenkoku where "市区町村" like '%青%' AND "町域" like '%赤%';
QUERY PLAN
------------------------------------------------------------------------------------------------------
Seq Scan on zenkoku (cost=0.00..5765.54 rows=1 width=34) (actual time=5.155..70.649 rows=2 loops=1)
Filter: (("市区町村" ~~ '%青%'::text) AND ("町域" ~~ '%赤%'::text))
Rows Removed by Filter: 147767
Total runtime: 70.684 ms
(4 rows)
SET enable_bitmapscan TO on;
というわけで、住所データについても、pg_bigmでの高速な日本語検索が確認できました！
日本版Wikipediaのタイトル一覧データの検索
日本版Wikipediaのタイトル一覧データ(jawiki-latest-all-titles.gz)をここからダウンロードします。
$ cd /tmp
$ wget http://dumps.wikimedia.org/jawiki/latest/jawiki-latest-all-titles.gz
$ gunzip -d jawiki-latest-all-titles.gz
タイトル一覧を格納するためのテーブルを作成します。
CREATE TABLE wikipedia_title (title text);
タイトル一覧をPostgreSQLに取り込めるように、タイトル文字列内のダブルクォート文字をダブルクォート文字でエンコーディングし、またタイトル文字列の先頭と末尾にダブルクォート文字を追加します。
$ sed s/\"/\"\"/g jawiki-latest-all-titles | sed s/^/\"/g | sed s/$/\"/g > jawiki-latest-all-titles.tmp
タイトル一覧をPostgreSQLに取り込んで、titleに対してpg_bigmの全文検索インデックスを作成します。タイトル一覧は、CSV形式で先頭行がヘッダ情報なので、\copyでデータを取り込む際にはcsvとheaderオプションを指定します。
\copy wikipedia_title from /tmp/jawiki-latest-all-titles.tmp csv header
CREATE INDEX wikipedia_title_idx ON wikipedia_title USING gin (title gin_bigm_ops);
タイトル一覧には、2,461,588件のデータが含まれています。
SELECT count(*) FROM wikipedia_title;
count
---------
2461588
(1 row)
titleに「全文検索」が含まれているタイトルを検索します。そんなタイトルが3件あるようで、同じタイトルが2回出てきていますが、どうやら日本語Wikipediaのタイトル一覧には同じタイトルで複数回現れるものがあるようです。
SELECT title FROM wikipedia_title WHERE title LIKE likequery('全文検索');
title
------------------
全文検索
全文検索エンジン
全文検索
(3 rows)
全文検索インデックスを使った検索とシーケンシャルな検索の性能差を確認してみます。
まずは全文検索インデックスを使った場合です。2,461,588件から3件検索するのに0.150 msかかってます。
EXPLAIN ANALYZE SELECT title FROM wikipedia_title WHERE title LIKE likequery('全文検索');
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on wikipedia_title (cost=53.90..942.57 rows=245 width=21) (actual time=0.109..0.114 rows=3 loops=1)
Recheck Cond: (title ~~ '%全文検索%'::text)
-> Bitmap Index Scan on wikipedia_title_idx (cost=0.00..53.84 rows=245 width=0) (actual time=0.097..0.097 rows=3 loops=1)
Index Cond: (title ~~ '%全文検索%'::text)
Total runtime: 0.150 ms
(5 rows)
次に、シーケンシャルスキャンで検索した場合で、943.450 msかかってます。
SET enable_bitmapscan TO off;
EXPLAIN ANALYZE SELECT title FROM wikipedia_title WHERE title LIKE likequery('全文検索');
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Seq Scan on wikipedia_title (cost=0.00..46768.85 rows=245 width=21) (actual time=302.481..943.421 rows=3 loops=1)
Filter: (title ~~ '%全文検索%'::text)
Rows Removed by Filter: 2461585
Total runtime: 943.450 ms
(4 rows)
SET enable_bitmapscan TO on;
というわけで、200万件超のデータについても、pg_bigmでの高速な日本語検索が確認できました！
さいごに
pg_bigmを使うことで、PostgreSQLに格納した文書データを高速に検索できるようになります！簡単に導入できるモジュールなので、ぜひ、いろんなデータでお試しください！