Classer les résultats et régler la pertinence avec ts_rank

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.

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.

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 ||.

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.

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.

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))

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.

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.

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.

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.

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.

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?

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.