liveBook · Manning

Ts_rank

As explained in Just Use Postgres!

ts_rank is a PostgreSQL function that computes full-text search relevance from term frequency, lexeme positions, weights, and optional normalization.

ts_rank computes PostgreSQL full-text search relevance from term frequency and lexeme positions. Use it to replace popularity-only ordering with query-specific ranking. section 6.4

Its signature exposes the main ranking controls:

  • Pass a tsvector document and tsquery search expression; optionally provide weights and a normalization integer. section 6.4
  • Use A, B, C, and D labels to prioritize document regions; default weights are {0.1, 0.2, 0.4, 1.0} in D-to-A order. section 6.4
  • Compare ts_rank with ts_rank_cd when term density and query-term proximity should affect relevance. section 6.4

Rank movies matching “ghosts”

SELECT id, name, vote_average, ts_rank(lexemes, to_tsquery('ghosts')) AS search_rank FROM omdb.movies WHERE lexemes @@ to_tsquery('ghosts') ORDER BY search_rank DESC, vote_average DESC NULLS LAST LIMIT 10; This ordering places Ghost first, ahead of A Girl Walks Home Alone at Night, because frequency and position produce a higher search rank. Assigning movie names weight A and descriptions weight B further changes the ranking. section 6.4

Listing 6.11 Ranking search results with the ts_rank function 
SELECT id, name, vote_average, 
  ts_rank(lexemes, to_tsquery('ghosts')) AS search_rank
FROM omdb.movies
WHERE lexemes @@ to_tsquery('ghosts')
ORDER BY search_rank DESC, vote_average DESC NULLS LAST LIMIT 10;

Inspect the complete executable ranking query and its search_rank expression. section 6.4

How Just Use Postgres! covers it

How deep it goes
Defines the signature and explains frequency, positions, weights, and normalization without detailing every normalization value.
Examples
Uses movie records for “ghosts,” comparing Ghost with A Girl Walks Home Alone at Night.
What it assumes
Assumes lexemes stored as tsvector and search expressions converted to tsquery.
What you can do after
Apply it via PostgreSQL SQL to order matching movies by search rank, using vote average as a tiebreaker.
How it connects
Connects tsvector, tsquery, setweight, and ts_rank_cd to relevance ranking.
Cover of Just Use Postgres!
liveBookmanning.com
Read Just Use Postgres! on liveBook Get the book at manning.com

Related concepts

This explainer was generated by Manning's knowledge graph from the text of Just Use Postgres!. The section links point to the parts of the book it draws on.