0Pricing
PostgreSQL Performance & Query Optimization · Lesson

Ranking and Relevance Tuning with ts_rank

Weight document sections and tune ranking functions to surface the most relevant results first.

Ranking and Relevance Tuning with ts_rank is a free PostgreSQL Performance & Query Optimization lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the PostgreSQL Performance & Query Optimization learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

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.

Frequently asked questions

Is the “Ranking and Relevance Tuning with ts_rank” lesson free?

Yes — the full text of “Ranking and Relevance Tuning with ts_rank” is free to read here on the web, and the PostgreSQL Performance & Query Optimization course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the PostgreSQL Performance & Query Optimization course, upgrade to CoddyKit PRO.

What will I learn in “Ranking and Relevance Tuning with ts_rank”?

Weight document sections and tune ranking functions to surface the most relevant results first. You practise PostgreSQL Performance & Query Optimization with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start PostgreSQL Performance & Query Optimization?

No prior experience is required. PostgreSQL Performance & Query Optimization on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Ranking and Relevance Tuning with ts_rank” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this PostgreSQL Performance & Query Optimization lesson?

Yes. Every PostgreSQL Performance & Query Optimization lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Designing tsvector Columns and GIN Indexes
  2. Ranking and Relevance Tuning with ts_rank
  3. Fuzzy Matching with pg_trgm Similarity
  4. Combining Filters with Search Predicates
← Back to PostgreSQL Performance & Query Optimization