0Pricing
PostgreSQL Performance & Query Optimization · レッスン

ts_rankによるランキングと関連度のチューニング

ドキュメントのセクションに重みを付け、ランキング関数を調整して、最も関連性の高い結果を先に表示します。

「ts_rankによるランキングと関連度のチューニング」はCoddyKit上の無料PostgreSQL Performance & Query Optimizationレッスンです。 これはレッスン2/4です。 下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。 これはPostgreSQL Performance & Query Optimization学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 PostgreSQL Performance & Query Optimizationコースには全4レッスンが含まれています。

このレッスンの一部はまだ翻訳されておらず、英語で表示されています。

Why Ranking Matters

A full-text query with @@ only tells you whether a document matches a query, not how well. To surface the most relevant rows first, you need a ranking function.

PostgreSQL ships two: ts_rank (frequency-based) and ts_rank_cd (cover-density, considers term proximity). Both return a real score you sort by.

  • Matching is binary, fast, and index-backed.
  • Ranking is a separate, more expensive computation done on the matched rows.
SELECT title,
       ts_rank(to_tsvector('english', body), query) AS rank
FROM articles, to_tsquery('english', 'index & performance') query
WHERE to_tsvector('english', body) @@ query
ORDER BY rank DESC
LIMIT 10;

How ts_rank Scores

ts_rank bases its score on term frequency: how often the query lexemes appear in the document, and their assigned weights. More occurrences of a query term generally means a higher score.

Critically, the rank is computed against the tsvector, which stores lexeme positions. A document where the term appears 5 times outranks one where it appears once, all else equal.

  • ts_rank ignores how close terms are to each other.
  • ts_rank_cd rewards documents where query terms cluster together.

Weight Labels A, B, C, D

Each lexeme position in a tsvector can carry a weight label: A, B, C, or D. Use setweight() to tag different document sections so a match in the title counts more than a match in the body.

D is the default (lowest). The convention is: A = title, B = abstract/summary, C = body, D = comments or metadata.

You build a weighted vector by concatenating setweight() calls with ||.

SELECT setweight(to_tsvector('english', 'PostgreSQL Indexing'), 'A') ||
       setweight(to_tsvector('english', 'A guide to fast queries'), 'B') ||
       setweight(to_tsvector('english', 'Detailed body text about GIN indexes'), 'C');

Storing a Weighted tsvector

For performance, precompute the weighted tsvector into a generated column and index it with GIN. This means ranking and matching both run against the same weighted vector, and you never re-tokenize at query time.

The generated column recomputes automatically when title or body changes, so it stays consistent.

ALTER TABLE articles
  ADD COLUMN search_vec tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(body, '')),  'C')
  ) STORED;

CREATE INDEX articles_search_idx ON articles USING GIN (search_vec);

Tuning Weights with the Array

ts_rank accepts an optional first argument: a 4-element float4[] of multipliers for labels in the order {D, C, B, A}. Note the order — it runs D first, A last.

The default array is {0.1, 0.2, 0.4, 1.0}. Raise the A multiplier to make title matches dominate even more, or flatten the array to reduce the impact of section weighting.

SELECT title,
       ts_rank('{0.1, 0.2, 0.4, 1.0}', search_vec, query) AS rank
FROM articles, to_tsquery('english', 'gin & index') query
WHERE search_vec @@ query
ORDER BY rank DESC
LIMIT 10;

Length Normalization

By default ts_rank does not normalize for document length, so long documents can accumulate higher scores simply by being long. The optional final integer argument controls normalization via bit flags you sum together.

  • 0 — ignore length (default)
  • 1 — divide rank by 1 + log(length)
  • 2 — divide rank by length
  • 4 — divide by mean harmonic distance (cd only)
  • 8 — divide by number of unique words
  • 16 — divide by 1 + log(unique words)
  • 32 — divide by itself + 1 (maps rank into [0,1))

Applying Normalization

Flag 1 is the most common choice: it gently penalizes long documents using a logarithm so a 2000-word article doesn't crush a focused 200-word one. Combine flags by summing them, e.g. 1|32 = 33 to also map into [0,1).

A normalized-to-[0,1) score is convenient when you want to blend full-text rank with other signals like recency or popularity.

SELECT title,
       ts_rank(search_vec, query, 1) AS rank_lognorm,
       ts_rank(search_vec, query, 33) AS rank_0_to_1
FROM articles, to_tsquery('english', 'query & optimization') query
WHERE search_vec @@ query
ORDER BY rank_lognorm DESC
LIMIT 10;

ts_rank_cd for Phrase Proximity

ts_rank_cd implements cover density ranking: it rewards documents where the query lexemes appear close together. This needs positional information, so it only works on a tsvector that still has positions (not stripped).

For queries like "query planner" where adjacency signals relevance, ts_rank_cd usually beats plain ts_rank. It accepts the same weight array and normalization arguments.

SELECT title,
       ts_rank_cd(search_vec, query, 1) AS cd_rank
FROM articles,
     phraseto_tsquery('english', 'query planner') query
WHERE search_vec @@ query
ORDER BY cd_rank DESC
LIMIT 10;

The Two-Phase Performance Pattern

Ranking is CPU-bound and runs per matched row, so never let it run over millions of rows. The winning pattern is two-phase: filter cheaply with the GIN index, then rank only the survivors.

Push the @@ match (index-backed) into a subquery or CTE, optionally with a coarse LIMIT, then compute ts_rank on that small candidate set.

  • The index narrows millions to thousands.
  • ts_rank then sorts only thousands.
WITH candidates AS (
  SELECT id, title, search_vec
  FROM articles
  WHERE search_vec @@ to_tsquery('english', 'index & tuning')
  LIMIT 500
)
SELECT id, title,
       ts_rank(search_vec, to_tsquery('english', 'index & tuning')) AS rank
FROM candidates
ORDER BY rank DESC
LIMIT 10;

Ranking Is Not Indexable

A common misconception: that a GIN index can satisfy ORDER BY ts_rank(...). It cannot. GIN indexes accelerate the @@ membership test, but ts_rank is a black-box function whose value isn't stored in the index, so PostgreSQL must compute it and then sort.

If ranking sort is a bottleneck, options include: precomputing a static quality score column, using RUM indexes (an extension that can return rows in rank order), or capping the candidate set first.

Blending Rank with Business Signals

Pure text rank rarely matches product intuition. Blend the normalized text score with signals like recency and popularity to compute a final ordering. Because flag 32 maps text rank into [0,1), it composes cleanly with other normalized factors.

Keep the @@ filter index-backed; the blend math only runs on matched candidate rows.

SELECT id, title,
       ts_rank(search_vec, query, 32) AS text_score,
       ts_rank(search_vec, query, 32) * 0.7
         + (1.0 / (1 + extract(epoch FROM now() - created_at) / 86400)) * 0.3
         AS final_score
FROM articles, to_tsquery('english', 'postgres & performance') query
WHERE search_vec @@ query
ORDER BY final_score DESC
LIMIT 10;

Quick Check

You rank search results over a 5-million-row table and the query is slow. EXPLAIN shows a Bitmap Index Scan on the GIN index followed by a Sort on ts_rank(...). What is the most effective fix?

Recap

You learned to tune full-text relevance in PostgreSQL:

  • ts_rank scores by term frequency; ts_rank_cd rewards proximity (needs positions).
  • Tag sections with setweight() using labels A/B/C/D, and store the weighted vector in a GIN-indexed generated column.
  • The weight array {D, C, B, A} (default {0.1,0.2,0.4,1.0}) tunes section influence.
  • The normalization flag controls length penalties; 1 applies a log penalty, 32 maps into [0,1) for blending.
  • Ranking is not indexable: always filter with @@ first, then rank the small candidate set.

よくある質問

「ts_rankによるランキングと関連度のチューニング」レッスンは無料ですか？

はい。「ts_rankによるランキングと関連度のチューニング」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、PostgreSQL Performance & Query Optimizationコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 PostgreSQL Performance & Query Optimizationコースには全4レッスンが含まれています。

「ts_rankによるランキングと関連度のチューニング」で何を学びますか？

ドキュメントのセクションに重みを付け、ランキング関数を調整して、最も関連性の高い結果を先に表示します。 ブラウザで直接実行するハンズオンコードでPostgreSQL Performance & Query Optimizationを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

PostgreSQL Performance & Query Optimizationを始めるのに経験は必要ですか？

事前経験は必要ありません。CoddyKitのPostgreSQL Performance & Query Optimizationは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン2/4です。

「ts_rankによるランキングと関連度のチューニング」レッスンにはどのくらい時間がかかりますか？

ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。

このPostgreSQL Performance & Query Optimizationレッスンでコードを書いて実行できますか？

はい。すべてのPostgreSQL Performance & Query Optimizationレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。

このコースのすべてのレッスン

  1. tsvectorカラムとGINインデックスの設計
  2. ts_rankによるランキングと関連度のチューニング
  3. pg_trgm類似度によるあいまい検索
  4. フィルターと検索述語の組み合わせ
← PostgreSQL Performance & Query Optimizationに戻る