tsvector and tsquery types in PostgreSQL - Step-by-Step Execution

Concept Flow - tsvector and tsquery types

Input Text

↓

Convert to tsvector

↓

Store/Search in tsvector column

↓

Create tsquery

↓

Match tsquery against tsvector

↓

Return matching rows

Text is converted into a searchable vector (tsvector). A query (tsquery) is created to search this vector. Matching rows are returned.

Execution Sample

PostgreSQL

SELECT to_tsvector('english', 'The quick brown fox') AS document_vector;
SELECT to_tsquery('english', 'quick & fox') AS search_query;
SELECT * FROM documents WHERE document_vector @@ to_tsquery('english', 'quick & fox');

Convert text to tsvector, create tsquery, then find rows where the vector matches the query.

Execution Table

Step	Action	Input	Output/Result
1	Convert text to tsvector	'The quick brown fox'	'brown':3 'fox':4 'quick':2
2	Create tsquery	'quick & fox'	'quick' & 'fox'
3	Match tsquery against tsvector	tsvector: 'brown':3 'fox':4 'quick':2, tsquery: 'quick' & 'fox'	TRUE (matches)
4	Match tsquery against tsvector	tsvector: 'brown':3 'fox':4, tsquery: 'quick' & 'fox'	FALSE (no match)
5	Exit	No more rows	Query complete

💡 All rows checked; query returns only rows where tsquery matches tsvector

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	Final
tsvector	NULL	'brown':3 'fox':4 'quick':2	'brown':3 'fox':4 'quick':2	'brown':3 'fox':4 'quick':2	'brown':3 'fox':4 'quick':2
tsquery	NULL	NULL	'quick' & 'fox'	'quick' & 'fox'	'quick' & 'fox'

Key Moments - 2 Insights

Why does 'quick & fox' match the tsvector but 'quick & rabbit' does not?

Because the tsvector contains 'quick' and 'fox' but does not contain 'rabbit'. The match requires all terms in tsquery to be present in tsvector (see execution_table rows 3 and 4).

What does the numbers after words in tsvector mean, like 'quick':2?

They represent the position of the word in the original text. This helps with phrase searching but does not affect simple matching (see execution_table row 1).

Visual Quiz - 3 Questions

Look at the execution_table row 1. What is the position of the word 'fox' in the tsvector?

A 4

B 2

C 3

D 1

💡 Hint

Check the output column in row 1 for 'fox': its position is after the colon.

At which step does the tsquery get created?

A Step 1

B Step 2

C Step 3

D Step 4

💡 Hint

Look at the Action column in the execution_table to find when tsquery is created.

If the tsvector does not contain the word 'quick', what will be the result of matching with tsquery 'quick & fox'?

A TRUE

B Error

C FALSE

D Partial match

💡 Hint

Refer to execution_table row 4 where missing a word causes the match to be FALSE.

Concept Snapshot

tsvector stores processed text as lexemes with positions.
tsquery represents search conditions using lexemes.
Use @@ operator to match tsquery against tsvector.
tsvector is created with to_tsvector(text).
tsquery is created with to_tsquery(query).
Matching returns TRUE if tsquery conditions are met in tsvector.

Full Transcript

This visual execution shows how PostgreSQL processes text search using tsvector and tsquery types. First, input text is converted into a tsvector, which breaks text into lexemes with their positions. Then, a tsquery is created representing the search terms and conditions. The tsquery is matched against the tsvector using the @@ operator. The execution table traces each step: converting text, creating query, matching, and results. Variables track the state of tsvector and tsquery through the steps. Key moments clarify common confusions like why some queries match and others don't, and what the position numbers mean. The quiz tests understanding of positions, creation steps, and matching logic. The snapshot summarizes the key points for quick reference.