Ludia / 全文検索の動作確認
インストールが終わったので、sampleデータベースを作成します。
CREATE DATABASE sample ENCODING = 'EUC_JP';
sampleデータベースでsennaが利用できるようにソースファイル内にあるpgsenna2.sqlを実行します。
$ psql -f ./pgsenna2.sql sample
次にサンプルのダミーデータを投入して、動作確認をします。手っ取り早く、日本郵政公社が提供している「郵便番号データ」を使うことにします。ダウンロードは以下からできます。
動作確認用のテーブルを作成し、データを投入します。
CREATE TABLE m_address ( id integer , zip_code_5 char(5) , zip_code char(7) , ken_kana text , city_kana text , machi_kana text , ken text , city text , machi text , machi_flag integer , aza_flag integer , chou_flag integer , two_cho_flag integer , update_flag integer , update_reason integer ); \copy m_address from '/path/to/csv/ken_all.euc.csv' with csv
インデックスを作成します。とりあえず分かち書き方式でインデックスを作成してあります。
CREATE INDEX idx_m_address_city_senna ON m_address USING fulltext(city);
投入したデータ件数をあらかじめ調べておきます。約12万件といった感じです。
SELECT count(*) FROM m_address ; count -------- 121785 (1 row)
では実際のクエリーを発行してみます。まずは単純な部分一致検索を行ないました。
SELECT id, zip_code, ken, city, machi FROM m_address WHERE city @@ '山'; id | zip_code | ken | city | machi -------+----------+----------+------------------+---------------------- 26110 | 6078416 | 京都府 | 京都市山科区 | 御陵進藤町 26110 | 6078419 | 京都府 | 京都市山科区 | 御陵檀ノ後 (中略) 43208 | 8610406 | 熊本県 | 山鹿市 | 菊鹿町下内田 43208 | 8610405 | 熊本県 | 山鹿市 | 菊鹿町下永野 26110 | 6078412 | 京都府 | 京都市山科区 | 御陵四丁野町 (1791 rows)
EXPLAIN ANALYZEで実行内容を見てみると、以下の通り。ばっちりインデックスが使われています。
EXPLAIN ANALYZE SELECT id, zip_code, ken, city, machi FROM m_address WHERE city @@ '山';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_m_address_senna on m_address (cost=0.00..0.01 rows=122 width=48) (actual time=0.021..4.258 rows=1791 loops=1)
Index Cond: (city @@ '山'::text)
Total runtime: 7.590 ms
(3 rows)ちなみに通常のLIKE句での検索の場合だと以下の通り。当たり前のごとくインデックスは使われないのでかなり遅いです。
EXPLAIN ANALYZE SELECT id, zip_code, ken, city, machi FROM m_address WHERE city LIKE '%山%';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Seq Scan on m_address (cost=0.00..4169.31 rows=24357 width=48) (actual time=4.619..143.090 rows=8223 loops=1)
Filter: (city ~~ '%山%'::text)
Total runtime: 151.494 ms
(3 rows)次にAND検索を行なってみました。sennaのクエリーの書式によると「*D+ 」とすればAND検索になります。
SELECT id, zip_code, ken, city, machi FROM m_address WHERE city @@ '*D+ 京都 山'; id | zip_code | ken | city | machi -------+----------+--------+--------------+---------------------- 26110 | 6070000 | 京都府 | 京都市山科区 | 以下に掲載がない場合 26110 | 6078423 | 京都府 | 京都市山科区 | 御陵山ノ谷 (中略) 26110 | 6078083 | 京都府 | 京都市山科区 | 竹鼻木ノ本町 26110 | 6078087 | 京都府 | 京都市山科区 | 竹鼻サイカシ町 26110 | 6078003 | 京都府 | 京都市山科区 | 安朱稲荷山町 (290 rows)
EXPLAIN ANALYZEで実行内容を見てみると、以下の通り。ばっちりインデックスが使われています。AND検索の場合だと、単一キーワードと実行速度は若干速くなっています。(誤差の範囲内かな？)
EXPLAIN ANALYZE SELECT id, zip_code, ken, city, machi FROM m_address WHERE city @@ '*D+ 京都 山';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_m_address_senna on m_address (cost=0.00..0.01 rows=122 width=48) (actual time=0.027..0.694 rows=290 loops=1)
Index Cond: (city @@ '*D+ 京都 山'::text)
Total runtime: 5.724 ms
(3 rows)ここでも通常のLIKE句での検索の場合だと以下の通り。こちらもインデックスを使わない上にINTERSECT句を使っているので遅いですね。
EXPLAIN ANALYZE
SELECT id, zip_code, ken, city, machi FROM m_address WHERE city LIKE '%京都%'
INTERSECT
SELECT id, zip_code, ken, city, machi FROM m_address WHERE city LIKE '%山%';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
SetOp Intersect (cost=10444.72..10824.69 rows=2533 width=48) (actual time=426.147..445.195 rows=504 loops=1)
-> Sort (cost=10444.72..10508.05 rows=25331 width=48) (actual time=408.068..421.484 rows=13219 loops=1)
Sort Key: id, zip_code, ken, city, machi
-> Append (cost=0.00..8591.93 rows=25331 width=48) (actual time=80.701..341.558 rows=13219 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=0.00..4179.05 rows=974 width=48) (actual time=80.698..149.655 rows=4996 loops=1)
-> Seq Scan on m_address (cost=0.00..4169.31 rows=974 width=48) (actual time=80.690..137.502 rows=4996 loops=1)
Filter: (city ~~ '%京都%'::text)
-> Subquery Scan "*SELECT* 2" (cost=0.00..4412.88 rows=24357 width=48) (actual time=4.555..165.963 rows=8223 loops=1)
-> Seq Scan on m_address (cost=0.00..4169.31 rows=24357 width=48) (actual time=4.546..146.403 rows=8223 loops=1)
Filter: (city ~~ '%山%'::text)
Total runtime: 449.568 ms
(11 rows)最後にOR検索も試してみました。sennaのクエリーの書式によると「*DOR 」とすればOR検索になります。
SELECT id, zip_code, ken, city, machi FROM m_address WHERE city @@ '*DOR 京都 港区'; id | zip_code | ken | city | machi -------+----------+--------+----------------+-------------------------------------------------------------------------- 40625 | 8240113 | 福岡県 | 京都郡みやこ町 | 吉岡 13103 | 1066154 | 東京都 | 港区 | 六本木六本木ヒルズ森タワー（５４階） (中略) 26109 | 6011452 | 京都府 | 京都市伏見区 | 小栗栖西ノ峯 26109 | 6011451 | 京都府 | 京都市伏見区 | 小栗栖鉢伏 40625 | 8240115 | 福岡県 | 京都郡みやこ町 | 光冨 (5786 rows)
EXPLAIN ANALYZEで実行内容を見てみると、以下の通り。ばっちりインデックスが使われています。OR検索なので、やはり遅くなります。
EXPLAIN ANALYZE SELECT id, zip_code, ken, city, machi FROM m_address WHERE city @@ '*DOR 京都 港区';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_m_address_senna on m_address (cost=0.00..0.01 rows=122 width=48) (actual time=0.028..13.910 rows=5786 loops=1)
Index Cond: (city @@ '*DOR 京都 港区'::text)
Total runtime: 23.376 ms
(3 rows)LIKE句での検索の場合だと以下の通り。こちらもインデックスを使わない上にUNION句を使っているので遅いですね。
EXPLAIN ANALYZE
SELECT id, zip_code, ken, city, machi FROM m_address WHERE city LIKE '%京都%'
UNION
SELECT id, zip_code, ken, city, machi FROM m_address WHERE city LIKE '%港区%';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Unique (cost=8464.54..8493.76 rows=1948 width=48) (actual time=305.030..326.259 rows=5786 loops=1)
-> Sort (cost=8464.54..8469.41 rows=1948 width=48) (actual time=305.022..310.922 rows=5786 loops=1)
Sort Key: id, zip_code, ken, city, machi
-> Append (cost=0.00..8358.10 rows=1948 width=48) (actual time=77.272..276.977 rows=5786 loops=1)
-> Seq Scan on m_address (cost=0.00..4169.31 rows=974 width=48) (actual time=77.267..133.454 rows=4996 loops=1)
Filter: (city ~~ '%京都%'::text)
-> Seq Scan on m_address (cost=0.00..4169.31 rows=974 width=48) (actual time=39.233..132.089 rows=790 loops=1)
Filter: (city ~~ '%港区%'::text)
Total runtime: 333.575 ms
(9 rows)たしかに速度は劇的に改善していますね。ちなみにLIKE句を使った場合に検索結果のレコード数を確認したところ、sennaクエリーでの検索結果のレコード数と異なっていました。多分、ここら辺の話なのかなと思います。(調べていません)
検索結果の数が数値1よりも小さい場合、完全一致→非わかち書き→部分一致の順に自動的に検索処理方法を切り替えます。完全一致でヒットした文書と比べて非わかち書き一致、部分一致でヒットした文書には数値2分だけ小さいスコアを付与します。数値2を省略した場合は既定値(=2)と解釈されます。
ちなみに上記とは別に大量データ(約440万件)を投入して、検索を行なった場合、LIKE句による部分一致検索に比べて、sennaクエリーを利用した全文検索の方が、劇的に速くなっていました。参考までに数値を載せておきます。
| sennaクエリー(形態素解析) | Total runtime: 108.719 ms | ||
| sennaクエリー(2-gram) | Total runtime: 139.390 ms | ||
| LIKE句 | Total runtime: 10680.594 ms |
あとは以下の点については詳しく調べたほうがよいですね。
- インデックスデータがどの程度のディスク容量を必要とするのか？
- インデックス作成時にかかる時間
ちなみにいくつか制約事項があるのでREADMEファイルには目を通しておいたほうがよいです。
- 複数列インデックスとしては使用できません。
- 一意性インデックスの機能は提供しません。
- VACUUMには完全に対応していません。
無効なTIDのチェックは行われますが、インデックスのサイズは減少しません。
- REINDEXには対応していません。
インデックスの再構築は、
インデックスファイルの削除、インデックスのDROP、インデックスの再作成、
という手順で行ってください。
