Relevance ranking orders full-text search results by how closely their contents match a query, rather than relying only on a movie's vote average. Postgres compares matching lexemes, their frequency, and their positions to produce a more useful result order. section 6.4 section 6.0
Postgres provides two built-in ranking functions:
- Use ts_rank to rank documents by query-term frequency and position. section 6.4
- Use ts_rank_cd when term density matters, giving higher scores to documents whose query terms appear closer together. section 6.4
- Use setweight to make matches in important fields, such as a movie name, outrank matches in descriptions. section 6.4
Ranking movies for “ghosts”
A basic query orders matching movies by vote_average, placing A Girl Walks Home Alone at Night first even though its lexemes contain “ghost” once. Ranking with ts_rank instead places Ghost first because “ghost” appears three times. Weighting the name as A and the description as B further emphasizes title matches; adding ghosts:A restricts matching to the higher-weight field.
