Skip to content

Instantly share code, notes, and snippets.

@asufana

asufana/Postgresql+Criteriaでの全文検索処理.markdown

Last active December 14, 2015 02:58
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save asufana/9244537 to your computer and use it in GitHub Desktop.
Save asufana/9244537 to your computer and use it in GitHub Desktop.
Download ZIP
全文検索処理について
Raw
Postgresql+Criteriaでの全文検索処理.markdown

全文検索処理について

PostgreSQL+Criteriaでの全文検索処理について

＊もくじ

  • 全文検索インデックスの作成
  • 検索クエリの生成

環境

  • DB：PostgreSQL9.1
  • AP：Hibernateを使ったJavaAP

全文検索インデックスの作成

とりあえずインデックスがないと始まらない。

  • 形態素解析インデックス
  • N-gramインデックス
  • 組み合わせ

全文検索インデックスの作成

形態素解析インデックス

Namazuとか

  • 文章を意味のある単語に区切る
  • 裏庭 / には / 鶏 / が / いる

全文検索インデックスの作成

N-gramインデックス

HyperEstraierとか

  • N文字ずつ分割して索引化する
  • ユニグラム(1-gram/uni-gram)
  • 裏 / 庭 / に / は / 鶏 / が / い / る
  • バイグラム(2-gram/bi-gram)
  • 裏庭 / 庭に / には / は鶏 / 鶏が / がい / いる
  • トライグラム(3-gram/tri-gram)：
  • 省略

＊それぞれのインデックスの特徴は各自調べるように

全文検索エンジン

だれが検索処理をするか？

  • DBMS自体（あるいはアドオン的に）に全文検索処理機能を使う

  • DBの中身が検索対象ならこれでいいよね

  • 専用の全文検索エンジンを別途用意する

  • Web検索とかならクローラ付きだったりするとワンストップで出来て便利

PostgreSQLの全文検索エンジン

利用可能な全文検索インデックス

PostgreSQL 9.xにおける日本語全文検索について調べてみた

uniqram

導入が関数用意するだけというお手軽度ナンバー1。 再コンパイルも必要なければレプリケーションとかも気にしなくても良いのは素敵だわー。 つうことで、今回の調査対象入り。

検索クエリは特殊

SELECT * FROM mail WHERE to_tsquery_ungm('ほげ') @@ to_tsvector_ungm(title);

PostgreSQLの全文検索エンジン

利用可能な全文検索インデックス

pg_bigm

pg_trgmの弱点である3文字以下でも早いというのがこれ。 今回の大本命！でめちゃくちゃ期待してる！ もちろん調査対象

検索クエリは変わりなし

SELECT * FROM mail WHERE title LIKE '%ほげ%';

＊NTTデータが最近リリースしたもの

PostgreSQLの全文検索エンジン

利用可能な全文検索インデックス

pg_trgm

こいつの存在は前から知っていて、個人的にはコレで良いんじゃないか？って思っていたんだけども、「2文字以下じゃインデックス効かない」というのが非常にネック。 日本語は2文字以下の単語も結構あるからねえ。 個人的にはそんぐらい良いだろって思ってるんだけど、まあ、一抹の不安はあるよねってことで、今回調査対象。

検索クエリは変わりなし

SELECT * FROM mail WHERE title LIKE '%ほげ%';

＊社内で利用しているのがこれ

＊PostgreSQL標準品

検索速度と機能のまとめ

中間一致検索でインデックスを使う PostgreSQL日記/ウェブリブログ

  • pg_bigmがトータルとしてよさげ
  • pg_bigm と pg_trgm の共存が可能らしいのでこれが最強か？
  • インデックス作成に時間がかかるだろうけど

全文検索インデックス作成例

メール問合せ履歴をインデックス化

  • 21万件のメール本文とタイトルを全文検索
  • インデックス作成に3分弱
  • CPUコストがかかるので日次で作成
  • 更新トリガーでインデックス再作成とは言わないまでも、もうちょっと頻度を上げないとほんとはダメだね
create index idx_body_tmp on mail using gin (body gin_trgm_ops);　-- 169.59 sec
drop index idx_body;　-- 0.1 sec
alter index idx_body_tmp rename to idx_body;　-- 0.0 sec

＊drop index に CONCURRENTLY オプションを付加した方がよいかどうか検討

＊GiST/GINの選択

検索クエリの作り方

検索クエリの機能

せっかくなので除外キーワード指定ぐらいできるようにしたい

絞り込み検索

  • スペースで区切って検索ワードを指定
  • 開発 java hibernate

フレーズ検索

  • フレーズを [ " ] ダブルクォーテーションで括って指定
  • "play framework"

除外検索

  • [ - ] 半角ハイフンをワードまたは、フレーズの先頭に付加
  • フレームワーク -javaee

検索クエリの生成

Hibernate Criteriaで絞り込みフィルタと除外フィルタを用意する

絞り込みフィルタ

criteria.add(Restrictions.or(Restrictions.ilike("title",
    searchWord,
    MatchMode.ANYWHERE)));

検索クエリの生成

Criteriaで絞り込みフィルタと除外フィルタを用意する

除外フィルタ

//検索フィルタをクローンしてサブクエリに利用する
final DetachedCriteria notInCriteria = (DetachedCriteria) Copy.clone(criteria);
notInCriteria.setProjection(Projections.property("id"));
notInCriteria.add(Restrictions.or(Restrictions.ilike("title",
    serachWord,
    MatchMode.ANYWHERE)));
        
//検索条件をサブクエリ化し、その結果をnot inして除外する
//  たとえば、赤 青 -緑 と検索した場合、以下の様なクエリを生成する
//  select * from 信号 where 色='赤' and 色='青'
//    and 色 not in (select 色 from 信号 where 色='赤' and 色='青' and 色='緑')
criteria.add(Subqueries.propertyNotIn("id", notInCriteria));

＊他にもっといい方法あるかも。調べてない。。

検索クエリの生成

検索文字列を、絞り込みワードと除外ワードに分割して、先のフィルタに流し込む

//検索文字列解析
final List<String> words = new ArrayList<String>();
final List<String> excludeWords = new ArrayList<String>();
final Pattern pattern = Pattern.compile("(?:\\-?\".+?\"(?:$|[ 　])|[^ 　]+)");
final Matcher matcher = pattern.matcher(condition);
while (matcher.find()) {
    final String matchWord = matcher.group().trim();
    if (isEmpty(matchWord) == false) {
        //除外ワード
        if (matchWord.startsWith("-")) {
            excludeWords.add(matchWord.replaceAll("^-\"|^-|\"$", ""));
        }
        //絞込みワード
        else {
            words.add(matchWord.replaceAll("^\"|\"$", ""));
        }
    }
}

検索クエリの生成

＊デモ

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment