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

Day14 後半のゴール

「“実用レベルの全文検索SQL”を組めるイメージを持つ」

前半で、tsvector・tsquery・@@ の「型」は掴めました。
後半では、それを「実務でちゃんと使える形」に仕上げていきます。

ポイントはこの3つです。
検索用カラム（tsvector）をテーブルに持つ。
インデックスを張って“検索エンジンっぽい速さ”にする。
検索結果にスコアを付けて「それっぽく並べる」。

ここまで来ると、「PostgreSQLだけでそこそこちゃんとした検索」ができるようになります。

検索用カラムをテーブルに持つ

「毎回 to_tsvector しないで済むようにする」

前半の例では、毎回クエリの中で to_tsvector('english', title || ' ' || body) を計算していました。
これは分かりやすい反面、データが増えると重くなります。

そこで、「検索用の tsvector カラム」をテーブルに持ってしまう、というパターンがよく使われます。

ALTER TABLE articles
ADD COLUMN search_vector tsvector;

ALTER TABLE articles
ADD COLUMN search_vector tsvector;

そして、一度全件を初期化します。

UPDATE articles
SET search_vector =
  to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''));

UPDATE articles
SET search_vector =
  to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''));

これで、各記事に「検索用の単語集合」が保存されました。
以降の検索は、こう書けます。

SELECT
  id,
  title
FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgres & database');

SELECT
  id,
  title
FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgres & database');

毎回 to_tsvector を計算するのではなく、
「事前に計算しておいたカラムに対して検索する」形に変わったのがポイントです。

本番運用では、INSERT/UPDATE 時に search_vector を更新するトリガーを付けるのが定番ですが、
Day14 では「検索用カラムを持つ」という発想だけ押さえておけばOKです。

GIN インデックスで“検索エンジンっぽい速さ”にする

「全文検索はインデックスを張って初めて本領発揮する」

search_vector を持ったら、次はインデックスです。
全文検索では GIN インデックスを使うのが定番です。

CREATE INDEX idx_articles_search_vector
ON articles
USING GIN (search_vector);

CREATE INDEX idx_articles_search_vector
ON articles
USING GIN (search_vector);

これで、search_vector @@ ... を使った検索が一気に速くなります。

イメージとしては、「単語ごとに“どの記事に出てくるか”の逆引き表を作る」感じです。
postgres という単語が出てくる記事だけを、インデックスから一瞬で引っ張ってきてくれるので、
全件スキャンする必要がなくなります。

ここで大事なのは、「全文検索はインデックス前提で設計する」という感覚です。
to_tsvector(...) @@ to_tsquery(...) をその場で計算しているだけだと、
「動くけど遅い検索」で終わってしまいます。

検索結果にスコアを付ける

「“それっぽい順番”で並べるための ranking」

検索といえば、「どの結果がより“それっぽい”か」をスコアで並べるのが定番です。
PostgreSQL には、そのための関数が用意されています。

代表的なのが ts_rank と ts_rank_cd です。
まずは ts_rank を使った最小例を見てみます。

SELECT
  id,
  title,
  ts_rank(search_vector, to_tsquery('english', 'postgres & database')) AS rank
FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgres & database')
ORDER BY rank DESC
LIMIT 20;

SELECT
  id,
  title,
  ts_rank(search_vector, to_tsquery('english', 'postgres & database')) AS rank
FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgres & database')
ORDER BY rank DESC
LIMIT 20;

ここで起きていることは、

search_vector @@ ... で「条件にマッチする記事」を絞る。
ts_rank(...) で「どれくらい強くマッチしているか」をスコア化する。
ORDER BY rank DESC で「スコアの高い順」に並べる。

ts_rank は、ざっくり言うと「検索語が何回出てくるか」「どの位置に出てくるか」などを元にスコアを計算します。
タイトルに出てくる方がスコアが高くなったり、複数回出てくるとスコアが上がったりします。

これだけで、「ただヒットした順」ではなく、「それっぽい順」に並んだ検索結果が作れます。

タイトルを重く、本文を軽くする

「重み付けで“タイトルマッチを優遇する”」

現実の検索では、「タイトルにヒットした記事を優先したい」ことが多いです。
PostgreSQL では、setweight を使って「どのフィールドをどれくらい重くするか」を調整できます。

まず、search_vector を作るときに重みを付けます。

UPDATE articles
SET search_vector =
  setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
  setweight(to_tsvector('english', coalesce(body,  '')), 'B');

UPDATE articles
SET search_vector =
  setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
  setweight(to_tsvector('english', coalesce(body,  '')), 'B');

ここでの意味は、

タイトル由来の単語 → 重み A（最も重い）
本文由来の単語 → 重み B（少し軽い）

という設定です。

その上で、検索時に ts_rank を使うと、
タイトルにヒットした記事の方が高いスコアになります。

SELECT
  id,
  title,
  ts_rank(search_vector, to_tsquery('english', 'postgres & database')) AS rank
FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgres & database')
ORDER BY rank DESC
LIMIT 20;

SELECT
  id,
  title,
  ts_rank(search_vector, to_tsquery('english', 'postgres & database')) AS rank
FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgres & database')
ORDER BY rank DESC
LIMIT 20;

同じ「postgres & database」を含んでいても、

タイトルに両方入っている記事
本文にしか入っていない記事

では、前者の方が上に来るようになります。

ここが、「全文検索を“検索エンジンっぽく”するための一歩」です。

検索キーワードをユーザー入力から受け取るときの注意

「to_tsquery にそのまま突っ込まない」

セキュリティの話も少しだけ触れておきます。
ユーザーが検索フォームに入力した文字列を、そのまま to_tsquery に渡すのは危険です。

to_tsquery の入力は、「演算子（& や |）」「!」「括弧」などを含めた“検索式”として解釈されます。
例えば、ユーザーが postgres & !mysql のような文字列を入れると、
「postgres を含み、mysql を含まない記事」という検索条件になります。

それ自体は機能としては便利ですが、
「想定していない複雑な検索式」を投げ込まれると、パフォーマンスや挙動の予測が難しくなります。

安全側に倒すなら、

ユーザー入力は単なる“単語列”として扱う。
サーバー側でスペース区切りにして、word1 & word2 & ... のように組み立てる。
plainto_tsquery を使って、「普通の文章」から安全な tsquery を作る。

という方針がよく取られます。

例えば：

SELECT
  id,
  title,
  ts_rank(search_vector, plainto_tsquery('english', 'postgres database')) AS rank
FROM articles
WHERE search_vector @@ plainto_tsquery('english', 'postgres database')
ORDER BY rank DESC;

SELECT
  id,
  title,
  ts_rank(search_vector, plainto_tsquery('english', 'postgres database')) AS rank
FROM articles
WHERE search_vector @@ plainto_tsquery('english', 'postgres database')
ORDER BY rank DESC;

plainto_tsquery は、「演算子を含まない普通の文章」を前提に、
いい感じに単語に分解して AND 条件の tsquery を作ってくれます。

「ユーザー入力をそのまま検索式として解釈させない」というのは、
全文検索に限らず、セキュリティ的に大事な姿勢です。

FULL TEXT SEARCH を導入するときの“最小構成”

「これだけ押さえれば、実用ラインに乗る」

ここまでの内容を、「実際に導入するときの最小セット」としてまとめると、こうなります。

検索対象テーブルに tsvector カラム（例: search_vector）を追加する。
タイトル・本文などから to_tsvector で search_vector を作る（必要なら setweight で重み付け）。
search_vector に GIN インデックスを張る。
検索時は plainto_tsquery か、サーバー側で組み立てた to_tsquery を使い、
WHERE search_vector @@ ... ORDER BY ts_rank(search_vector, ...) DESC で結果を返す。

このパターンを一度自分の手で書けるようになると、
「PostgreSQLで全文検索をやる」という選択肢を現実的に検討できるようになります。

Day14 後半のまとめ

実用的な全文検索は、「検索用の tsvector カラム（例: search_vector）をテーブルに持ち、タイトルや本文から to_tsvector（必要なら setweight で重み付け）で事前計算しておき、そのカラムに GIN インデックスを張る」ことで、search_vector @@ to_tsquery(...) という形の検索を高速に実行できるようにするところから始まる。
検索結果には ts_rank(search_vector, tsquery) を使ってスコアを付け、ORDER BY rank DESC で「タイトルにヒットしたものを優先する」「より多くマッチしているものを上に出す」といった“それっぽい順番”を実現できるし、ユーザー入力から検索条件を作るときは plainto_tsquery を使って「普通の文章 → 安全な tsquery」に変換することで、意図しない複雑な検索式やパフォーマンス問題を避けられる。
最終的に、「LIKE の代わりに tsvector + tsquery + GIN + ts_rank という型を使う」発想を持てるようになることが、Day14 後半の着地点になる。