0Pricing
PostgreSQL Performance & Query Optimization · 课时

使用 ts_rank 调整排序与相关性

为文档各部分设置权重并调整排序函数，让最相关的结果优先出现。

使用 ts_rank 调整排序与相关性 是 CoddyKit 上的免费 PostgreSQL Performance & Query Optimization 课时。 这是第 2 节课，共 4 节。 你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 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 调整排序与相关性」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 PostgreSQL Performance & Query Optimization 课程的其余内容，请升级到 CoddyKit PRO。 PostgreSQL Performance & Query Optimization 课程共包含 4 节课。

「使用 ts_rank 调整排序与相关性」这节课中我会学到什么？

为文档各部分设置权重并调整排序函数，让最相关的结果优先出现。 你通过在浏览器中直接运行的动手代码来练习 PostgreSQL Performance & Query Optimization，全天候 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