【実務・中級編】 全文検索ランキング – PostgreSQL

皆さん、こんにちは！ 最強のDBエンジニアこと、私がお届けする技術ブログの時間だよ。

「おいおい、またDBの話かよ…」って思ったそこの君、ちょっと待って！ 今日はね、みんなが日々使ってるWebサイトやアプリの裏側で、実はものすごくユーザー体験を左右してる、地味だけど超重要な話をするから、ぜひ最後まで付き合ってほしいんだ。

テーマは PostgreSQLの全文検索、その「賢い」使い方。
特に、ただヒットさせるだけじゃ物足りない、検索結果の関連度スコアリング (`ts_rank`, `ts_rank_cd`) と、検索結果のハイライト表示 (`ts_headline`) について、実務でどう使うか、先輩目線でみっちり伝授するよ！

—

検索、ただヒットすればいいってもんじゃないんだ

みんな、Webサイトの検索機能って使ってるよね？ 自分が探してる情報を見つけるために、キーワードを入れて「検索！」ってボタンを押す。すると、ズラズラ～っと結果が出てくる。

でもさ、もし検索結果が何百件、何千件も出てきて、しかもどれもこれも「なんか関係ありそうだけど、一番欲しい情報じゃないな…」って感じだったらどう？ ユーザーはすぐに離脱しちゃうよね。正直、一番知りたいのは「最も関連性の高い情報」のはずなんだ。

PostgreSQLの全文検索（`to_tsvector` と `to_tsquery` を使ったやつね）は、確かにパワフル。でも、それだけだと「キーワードが含まれているかどうか」しか判断できない。そこで登場するのが、今日の主役たち！

• `ts_rank` / `ts_rank_cd`: 検索結果の「関連度」を数値で評価してくれる関数
• `ts_headline`: 検索結果の中で、キーワードがどこにあるかをハイライト表示してくれる関数

こいつらを使いこなせば、ユーザーは「求めている情報に最短でたどり着ける」ようになる。これって、めちゃくちゃ大事なUX改善なんだよ。

—

賢い検索結果の出し方、まずは「関連度」から！

`ts_rank` / `ts_rank_cd` で検索結果に「重み」をつける

「関連度」って、どうやって決めると思う？ 単純にキーワードの出現回数だけじゃダメだよね。例えば、記事の「タイトル」にキーワードが入ってるのと、「本文の隅っこ」にキーワードが入ってるのとじゃ、重みが全然違うはずだ。

`ts_rank` と `ts_rank_cd` は、その「重み付け」を柔軟に設定できるんだ。

まずは、お決まりのテーブル作成から始めようか。

— サンプルテーブルの作成
CREATE TABLE articles (
id SERIAL PRIMARY KEY,
title TEXT NOT NULL,
content TEXT NOT NULL,
tags TEXT[],
document_tsv TSVECTOR — 全文検索用のTSVECTORカラム
);

— TSVECTORカラムの更新関数とトリガー
CREATE OR REPLACE FUNCTION update_article_tsv() RETURNS TRIGGER AS $$
BEGIN
NEW.document_tsv =
setweight(to_tsvector(‘japanese’, NEW.title), ‘A’) ||
setweight(to_tsvector(‘japanese’, NEW.content), ‘B’) ||
setweight(to_tsvector(‘japanese’, array_to_string(NEW.tags, ‘ ‘)), ‘C’);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_update_article_tsv
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION update_article_tsv();

— サンプルデータの挿入
INSERT INTO articles (title, content, tags) VALUES
(‘PostgreSQLの全文検索入門’, ‘PostgreSQLの全文検索機能は非常に強力です。to_tsvectorとto_tsqueryを使って効率的な検索を実現しましょう。’, ARRAY[‘PostgreSQL’, ‘全文検索’, ‘TSVECTOR’]),
(‘インデックス設計の極意’, ‘データベースのパフォーマンスを最大化するには、適切なインデックス設計が不可欠です。B-treeやGINインデックスを使いこなしましょう。’, ARRAY[‘データベース’, ‘インデックス’, ‘パフォーマンス’]),
(‘DBエンジニアのためのGo言語’, ‘Go言語は、特にデータベース関連のツールやAPI開発でその真価を発揮します。並行処理と高速性が魅力です。’, ARRAY[‘Go言語’, ‘API’, ‘開発’]),
(‘PostgreSQLで学ぶデータモデリング’, ‘データモデリングは、リレーショナルデータベース設計の基盤です。正規化や非正規化のバランスが重要になります。’, ARRAY[‘PostgreSQL’, ‘データモデリング’, ‘正規化’]);

— 全文検索用のGINインデックスの作成（これがないとパフォーマンスが死ぬぞ！）
CREATE INDEX idx_articles_document_tsv ON articles USING GIN (document_tsv);

はい、これで準備完了。
`update_article_tsv` 関数を見てほしいんだけど、`setweight` っていう関数を使ってるよね。これが肝なんだ。

• `title` には `’A’`
• `content` には `’B’`
• `tags` には `’C’`

と、それぞれ重み付けしてる。PostgreSQLの全文検索では、A, B, C, D の4段階で重みを付けられるんだ。Aが一番重くて、Dが一番軽い、と覚えておけばOK。

じゃあ、実際に検索してみよう。例えば、「PostgreSQL」というキーワードで検索して、関連度順に並べたい場合。

SELECT
id,
title,
ts_rank(document_tsv, to_tsquery(‘japanese’, ‘PostgreSQL’)) AS rank_score
FROM
articles
WHERE
document_tsv @@ to_tsquery(‘japanese’, ‘PostgreSQL’)
ORDER BY
rank_score DESC;

どう？ `ts_rank(document_tsv, to_tsquery(‘japanese’, ‘PostgreSQL’))` って部分が、関連度スコアを計算してるんだ。結果はこんな感じになるはず。

| id | title | rank_score |
|—-|——————————|————|
| 1 | PostgreSQLの全文検索入門 | 0.09341535 |
| 4 | PostgreSQLで学ぶデータモデリング | 0.046707676 |

このスコアが、まさに「関連度」を表してるんだ。高いほど関連性が強いと判断される。

`weights` を使ってもっと細かく制御する

さっきの `ts_rank` は、実はデフォルトの重み（A=1.0, B=0.4, C=0.2, D=0.1）を使ってるんだ。でも、もっと柔軟に重みを設定したい場合もあるよね。その時は、`weights` 引数を使うんだ。

`ts_rank(weights float4[], vector tsvector, query tsquery)`

`weights` は4つの要素を持つ配列で、それぞれ D, C, B, A の順に対応する。

例えば、「タイトル（A）が一番重要だけど、タグ（C）もかなり重要視したい。本文（B）はそこそこ、Dは今回は使ってないから無視」みたいな場合は、こんな感じ。

— D, C, B, A の順で重みを指定
— 例えば、D=0.1, C=0.5, B=0.3, A=1.0 にしたい場合
SELECT
id,
title,
ts_rank(‘{0.1, 0.5, 0.3, 1.0}’, document_tsv, to_tsquery(‘japanese’, ‘PostgreSQL’)) AS rank_score_custom
FROM
articles
WHERE
document_tsv @@ to_tsquery(‘japanese’, ‘PostgreSQL’)
ORDER BY
rank_score_custom DESC;

これで、より自分のアプリケーションに合った関連度を計算できるようになるんだ。
「実務ではね、この `weights` のチューニングが結構大事なんだよ。ユーザーからのフィードバックやABテストを繰り返して、最適な値を模索していくんだ。」

`ts_rank_cd` との違い、正規化って何？

さて、もう一つ `ts_rank_cd` っていう関数があるよね。こっちは `_cd` が付いてるけど、何が違うかというと、ドキュメントの長さによる正規化 をしてくれるんだ。

`ts_rank` は、単純にキーワードの出現頻度と重みだけでスコアを計算する。だから、ものすごく長い記事だと、偶然キーワードがたくさん出てきて、関連度が低いのにスコアが高くなっちゃう ことがあるんだ。

例えば、「PostgreSQL」って単語が1回しか出てこない短い記事と、100回出てくるけど全然関係ない超長い記事があったら、後者の方がスコアが高くなっちゃう可能性がある。これじゃ困るよね。

`ts_rank_cd` は、そのドキュメントの長さも考慮してスコアを正規化してくれるんだ。つまり、「短い記事でキーワードがバッチリ入ってる」方が、「長い記事でたまたまキーワードがたくさん入ってる」よりも高く評価されやすくなる。

実務では、ドキュメントの長さがバラバラな場合は `ts_rank_cd` を使うことが多いかな。特にブログ記事とか、ユーザーが投稿するコンテンツなんかは長さがまちまちだからね。

— ts_rank_cd を使った例
SELECT
id,
title,
ts_rank_cd(‘{0.1, 0.5, 0.3, 1.0}’, document_tsv, to_tsquery(‘japanese’, ‘PostgreSQL’)) AS rank_score_cd
FROM
articles
WHERE
document_tsv @@ to_tsquery(‘japanese’, ‘PostgreSQL’)
ORDER BY
rank_score_cd DESC;

引数は `ts_rank` と同じだよ。ドキュメントの特性に合わせて使い分けてみてね。

—

検索結果を「魅せる」魔法、ハイライト表示！

`ts_headline` でキーワードを一目でわかるようにする

関連度で並べ替えたはいいけど、検索結果の一覧に表示される記事のタイトルや概要を見たとき、どこにキーワードがあるかパッとわかったら、もっとユーザーは嬉しいよね？

それを実現するのが `ts_headline` だ！ これはもう、ユーザー体験向上のための必須アイテムと言っていい。

使い方はこんな感じ。

`ts_headline(text TEXT, query TSQUERY, [options TEXT])`

`text` にはハイライトしたい元のテキスト（例えば `content` カラム）、`query` には検索に使ったクエリを渡す。そして、`options` でハイライトの仕方を細かく設定できるんだ。

試しに、「PostgreSQL」で検索して、本文の一部をハイライト表示してみよう。

SELECT
id,
title,
ts_rank_cd(document_tsv, to_tsquery(‘japanese’, ‘PostgreSQL’)) AS rank_score,
ts_headline(
content,
to_tsquery(‘japanese’, ‘PostgreSQL’),
‘StartSel=, StopSel=, MaxFragments=1, FragmentDelimiter=…, MaxWords=10, MinWords=5′
) AS highlighted_content
FROM
articles
WHERE
document_tsv @@ to_tsquery(‘japanese’, ‘PostgreSQL’)
ORDER BY
rank_score DESC;

この `options` がね、なかなか奥深いんだ。いくつか主要なものを紹介するね。

• `StartSel` / `StopSel`: ハイライトする部分の開始タグと終了タグ。HTMLタグ（``, `` など）を指定することが多い。
• `MaxFragments`: キーワードが出現する部分を何箇所表示するか。複数箇所にキーワードがある場合、それぞれの断片を表示してくれる。`1` にすると最初の1箇所だけ。
• `FragmentDelimiter`: 複数の断片を表示するときの区切り文字。デフォルトは `…`。
• `MaxWords`: 1つの断片に含める最大単語数。多すぎると概要じゃなくなるから注意。
• `MinWords`: 1つの断片に含める最小単語数。少なすぎると文脈がわからなくなる。
• `ShortWord`: この文字数以下の単語はハイライトしない、という設定。