|
LudiaTM で PostgreSQL から全文検索を行う具体的な手順をまとめておきます。 本来、Senna 全文検索エンジンはウェブページとか、メールとか、テキストファイルとか 大きなテキスト群全文を検索するのが目的でしょうけど、データを用意するのが大変なので、 今回はお手頃な 郵便番号辞書 を使ってみます。 実際のところ郵便番号辞書程度のデータ規模なら、全文検索エンジンまでは必要ありません。 あくまで、Ludia を使う手順と EXPLAIN の実行結果メモということで。 用意するもの： ・PostgreSQL 8.1 ・Ludia （今回は 0.8.0 を使いました） ・Senna （今回は 0.8.2 を使いました） ・郵便番号辞書CSVファイル PostgreSQL 8.1・Ludia・Senna をインストールしておきます。 郵便番号辞書は、この手順でＤＢにインポートして下さい。 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 PLANSELECT zip7, pref, city, area FROM tbl_zip WHERE area ILIKE '%川崎%' LIMIT 10; zip7 | pref | city | areaもちろん、問題なく検索できています。 クエリプランの、cost の最大値 5970.51 に注目してください。 １回目は検索処理に少し時間がかかります。 ただし、２回目からはデータがキャッシュメモリに入りきると、速くなってしまいます。 なお、ILIKE なので、英字の大文字小文字の違いは無視されます。 Ludia 利用時の全文検索のクエリプラン次に、@@ 演算子を使って Senna のインデックスを使った検索を行います。 EXPLAIN SELECT * FROM tbl_zip WHERE area @@ '川崎'; QUERY PLANSELECT zip7, pref, city, area FROM tbl_zip WHERE area @@ '川崎' LIMIT 10; zip7 | pref | city | areacost の値がかなり小さいですが、これは全文検索インデックスを優先的に 利用させるための PostgreSQL プランナ向けの決め打ち値なのかもしれません。 検索結果は、ILIKE のときとは順序が異なっています。 基本的に Senna が算出したスコア順で抽出されているはずですが、 こう短すぎるカラムだと、あまりスコアも上下していないかと思います。 複数キーワードのクエリとプラグマ検索クエリとして、スペース区切りで複数のキーワードを並べることもできます。 SELECT zip7, pref, city, area FROM tbl_zip WHERE area @@ '桜 本町' LIMIT 10; zip7 | pref | city | areaデフォルトでは、複数のキーワードを並べると OR 検索になるようです。 複数のキーワードで AND 検索する場合は、Senna のプラグマ を指定する必要があります。 SELECT zip7, pref, city, area FROM tbl_zip WHERE area @@ '*D+ 桜 本町' LIMIT 10; zip7 | pref | city | areaクエリの先頭に「*D+」と付けると、AND 検索になります。 これがデフォルトになっていて欲しいところですが、どこかに設定箇所あるのかな？ 複数カラムを条件にした場合のクエリプランデータベースを利用した全文検索システムの実際の運用では、 複数のカラムにまたがった検索時のクエリが多く発生します。 EXPLAIN SELECT * FROM tbl_zip WHERE city @@ '川崎' OR area @@ '川崎'; QUERY PLANSELECT zip7, pref, city, area FROM tbl_zip WHERE city @@ '川崎' OR area @@ '川崎' LIMIT 10; zip7 | pref | city | area複数カラムの OR 検索では、Senna (Ludia) のインデックスを利用した上で、 Bitmap Heap Scan で高速な OR 検索が実施されるようです。 EXPLAIN SELECT * FROM tbl_zip WHERE city @@ '川崎' AND area @@ '川崎'; QUERY PLANSELECT zip7, pref, city, area FROM tbl_zip WHERE city @@ '川崎' AND area @@ '川崎' LIMIT 10; zip7 | pref | city | area複数カラムの 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 される思う、多分） |
| << 前記事（2006/10/20） | トップへ | 後記事（2006/10/21）>> |
| タイトル （本文） | ブログ名／日時 |
|---|
| 内 容 | ニックネーム／日時 |
|---|
| << 前記事（2006/10/20） | トップへ | 後記事（2006/10/21）>> |
