PostgreSQL日記

アクセスカウンタ

zoom RSS 中間一致検索でインデックスを使う

<<   作成日時 : 2008/03/09 00:21   >>

驚いた ブログ気持玉 6 / トラックバック 3 / コメント 5

今日はPostgreSQLネタです.

「中間一致検索」とは,たとえば「東京都」という文字列を含むものを探す検索を行うこと.
SQLで言えば,

foo LIKE '%東京都%'

のような検索式になる.これに対して,「東京都」で始まるものを探すのが「前方一致検索」で,

foo LIKE '東京都%'

となる.

前方一致検索は,Btreeインデックスを使って高速な検索が容易にできる.これに対して,中間一致検索は,インデックスを使えないのが普通である.インデックスが使えないとテーブルを全部なめるしかないため,非常に低速になるし,I/Oが増えるのでデータベースシステムに負荷がかかり,嫌われる.

PostgreSQL 8.3では全文検索機能が容易に使えるようになった.これを利用して,中間一致検索を高速化してみる.

ここで問題がある.

PostgreSQLの全文検索は基本的に単語単位での検索を行うことが想定されている.たとえば「東」で検索しても,「東京」や「東京都」は検索にかからない.したがって,PostgreSQLの全文検索機能は中間一致検索の代りにはならないのだ.

そこで,全文検索のデータを作成する際に,単語単位で処理するのではなく,文字単位で処理することにする.たとえば,「東京都」は「東 京 都」に分けて認識させるのである.このような処理を関数は次のように定義できる.

create or replace function unigram(text) returns tsvector as $$
select to_tsvector('simple', array_to_string(regexp_split_to_array($1, E'\\s*'),' '));
$$ immutable language sql;

問合わせ文字列を処理する関数は次のようになる.

create or replace function q(text) returns tsquery as $$
select to_tsquery(array_to_string(regexp_split_to_array($1, E'\\s*'),'&'));
$$ language sql;

後は,中間一致検索を行いたいテキストを格納したカラムにGINインデックスを貼れば良い.たとえば,次のようなテーブルがあったとする.

create table t1(t text);

この「t」カラムにGINインデックスを貼るのは次のようになる.

create index t1_index on t1 using gin(unigram(t));

検索はこうだ.

select * from t1 where q(t) @@ '東京都' and t like '%東京都%';

select * from t1 where q('東京都') @@ unigram(t) and t like '%東京都%';

ここで,「@@」は全文検索を行う演算子で,「東」「京」「都」を含む行を検索している.これだけでは,「京都から東に向かいました」という行も検索されてしまうため,and条件以降でノイズを振り落としている.これだけ見ると普通の中間一致検索と同じ式だが,@@を使った検索で行を絞り込んだ後にlikeの処理を行うので,あまり速度は低下しない.

手元の30万件ほどのテーブルで実際に試したところ,通常のlike検索では1.5秒ほどかかるのが,この方式では50ミリ秒と,30倍も早くなった.

テーマ

注目テーマ 一覧


月別リンク

ブログ気持玉

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

トラックバック(3件)

タイトル (本文) ブログ名/日時
VISVIM 通販
中間一致検索でインデックスを使う PostgreSQL日記/ウェブリブログ ...続きを見る
VISVIM 通販
2013/07/08 16:22
エアマックス 95
中間一致検索でインデックスを使う PostgreSQL日記/ウェブリブログ ...続きを見る
エアマックス 95
2013/07/09 00:43
モンクレール ダウン 新作
中間一致検索でインデックスを使う PostgreSQL日記/ウェブリブログ ...続きを見る
モンクレール ダウン 新作
2013/11/27 01:56

トラックバック用URL help


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

タイトル
本 文

コメント(5件)

内 容 ニックネーム/日時
tカラムの文字の中に、( とかあるとエラーとなってしまうようです。私の環境だけでしょうか?
参考になるのですが。
2008/04/17 00:35
tsv型のカラムを作って、上記の例を参考に、日本をすべて分かち書きにする形でうまくいきそうです。
お騒がせしました。
参考になるのですが。
2008/04/17 02:51
すばらしいです!ありがとうございます。なお、文中にあるSQLは

select * from t1 where q(t) @@ q('東京都') and t like '%東京都%';

ですよね?
そら
2011/12/03 23:35
いえ、q()はts_query型を返し、@@演算子はts_query @@ ts_vector(もしくテキスト型を渡すと勝手にキャストされる)でなければならないので、q(t) @@ q('東京都') ではダメです。
石井
2011/12/04 18:08
> tカラムの文字の中に、( とかあるとエラーとなってしまうようです。
済みません。これは、q(t)で発生しています。qが使っているts_query()は、「(」が特殊文字扱いなので、エラーになってしまいます。ところで、そもそも、なぜこんなクエリになっていたのか思い出せないのですが、元のクエリが間違いなので、クエリを書き直しました。
石井
2011/12/04 18:24

コメントする help

ニックネーム
本 文
中間一致検索でインデックスを使う PostgreSQL日記/BIGLOBEウェブリブログ
文字サイズ:       閉じる