PostgreSQL | SQLite＋MySQL経験者向け、30日で習得するPostgreSQL：高度SQL － Day14 全文検索

SQL PostgreSQL
スポンサーリンク

Day14 前半のゴール

「“LIKE検索じゃ足りない世界”をイメージできるようにする」

今日は PostgreSQL の「全文検索（FULL TEXT SEARCH）」です。
これは、WHERE title LIKE '%foo%' みたいな“部分一致検索”の一段上――「文章としての意味を少し理解した検索」を、DBだけでやる仕組みです。

前半のゴールはこうです。
LIKE検索と全文検索の違いを、具体的なイメージで説明できる。
全文検索の基本要素（tsvector と tsquery）の役割を理解する。
「まずは英語テキストで全文検索を試す」レベルのSQLを書ける。

なぜ LIKE 検索では足りなくなるのか

「単語の境界・複数キーワード・ノイズ除去がつらい」

まずは「そもそも、なぜ全文検索が必要なのか」をはっきりさせます。

例えば、ブログ記事テーブルを考えます。

CREATE TABLE articles (
  id      SERIAL PRIMARY KEY,
  title   TEXT NOT NULL,
  body    TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
SQL

「タイトルや本文から ‘postgres’ を含む記事を探したい」とき、最初に思いつくのはこれです。

SELECT *
FROM articles
WHERE title ILIKE '%postgres%'
   OR body  ILIKE '%postgres%';
SQL

小規模ならこれでも動きますが、すぐに限界が見えてきます。

単語の境界を考慮してくれない（'postgreSQL' や 'Postgres,' などの揺れ）。
複数キーワードの AND / OR を自分で組み立てるのがつらい。
a, the, is みたいなノイズ単語も全部検索対象になってしまう。
インデックスが効きにくく、データ量が増えると一気に遅くなる。

「検索っぽいこと」を真面目にやろうとすると、LIKE だけではしんどい――
そこで出てくるのが、PostgreSQL の全文検索機能です。

FULL TEXT SEARCH のざっくり構造

「文章を“検索用の形”に変える側と、“検索クエリ”側がある」

PostgreSQL の全文検索は、ざっくり言うとこういう構造になっています。

文章（title, body など）を「検索用の形」に変換したもの
→ tsvector 型

検索したいキーワードを「検索用の形」に変換したもの
→ tsquery 型

そして、

tsvector と tsquery を @@ 演算子でマッチングする

という流れです。

イメージとしては、

文章 → 「単語を分解して、正規化して、検索しやすい形にしたもの」
検索語 → 「検索用に整えたキーワード」

を作っておいて、「この文章はこの検索語にマッチするか？」を判定する、という感じです。

tsvector の基本

「文章を“単語の集合”に変換したもの」

tsvector は、「全文検索用に前処理されたテキスト」です。
実際に見てみるのが早いです。

SELECT to_tsvector('english', 'PostgreSQL is a powerful, open source object-relational database system.');
SQL

結果はだいたいこんな感じになります（実際の順序や細部は多少違うことがあります）。

'databas':7 'object-rel':6 'open':5 'power':4 'postgresql':1 'system':8

ここで起きていることを分解すると、

文章を単語に分割している。
is, a などのストップワード（意味の薄い単語）を捨てている。
powerful → power のように、語幹に正規化している。

つまり、「検索に役立つ単語だけを、検索しやすい形で持っている」のが tsvector です。

この tsvector を、記事テーブルのカラムとして持っておくと、
全文検索が一気に速く・賢くなります。

tsquery の基本

「検索キーワードを“検索用の形”にしたもの」

tsquery は、「検索したいキーワード側」を表現する型です。
to_tsquery 関数で作ります。

SELECT to_tsquery('english', 'postgres & database');
SQL

結果はこんな感じになります。

'postgres' & 'databas'

& は「AND」を意味します。
postgres & database → 「postgres という単語と database という単語の両方を含む文章」という検索条件です。

| を使えば OR も書けます。

SELECT to_tsquery('english', 'postgres | mysql');
SQL

結果：

'postgres' | 'mysql'

to_tsquery も、内部で「語幹化」や「ストップワード除去」をしてくれます。
つまり、「検索語の側も、文章と同じルールで正規化される」わけです。

tsvector と tsquery を組み合わせる

「@@ で“この文章はこの検索条件にマッチするか”を判定する」

全文検索のコアは、@@ 演算子です。

SELECT
  to_tsvector('english', 'PostgreSQL is a powerful database') @@
  to_tsquery('english', 'postgres & database') AS matched;
SQL

この結果は true になります。
理由はシンプルで、

左側の tsvector に 'postgresql' と 'databas' が含まれている。
右側の tsquery は 'postgres' & 'databas'（両方含む条件）。

だから、「この文章はこの検索条件にマッチする」と判定されます。

逆に、to_tsquery('english', 'mysql & database') にすると、
'mysql' が含まれていないので false になります。

この「tsvector @@ tsquery」が、全文検索の最小単位です。
あとはこれを、実際のテーブルに適用していくだけです。

記事テーブルに全文検索をかけてみる（最小例）

「まずは計算カラムで試してみる」

いきなりカラムを増やさなくても、まずは「計算した tsvector で検索する」形で試せます。

SELECT
  id,
  title,
  body
FROM articles
WHERE to_tsvector('english', title || ' ' || body)
      @@ to_tsquery('english', 'postgres & database');
SQL

ここでやっていることは、

title || ' ' || body で、タイトルと本文を1つのテキストに連結する。
to_tsvector('english', ...) で、そのテキストを全文検索用に変換する。
to_tsquery('english', 'postgres & database') で、「postgres AND database」という検索条件を作る。
@@ で「この記事は条件にマッチするか？」を判定する。

LIKE検索との違いは、

PostgreSQL でも postgres でもマッチする（語幹化）。
is, a などのノイズ単語は無視される。
複数キーワードの AND / OR を、& / | で素直に書ける。

というところです。

まだこの段階ではインデックスを使っていないので、
データ量が多いと遅くなりますが、「動きのイメージ」をつかむには十分です。

言語指定の意味

「英語なら english、日本語なら別の仕組みが必要になる」

ここまでの例では、すべて 'english' を指定しています。

to_tsvector('english', ...)
to_tsquery('english', ...)
SQL

この 'english' は、「テキストの言語」を表しています。
言語によって、

どの単語をストップワードとして捨てるか。
どう語幹化するか。

が変わるので、PostgreSQLは「辞書（dictionary）」を言語ごとに持っています。

英語なら 'english' でOKですが、日本語は形態素解析が必要になるので、
標準機能だけだとかなり弱いです（Day14 ではまず英語で概念を押さえるのが目的）。

実務で日本語全文検索をちゃんとやるなら、

外部拡張（pg_trgm や日本語用辞書）
Elasticsearch などの専用検索エンジン

と組み合わせることが多いです。

Day14 前半では、「まずは英語テキストで全文検索の“型”を理解する」と割り切ってOKです。

FULL TEXT SEARCH を使うときの“最初の一歩の感覚”

「LIKE の代わりに to_tsvector(...) @@ to_tsquery(...) を思い出す」

ここまでをまとめると、全文検索の最小セットはこうなります。

文章側：to_tsvector('english', title || ' ' || body)
検索語側：to_tsquery('english', 'postgres & database')
マッチング：tsvector @@ tsquery

「タイトルや本文を LIKE で頑張っていたところ」を見つけたら、
「ここ、全文検索にできないかな？」と一度立ち止まってみる価値があります。

Day14 前半では、「tsvector / tsquery / @@」という3つのキーワードと、
「文章を“検索用の形”に変えてから検索する」という発想を、自分の中にインストールするところまで行ければ十分です。

Day14 前半のまとめ

全文検索は、WHERE title LIKE '%foo%' のような部分一致ではなく、「文章を to_tsvector で“検索用の単語集合（tsvector）”に変換し、検索語を to_tsquery で“検索条件（tsquery）”に変換して、tsvector @@ tsquery でマッチングする」仕組みで、ストップワード除去や語幹化、複数キーワードの AND / OR を自然に扱える。
to_tsvector('english', 'PostgreSQL is a powerful database') が 'postgresql' 'databas' 'power' ... のような形になり、to_tsquery('english', 'postgres & database') が 'postgres' & 'databas' になることで、「PostgreSQL でも postgres でもマッチする」「postgres AND database の両方を含む記事だけを取る」といった“検索らしい検索”を、SQLだけで書けるようになる。
まずは SELECT ... FROM articles WHERE to_tsvector('english', title || ' ' || body) @@ to_tsquery('english', 'postgres & database'); のような最小例で、「LIKE の代わりに全文検索の型を使う」感覚をつかむ――これが Day14 前半の着地点になる。

次へ
12
スポンサーリンク
タイトルとURLをコピーしました