Hatena::Grouppostgresql

PostgreSQL 雑記 このページをアンテナに追加 RSSフィード

2011-02-28N-gram全文検索 in PG9.1 このエントリーを含むブックマーク このエントリーのブックマークコメント

追記: KEEPONLYALNUM がデフォルト ON で出荷されていることが判明。このままではアルファベットと数字しかインデックスしないので、特に日本語圏のユーザは、必ず KEEPONLYALNUM を OFF にして、pg_trgm をリビルドする必要があります! (trgm.h の #define をコメントアウト)

PostgreSQL 9.1 では contrib/pg_trgm が拡張され、N-gram検索がサポートされます。btree インデックスとは異なり、LIKE 中間一致検索でもインデックスが使えるのが特徴です。日本語等のマルチバイト文字にも対応しており、LIKE と正確に同じ結果を得ることができます。

上記の特徴は、拙作 textsearch_senna と機能的には同じです。ただ、基礎となるインデックスに、gist または gin を利用するため、レプリケーションにも対応しています。速度的には全文検索専用にチューニングした senna のほうが高速なのでしょうが、外部ファイルを使うため 9.0以降のストリーミング・レプリケーションには対応できませんでした (pgpool, Slony ならば可)。pg_trgm ならば、レプリケーション + N-gram全文検索という構成を取れます。

pg_trgm の使い方

特別扱いする文字などは無いようで、日本語やスペースを跨る語の検索も問題ありません。

=# CREATE EXTENSION pg_trgm;
=# CREATE TABLE tbl (t text);
=# INSERT INTO tbl (t) VALUES ('ABC DE'), ('あいう えお');
=# CREATE INDEX idx ON tbl USING gin (t gin_trgm_ops);
=# SET enable_seqscan = off;
=# EXPLAIN (costs off) SELECT * FROM tbl WHERE t LIKE '%う え%';
                 QUERY PLAN
--------------------------------------------
 Bitmap Heap Scan on tbl
   Recheck Cond: (t ~~ '%う え%'::text)
   ->  Bitmap Index Scan on idx
         Index Cond: (t ~~ '%う え%'::text)
(4 rows)
=# SELECT * FROM tbl WHERE t LIKE '%う え%';
      t
-------------
 あいう えお
(1 row)

日本語は単語がスペース区切りで無いので、PG8.3以降の標準全文検索は textsearch_ja を組み合わせたとしても使いづらいケースがありました。pg_trgm はその痒かったところに手が届く、意外と魅力的な隠し玉かもしれません。

脱線: gin インデックスは必ずデータロードの後で作成すべし

pg_trgm とは直接の関係はありませんが、gin インデックスって、テーブルが空の状態で作成すると、初回の VACUUM をするまでは使われないんですね。まぁ、性能面を考えても初回ロードの後で作成するのがベストなのですが。

=# CREATE TABLE → CREATE INDEX → INSERT
=# SET enable_seqscan = off;
=# EXPLAIN (costs off) SELECT * FROM tbl WHERE t LIKE '%う え%';
 Seq Scan on tbl
=# VACUUM tbl;
=# EXPLAIN (costs off) SELECT * FROM tbl WHERE t LIKE '%う え%';
 Bitmap Heap Scan on tbl