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.