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.