Kawanet Blog II

アクセスカウンタ

zoom RSS PostgreSQL/Ludia/Senna の全文検索インデックスとクエリプラン

<<   作成日時 : 2006/10/20 23:04   >>

面白い ブログ気持玉 6 / トラックバック 0 / コメント 0

LudiaTM で PostgreSQL から全文検索を行う具体的な手順をまとめておきます。
本来、Senna 全文検索エンジンはウェブページとか、メールとか、テキストファイルとか
大きなテキスト群全文を検索するのが目的でしょうけど、データを用意するのが大変なので、
今回はお手頃な 郵便番号辞書 を使ってみます。
実際のところ郵便番号辞書程度のデータ規模なら、全文検索エンジンまでは必要ありません。
あくまで、Ludia を使う手順と EXPLAIN の実行結果メモということで。
 
用意するもの:
   ・PostgreSQL 8.1
   ・Ludia (今回は 0.8.0 を使いました)
   ・Senna (今回は 0.8.2 を使いました)
   ・郵便番号辞書CSVファイル
 
PostgreSQL 8.1・Ludia・Senna をインストールしておきます。
郵便番号辞書は、この手順でDBにインポートして下さい。

Ludia の初期化と、全文検索インデックスの作成


Ludia を使用するには、データベースごとに pgsenna2.sql を実行する必要があります。
pgsenna2.sql は、必ず postgres ユーザ権限で実行します。
template1 や postgres データベースには、まだ適用しない方がいいでしょう。

\i /path/to/share/pgsenna2.sql;

さっそく、全文検索用の転置インデックスを作成します。
Ludia (Senna) の N-gram (bigram) インデックスは、fulltextb メソッドを指定します。

CREATE INDEX senna_zip_pref ON tbl_zip USING fulltextb ( pref );
CREATE INDEX senna_zip_city ON tbl_zip USING fulltextb ( city );
CREATE INDEX senna_zip_area ON tbl_zip USING fulltextb ( area );


郵便番号は12万レコードありますが、それぞれ10秒程度でインデックスが作成できます。
快速! こんなに速い全文検索エンジンのインデクサはこれまであったでしょうか?

なお、Ludia はマルチカラムインデックス(複数列インデックス)に対応していないので、
今のところ、都道府県名・市名・町域名のように複数のカラムを検索したい場合は、
上記のように各カラムごとにインデックスを作成する必要があります。

まずは試しに、全文検索インデックスを使わずに ILIKE 演算子で検索してみます。

EXPLAIN SELECT * FROM tbl_zip WHERE area ILIKE '%川崎%';
                         QUERY PLAN
-------------------------------------------------------------
 Seq Scan on tbl_zip  (cost=0.00..5970.51 rows=39 width=200)
   Filter: (area ~~* '%川崎%'::text)
SELECT zip7, pref, city, area FROM tbl_zip WHERE area ILIKE '%川崎%' LIMIT 10;
  zip7   |  pref  |       city       |     area
---------+--------+------------------+--------------
 2050021 | 東京都 | 羽村市           | 川崎
 0481623 | 北海道 | 虻田郡真狩村     | 川崎
 0360223 | 青森県 | 平川市           | 西野曽江川崎
 0360163 | 青森県 | 平川市           | 苗生松川崎
 0360153 | 青森県 | 平川市           | 小杉川崎
 0290202 | 岩手県 | 一関市           | 川崎町薄衣
 0290201 | 岩手県 | 一関市           | 川崎町門崎
 0284135 | 岩手県 | 盛岡市           | 玉山区川崎
 0280051 | 岩手県 | 久慈市           | 川崎町
 0181745 | 秋田県 | 南秋田郡五城目町 | 川崎
もちろん、問題なく検索できています。
クエリプランの、cost の最大値 5970.51 に注目してください。
1回目は検索処理に少し時間がかかります。
ただし、2回目からはデータがキャッシュメモリに入りきると、速くなってしまいます。
なお、ILIKE なので、英字の大文字小文字の違いは無視されます。

Ludia 利用時の全文検索のクエリプラン


次に、@@ 演算子を使って Senna のインデックスを使った検索を行います。

EXPLAIN SELECT * FROM tbl_zip WHERE area @@ '川崎';
                                    QUERY PLAN
----------------------------------------------------------------------------------
 Index Scan using senna_zip_area on tbl_zip  (cost=0.00..0.01 rows=121 width=200)
   Index Cond: (area @@ '川崎'::text)
SELECT zip7, pref, city, area FROM tbl_zip WHERE area @@ '川崎' LIMIT 10;
  zip7   |  pref  |      city      |     area
---------+--------+----------------+--------------
 2050021 | 東京都 | 羽村市         | 川崎
 8720731 | 大分県 | 宇佐市         | 安心院町川崎
 3480039 | 埼玉県 | 羽生市         | 川崎
 3400814 | 埼玉県 | 八潮市         | 南川崎
 3430006 | 埼玉県 | 越谷市         | 北川崎
 3400204 | 埼玉県 | 北葛飾郡鷲宮町 | 上川崎
 3480035 | 埼玉県 | 羽生市         | 上川崎
 3400162 | 埼玉県 | 幸手市         | 下川崎
 3400163 | 埼玉県 | 幸手市         | 中川崎
 3480034 | 埼玉県 | 羽生市         | 下川崎
cost の値がかなり小さいですが、これは全文検索インデックスを優先的に
利用させるための PostgreSQL プランナ向けの決め打ち値なのかもしれません。

検索結果は、ILIKE のときとは順序が異なっています。
基本的に Senna が算出したスコア順で抽出されているはずですが、
こう短すぎるカラムだと、あまりスコアも上下していないかと思います。

複数キーワードのクエリとプラグマ


検索クエリとして、スペース区切りで複数のキーワードを並べることもできます。

SELECT zip7, pref, city, area FROM tbl_zip WHERE area @@ '桜 本町' LIMIT 10;
  zip7   |  pref  |  city  |    area
---------+--------+--------+------------
 1970022 | 東京都 | 福生市 | 本町
 9560864 | 新潟県 | 新潟市 | 新津本町
 7700063 | 徳島県 | 徳島市 | 不動本町
 7700802 | 徳島県 | 徳島市 | 吉野本町
 9500164 | 新潟県 | 新潟市 | 亀田本町
 5094232 | 岐阜県 | 飛騨市 | 古川町本町
 4140021 | 静岡県 | 伊東市 | 松原本町
 7230062 | 広島県 | 三原市 | 本町
 3270013 | 栃木県 | 佐野市 | 本町
 7700042 | 徳島県 | 徳島市 | 蔵本町
デフォルトでは、複数のキーワードを並べると OR 検索になるようです。
複数のキーワードで AND 検索する場合は、Senna のプラグマ を指定する必要があります。

SELECT zip7, pref, city, area FROM tbl_zip WHERE area @@ '*D+ 桜 本町' LIMIT 10;
  zip7   |   pref   |     city     |   area
---------+----------+--------------+----------
 5013232 | 岐阜県   | 関市         | 桜本町
 4850043 | 愛知県   | 小牧市       | 桜井本町
 5121213 | 三重県   | 四日市市     | 桜台本町
 7380005 | 広島県   | 廿日市市     | 桜尾本町
 8980064 | 鹿児島県 | 枕崎市       | 桜山本町
 4570038 | 愛知県   | 名古屋市南区 | 桜本町
クエリの先頭に「*D+」と付けると、AND 検索になります。
これがデフォルトになっていて欲しいところですが、どこかに設定箇所あるのかな?

複数カラムを条件にした場合のクエリプラン


データベースを利用した全文検索システムの実際の運用では、
複数のカラムにまたがった検索時のクエリが多く発生します。

EXPLAIN SELECT * FROM tbl_zip WHERE city @@ '川崎' OR area @@ '川崎';
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Bitmap Heap Scan on tbl_zip  (cost=0.00..4.01 rows=242 width=200)
   Recheck Cond: ((city @@ '川崎'::text) OR (area @@ '川崎'::text))
   ->  BitmapOr  (cost=0.00..0.00 rows=1 width=0)
         ->  Bitmap Index Scan on senna_zip_city  (cost=0.00..0.00 rows=1 width=0)
               Index Cond: (city @@ '川崎'::text)
         ->  Bitmap Index Scan on senna_zip_area  (cost=0.00..0.00 rows=1 width=0)
               Index Cond: (area @@ '川崎'::text)
SELECT zip7, pref, city, area FROM tbl_zip WHERE city @@ '川崎' OR area @@ '川崎' LIMIT 10;
  zip7   |   pref   |     city     |   area
---------+----------+--------------+----------
 2160003 | 神奈川県 | 川崎市宮前区 | 有馬
 2160004 | 神奈川県 | 川崎市宮前区 | 鷺沼
 2160005 | 神奈川県 | 川崎市宮前区 | 土橋
 2160006 | 神奈川県 | 川崎市宮前区 | 宮前平
 2160007 | 神奈川県 | 川崎市宮前区 | 小台
 2160011 | 神奈川県 | 川崎市宮前区 | 犬蔵
 2160012 | 神奈川県 | 川崎市宮前区 | 水沢
 2160013 | 神奈川県 | 川崎市宮前区 | 潮見台
 2160014 | 神奈川県 | 川崎市宮前区 | 菅生ケ丘
 2160015 | 神奈川県 | 川崎市宮前区 | 菅生
複数カラムの OR 検索では、Senna (Ludia) のインデックスを利用した上で、
Bitmap Heap Scan で高速な OR 検索が実施されるようです。

EXPLAIN SELECT * FROM tbl_zip WHERE city @@ '川崎' AND area @@ '川崎';
                                   QUERY PLAN
--------------------------------------------------------------------------------
 Index Scan using senna_zip_area on tbl_zip  (cost=0.00..0.01 rows=1 width=200)
   Index Cond: (area @@ '川崎'::text)
   Filter: (city @@ '川崎'::text)
SELECT zip7, pref, city, area FROM tbl_zip WHERE city @@ '川崎' AND area @@ '川崎' LIMIT 10;
  zip7   |  pref  |     city     | area
---------+--------+--------------+------
 8270003 | 福岡県 | 田川郡川崎町 | 川崎
複数カラムの AND 検索では、片側だけ Senna (Ludia) のインデックスを利用して、
あとはフィルタとして絞込みを行うようです。
なお、@@ 演算子はインデックスを利用しない場合も、期待通りに動作します。

インデックスの削除手順


Ludia (Senna) のインデックスを削除する手順は、以下の通りです。
DROP INDEX は通常手順と同じですが、最後に pgs2destroy() 関数を呼び出します。

DROP INDEX senna_zip_pref;
DROP INDEX senna_zip_city;
DROP INDEX senna_zip_area;
SELECT pgs2destroy();


この関数が、インデックスが削除されて不要になった Senna の転置インデックスファイル等を
一括削除してくれます。(DROP INDEX ってトリガが効かないのかな?)

マルチカラムインデックスもどき(期待)


ところで、Ludia がマルチカラムインデックスに対応していないのは痛いのですが、
PostgreSQL のインデックスアクセスメソッド+演算子として実装している以上、難しそう。

代替案として、GIN みたいに配列を使って検索できると便利なんですが。
配列を対象にしたインデックスなら、Ludia の拡張で実装できないのかな?

ALTER TABLE tbl_zip ADD COLUMN addrarray text[];
UPDATE tbl_zip SET addrarray = ARRAY[pref,city,area];
VACUUM FULL VERBOSE tbl_zip;
CREATE INDEX senna_zip_addrjoin ON tbl_zip USING fulltextb ( addrarray );
SELECT zip7, pref, city, area FROM tbl_zip WHERE addrarray @@ '川崎' LIMIT 10;

↑のSQL文は想像です。実際には動かないので注意!

できれば、pref→city→area とマッチした順に返してくれるのが理想です。
SQL では本来順序は保障されないが、Ludia のインデックス利用時の LIMIT 付なら
Ludia が返却した順序で結果が取り出せると思う。
これなら、マルチカラムインデックスのカラムごとの優先度を付けられます。

なお、Ludia の全文検索用インデックスを張った状態で全件 UPDATE をかけると
インデックスファイル中に更新前のデータがゴミが残ってしまうと思われるので、
いったん DROP INDEX してから全件 UPDATE をかけた方が効率が良さそうです。
(btree インデックスなどは、VACUUM FULL で REINDEX される思う、多分)

テーマ

関連テーマ 一覧


月別リンク

ブログ気持玉

クリックして気持ちを伝えよう!
ログインしてクリックすれば、自分のブログへのリンクが付きます。
→ログインへ
気持玉数 : 6
面白い 面白い 面白い
なるほど(納得、参考になった、ヘー)
ナイス
かわいい

トラックバック(0件)

タイトル (本文) ブログ名/日時

トラックバック用URL help


自分のブログにトラックバック記事作成(会員用) help

タイトル
本 文

コメント(0件)

内 容 ニックネーム/日時

コメントする help

ニックネーム
本 文
PostgreSQL/Ludia/Senna の全文検索インデックスとクエリプラン Kawanet Blog II/BIGLOBEウェブリブログ
文字サイズ:       閉じる