Ranking with ts_rank in PostgreSQL - Step-by-Step Execution

Concept Flow - Ranking with ts_rank

Input: Text & Query

↓

Convert Text to tsvector

↓

Convert Query to tsquery

↓

Calculate ts_rank(to_tsvector(content), query)

↓

Assign Rank Score

↓

Order Results by Rank Descending

↓

Return Ranked Rows

The flow shows how PostgreSQL converts text and query into searchable vectors, calculates a rank score with ts_rank, and orders results by relevance.

Execution Sample

PostgreSQL

SELECT title, ts_rank(to_tsvector(content), query) AS rank
FROM documents, to_tsquery('search & text') query
WHERE to_tsvector(content) @@ query
ORDER BY rank DESC;

This query ranks documents by relevance to the search query using ts_rank and orders them from highest to lowest rank.

Execution Table

Step	Document Title	to_tsvector(content)	tsquery	ts_rank	Rank Assigned	Order Position
1	PostgreSQL Tutorial	'postgre':1 'tutorial':2	'search' & 'text'	0.0	0.0	-
2	Full Text Search Guide	'full':1 'search':2 'text':3 'guide':4	'search' & 'text'	0.5	0.5	2
3	Text Search in PostgreSQL	'postgre':3 'search':2 'text':1	'search' & 'text'	0.8	0.8	1
4	Introduction to Databases	'databas':3 'introduct':1 'to':2	'search' & 'text'	0.0	0.0	-
5	Advanced SQL Queries	'advanc':2 'queri':3 'sql':1	'search' & 'text'	0.0	0.0	-
6	Exit	No more rows	N/A	N/A	N/A	Ranking complete

💡 Ranking stops after all documents are scored and ordered by ts_rank descending.

Variable Tracker

Variable	Start	After Doc 1	After Doc 2	After Doc 3	After Doc 4	After Doc 5	Final
ts_rank	N/A	0.0	0.5	0.8	0.0	0.0	Final ranks assigned
Order Position	N/A	-	2	1	-	-	Rows ordered by rank

Key Moments - 2 Insights

Why does 'PostgreSQL Tutorial' get a rank of 0.0 even though it contains 'postgre'?

Because the search query requires both 'search' AND 'text' (tsquery 'search & text'), and 'PostgreSQL Tutorial' does not contain both terms, so ts_rank returns 0.0 as shown in execution_table row 1.

How does ts_rank affect the order of results?

Rows with higher ts_rank values are placed higher in the order. For example, 'Text Search in PostgreSQL' has rank 0.8 and is ordered first, as seen in execution_table rows 3 and 6.

Visual Quiz - 3 Questions

Look at the execution table, what is the ts_rank value for 'Full Text Search Guide'?

A 0.5

B 0.0

C 0.8

D 1.0

💡 Hint

Check the ts_rank column in execution_table row 2.

At which step does the rank become zero because the document does not match the query?

A Step 3

B Step 1

C Step 2

D Step 5

💡 Hint

Look at execution_table row 1 where ts_rank is 0.0.

If the query changed to 'search | text' (OR instead of AND), which document would likely get a non-zero rank at step 1?

A Advanced SQL Queries

B Introduction to Databases

C PostgreSQL Tutorial

D None

💡 Hint

Refer to variable_tracker and execution_table for how ts_rank depends on matching terms.

Concept Snapshot

Ranking with ts_rank in PostgreSQL:
- Convert text to tsvector and query to tsquery
- Use ts_rank(tsvector, tsquery) to score relevance
- Higher rank means more relevant
- Filter with @@ operator to match query
- Order results by rank DESC to get best matches first

Full Transcript

This visual execution trace shows how PostgreSQL ranks documents using ts_rank. First, document content is converted to a tsvector, and the search query is converted to a tsquery. Then ts_rank calculates a relevance score for each document based on how well it matches the query. Documents that do not match both terms in the AND query get a rank of zero and are excluded from the ordered results. The results are then ordered by rank descending, showing the most relevant documents first. This step-by-step trace helps beginners see how ranking works internally and how the query affects the scores and order.

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