Ranking with ts_rank in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Ranking documents by relevance using ts_rank

Given a table documents with columns id and content, and a full-text search query to_tsquery('english', 'database & indexing'), which SQL query correctly ranks documents by relevance using ts_rank and returns the top 3?

PostgreSQL

SELECT id, ts_rank(to_tsvector('english', content), to_tsquery('english', 'database & indexing')) AS rank
FROM documents
WHERE to_tsvector('english', content) @@ to_tsquery('english', 'database & indexing')
ORDER BY rank DESC
LIMIT 3;

A SELECT id, ts_rank(to_tsvector('english', content), to_tsquery('english', 'database & indexing')) AS rank FROM documents ORDER BY rank DESC LIMIT 3;

B SELECT id, ts_rank(to_tsquery('english', 'database & indexing'), to_tsvector('english', content)) AS rank FROM documents WHERE to_tsvector('english', content) @@ to_tsquery('english', 'database & indexing') ORDER BY rank DESC LIMIT 3;

C SELECT id, ts_rank(to_tsvector('english', content), to_tsquery('english', 'database | indexing')) AS rank FROM documents WHERE to_tsvector('english', content) @@ to_tsquery('english', 'database & indexing') ORDER BY rank DESC LIMIT 3;

D SELECT id, ts_rank(to_tsvector('english', content), to_tsquery('english', 'database & indexing')) AS rank FROM documents WHERE to_tsvector('english', content) @@ to_tsquery('english', 'database & indexing') ORDER BY rank DESC LIMIT 3;

💡 Hint

Remember that ts_rank takes the document vector first, then the query vector, and the WHERE clause must filter matching documents.

Option D correctly uses ts_rank with the document vector first and the query second. It filters documents matching the query and orders by rank descending, limiting to top 3.

Option D reverses the arguments to ts_rank, which is invalid.

Option D uses a different query with | (OR) instead of & (AND), changing the meaning.

Option D lacks the WHERE clause, so it ranks all documents including non-matching ones, which is incorrect.

🧠 Conceptual

intermediate

Understanding ts_rank normalization options

Which statement correctly describes the effect of the normalization parameter in ts_rank(vector, query, normalization) in PostgreSQL?

A Normalization changes the language dictionary used for parsing the document vector.

B Normalization adjusts the rank by document length and frequency to prevent longer documents from scoring higher just because they have more words.

C Normalization converts the rank score into a boolean value indicating match or no match.

D Normalization sorts the results alphabetically instead of by rank.

💡 Hint

Think about why longer documents might unfairly get higher ranks without adjustment.

Normalization in ts_rank adjusts the ranking score to account for document length and term frequency, so longer documents don't get higher scores just because they contain more words.

Options A, C, and D describe unrelated behaviors.

📝 Syntax

advanced

Identify the syntax error in ts_rank usage

Which of the following SQL queries will raise a syntax error when using ts_rank in PostgreSQL?

A SELECT ts_rank(to_tsvector('english', content), to_tsquery('english', 'search & term')) FROM documents;

B SELECT ts_rank(to_tsvector('english', content), to_tsquery('english', 'search & term'), 0) FROM documents;

C SELECT ts_rank(to_tsvector('english', content), 'search & term') FROM documents;

D SELECT ts_rank(to_tsvector('english', content), to_tsquery('english', 'search & term'), 1) FROM documents;

💡 Hint

Check the types of arguments passed to ts_rank.

Option C passes a plain string instead of a tsquery type as the second argument, causing a syntax or runtime error.

Options A, B, and C correctly use to_tsquery for the query argument and valid normalization values.

❓ optimization

advanced

Improving performance of ts_rank queries

Which approach will best improve performance when running ts_rank queries on a large documents table?

A Create a GIN index on the <code>to_tsvector('english', content)</code> expression and use the <code>@@</code> operator in the WHERE clause.

B Add a B-tree index on the <code>content</code> column and use <code>LIKE '%search%'</code> in the WHERE clause.

C Use <code>ts_rank_cd</code> instead of <code>ts_rank</code> without any indexes.

D Store the rank values in a separate column and update them manually after inserts.

💡 Hint

Think about how PostgreSQL can quickly find matching rows for full-text search.

Option A uses a GIN index on the tsvector expression, which speeds up full-text search queries using the @@ operator, improving performance.

Option A uses a B-tree index and LIKE, which is inefficient for full-text search.

Option A changes the ranking function but does not improve indexing or search speed.

Option A adds complexity and maintenance overhead without improving query speed.

🔧 Debug

expert

Debugging unexpected ts_rank results

A developer runs this query to rank documents by relevance:

SELECT id, ts_rank(to_tsvector('english', content), to_tsquery('english', 'data & science')) AS rank FROM documents WHERE to_tsvector('english', content) @@ to_tsquery('english', 'data & science') ORDER BY rank DESC;

They notice some documents with the word 'database' rank higher than those with 'data' and 'science' explicitly. What is the most likely cause?

A The <code>to_tsquery</code> query is parsed as <code>data & science</code>, but 'database' matches the lexeme 'data' due to stemming, causing unexpected matches.

B The <code>ts_rank</code> function is ignoring the <code>WHERE</code> clause and ranking all documents.

C The <code>ORDER BY rank DESC</code> clause is sorting results in ascending order by mistake.

D The <code>to_tsvector</code> function is not applied to the <code>content</code> column, so ranking is random.

💡 Hint

Consider how stemming affects lexeme matching in full-text search.

PostgreSQL's full-text search uses stemming, so 'database' can be reduced to the lexeme 'data', causing it to match the query 'data & science'. This can cause documents with 'database' to rank higher if they also contain 'science'.

Options B, C, and D describe incorrect behaviors not consistent with the query.

Practice

1. What does the ts_rank function do in PostgreSQL full-text search?

easy

A. It creates a search query from a string.

B. It converts text into a searchable vector format.

C. It calculates the relevance ranking of a document for a search query.

D. It sorts rows alphabetically by a text column.

Solution

1. Step 1: Understand the purpose of ts_rank

   The ts_rank function scores how well a document matches a search query in full-text search.

2. Step 2: Differentiate from related functions

   to_tsvector prepares text, to_tsquery creates queries, but ts_rank ranks results.

3. Final Answer:

   It calculates the relevance ranking of a document for a search query. -> Option C

4. Quick Check:

   ts_rank = relevance score [OK]

Hint: Remember ts_rank ranks relevance, not text conversion [OK]

Common Mistakes:

• Confusing ts_rank with to_tsvector
• Thinking ts_rank creates search queries
• Assuming ts_rank sorts alphabetically

2. Which of the following is the correct syntax to rank search results using ts_rank in PostgreSQL?

easy

A. SELECT *, ts_rank(to_tsvector(text_column), to_tsquery('search')) AS rank FROM table ORDER BY rank DESC;

B. SELECT *, ts_rank(text_column, 'search') AS rank FROM table ORDER BY rank ASC;

C. SELECT *, ts_rank(to_tsquery(text_column), to_tsvector('search')) AS rank FROM table;

D. SELECT *, ts_rank('search', text_column) AS rank FROM table ORDER BY rank DESC;

Solution

1. Step 1: Check correct function arguments

   ts_rank requires a tsvector and a tsquery, so to_tsvector(text_column) and to_tsquery('search') are correct.

2. Step 2: Verify ordering and syntax

   Ordering by rank descending shows best matches first, matching SELECT *, ts_rank(to_tsvector(text_column), to_tsquery('search')) AS rank FROM table ORDER BY rank DESC;.

3. Final Answer:

   SELECT *, ts_rank(to_tsvector(text_column), to_tsquery('search')) AS rank FROM table ORDER BY rank DESC; -> Option A

4. Quick Check:

   Correct ts_rank syntax = SELECT *, ts_rank(to_tsvector(text_column), to_tsquery('search')) AS rank FROM table ORDER BY rank DESC; [OK]

Hint: ts_rank needs to_tsvector first, then to_tsquery second [OK]

Common Mistakes:

• Swapping to_tsvector and to_tsquery arguments
• Ordering results ascending instead of descending
• Passing plain text instead of tsvector or tsquery

3. Given the table documents with column content, what will this query return?

SELECT content, ts_rank(to_tsvector(content), to_tsquery('cat & dog')) AS rank FROM documents ORDER BY rank DESC LIMIT 2;

medium

A. The two documents containing either 'cat' or 'dog', sorted alphabetically.

B. The two documents with the highest relevance to both 'cat' and 'dog'.

C. All documents containing 'cat' or 'dog' without ranking.

D. An error because ts_rank cannot be used with LIMIT.

Solution

1. Step 1: Understand the query components

   The query uses to_tsquery('cat & dog') which searches for documents containing both 'cat' and 'dog'.

2. Step 2: Analyze ordering and limit

   Results are ordered by ts_rank descending, so highest relevance first, limited to 2 rows.

3. Final Answer:

   The two documents with the highest relevance to both 'cat' and 'dog'. -> Option B

4. Quick Check:

   ts_rank orders best matches first [OK]

Hint: AND in tsquery means both terms must appear [OK]

Common Mistakes:

• Thinking it returns documents with either word
• Assuming alphabetical order instead of rank
• Believing LIMIT causes an error with ts_rank

4. Identify the error in this query:

SELECT content, ts_rank(to_tsquery(content), to_tsvector('search')) AS rank FROM documents ORDER BY rank DESC;

medium

A. Arguments to ts_rank are reversed; tsvector should be first, tsquery second.

B. to_tsquery cannot be used on a column; it only accepts strings.

C. ORDER BY rank DESC is invalid syntax here.

D. The query is correct and will run without errors.

Solution

1. Step 1: Check ts_rank argument order

   ts_rank expects tsvector first, then tsquery. Here, they are reversed.

2. Step 2: Confirm argument types

   to_tsquery(content) is incorrect because content is text, not a query string; also to_tsvector('search') is a vector, but arguments are swapped.

3. Final Answer:

   Arguments to ts_rank are reversed; tsvector should be first, tsquery second. -> Option A

4. Quick Check:

   ts_rank(tsvector, tsquery) order matters [OK]

Hint: ts_rank always: tsvector first, tsquery second [OK]

Common Mistakes:

• Swapping ts_rank arguments
• Using to_tsquery on text column
• Ignoring argument types for ts_rank

5. You want to find the top 3 most relevant documents containing either 'apple' or 'orange' using high weights to boost relevance. Which query correctly uses ts_rank with weights to achieve this?

hard

A. SELECT content, ts_rank(setweight(to_tsvector(content), 'D'), to_tsquery('apple | orange')) AS rank FROM documents ORDER BY rank ASC LIMIT 3;

B. SELECT content, ts_rank(to_tsvector(content), to_tsquery('apple & orange')) AS rank FROM documents ORDER BY rank DESC LIMIT 3;

C. SELECT content, ts_rank(to_tsvector(content), to_tsquery('apple | orange')) AS rank FROM documents ORDER BY rank ASC LIMIT 3;

D. SELECT content, ts_rank(setweight(to_tsvector(content), 'A'), to_tsquery('apple | orange')) AS rank FROM documents ORDER BY rank DESC LIMIT 3;

Solution

1. Step 1: Understand weighting with setweight

   Using setweight with 'A' assigns highest weight to terms in to_tsvector, boosting the relevance of matching terms.

2. Step 2: Analyze the query logic

   to_tsquery('apple | orange') searches for either word, and ordering by rank descending with LIMIT 3 returns top 3 relevant documents.

3. Step 3: Compare options

   SELECT content, ts_rank(to_tsvector(content), to_tsquery('apple & orange')) AS rank FROM documents ORDER BY rank DESC LIMIT 3; uses AND (&) which requires both words, not either. SELECT content, ts_rank(to_tsvector(content), to_tsquery('apple | orange')) AS rank FROM documents ORDER BY rank ASC LIMIT 3; orders ascending, showing least relevant first. SELECT content, ts_rank(setweight(to_tsvector(content), 'D'), to_tsquery('apple | orange')) AS rank FROM documents ORDER BY rank ASC LIMIT 3; uses weight 'D' which is lowest, so it does not boost relevance as much.

4. Final Answer:

   SELECT content, ts_rank(setweight(to_tsvector(content), 'A'), to_tsquery('apple | orange')) AS rank FROM documents ORDER BY rank DESC LIMIT 3; -> Option D

5. Quick Check:

   Weighted ts_rank with OR query = SELECT content, ts_rank(setweight(to_tsvector(content), 'A'), to_tsquery('apple | orange')) AS rank FROM documents ORDER BY rank DESC LIMIT 3; [OK]

Hint: Use setweight with 'A' to boost preferred terms [OK]

Common Mistakes:

• Using AND instead of OR in tsquery
• Ordering by rank ascending instead of descending
• Assigning lowest weight to preferred terms