Ranking with ts_rank in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the ts_rank function do in PostgreSQL?

ts_rank calculates a relevance score for text search results, ranking rows based on how well they match the search query.

beginner

Which data type is required as input for ts_rank?

ts_rank requires tsvector (document) and tsquery (search query) as inputs.

beginner

How does ts_rank help when searching a large text column?

It assigns a score to each row showing how closely the text matches the search terms, so you can order results by relevance.

intermediate

What is the difference between ts_rank and ts_rank_cd?

ts_rank_cd uses cover density ranking, which considers how close search terms appear together, often giving better ranking for phrase matches.

beginner

How do you use ts_rank in a query to get the top 5 most relevant rows?

Use ORDER BY ts_rank(document, query) DESC LIMIT 5 to sort by rank and get the top 5 matches.

What does ts_rank return in PostgreSQL?

A A numeric relevance score

B A boolean indicating match

C The matching text snippet

D The total number of matches

ts_rank returns a numeric score showing how well the document matches the query.

Which two inputs are required for ts_rank?

A <code>tsvector</code> and <code>tsquery</code>

B Two <code>tsvector</code> values

C Two <code>tsquery</code> values

D A string and an integer

ts_rank needs a tsvector document and a tsquery search query.

How can you get the most relevant search results using ts_rank?

A Use <code>LIMIT 1</code> without ordering

B Order by <code>ts_rank</code> ascending

C Order by <code>ts_rank</code> descending

D Filter where <code>ts_rank</code> = 0

Ordering by ts_rank descending shows the highest relevance first.

What does ts_rank_cd improve compared to ts_rank?

A Ignores stop words

B Returns a boolean instead of a score

C Works only with numeric data

D Considers how close search terms appear together

ts_rank_cd uses cover density to rank better when terms are close together.

If you want to rank search results by relevance, which SQL clause is essential?

A GROUP BY ts_rank(...)

B ORDER BY ts_rank(...) DESC

C WHERE ts_rank(...) = 0

D HAVING ts_rank(...) > 10

Ordering by ts_rank descending sorts results by relevance score.

Explain how ts_rank is used to rank full-text search results in PostgreSQL.

Think about how you find the best matching text in a search.

Describe the difference between ts_rank and ts_rank_cd and when you might use each.

Consider how closeness of words affects search relevance.