Tokenization
Raw product text is broken into searchable linguistic tokens.
Version 2 · Linguistic Retrieval
Search meaning, not just substrings.
V2 replaces raw substring matching with PostgreSQL
full-text search using tokenization, lexemes,
stemming, weighted fields, tsquery,
relevance ranking and contextual headlines.
Try
Aa
PostgreSQL linguistic search
Enter a query to inspect full-text matching, ranking, application performance and the database execution plan.
Lexemes
Words are normalized so related forms can map toward common searchable terms.
Ranking
Matching products receive relevance scores
using ts_rank_cd().
Running full-text search
Building vectors, matching lexemes and
analyzing PostgreSQL execution.
!
Search failed
Something went wrong while executing the query.
Query
Search report
Query
—
PostgreSQL
Waiting for plan
Execution report
Human-readable interpretation of
EXPLAIN ANALYZE.
DB
Primary operation
—
Execution information will appear here after a search.
Time PostgreSQL spent constructing the execution strategy.
Server-side time reported by PostgreSQL.
Rows inspected but rejected by the full-text filter.
Pages obtained from PostgreSQL's shared buffer cache.
Time until the selected node produced its first result.
Time until the selected node completed its work.
Execution flow
What PostgreSQL did
1
→
Sequential scan
Inspect products row by row.
2
→
Build tsvector
Normalize searchable fields.
3
→
Full-text filter
Evaluate
@@.
4
→
Rank
Calculate
ts_rank_cd().
5
Sort
Highest relevance first.
Raw SQL query +
Raw EXPLAIN response +
Full-text search
Linguistic retrieval pipeline
V2 transforms raw product fields into weighted searchable lexemes.
A
Highest weight
Product name
Name matches contribute most strongly to ranking.
B
High relevance
Brand + category
Strong metadata signals about what the product is.
C
Supporting metadata
Tags
Associated terms contribute to relevance with lower weight.
D
Lowest weight
Description
Description matches remain valid but contribute less to ranking.
Raw fields
Product text
→
Normalize
to_tsvector()
→
Representation
Lexemes
→
Match
@@ tsquery
→
Relevance
ts_rank_cd()
Benchmark
Version comparison
Compare architectural and runtime changes across implemented search versions.
| Version | Strategy | Results | App time | DB execution | Peak memory | Scan |
|---|---|---|---|---|---|---|
| V1 | LIKE / ILIKE | — | — | — | — | — |
| V2 | PostgreSQL FTS | — | — | — | — | — |
Results
Ranked products
Products are ordered by
ts_rank_cd() relevance.
0
shown
Explanation
What changed in V2?
V2 improves search semantics, not yet retrieval efficiency.
From substrings to lexemes
V1 compares raw character sequences. V2 converts text into normalized linguistic terms before matching.
to_tsvector('english', text)
Search-engine style queries
User input is converted with
websearch_to_tsquery(),
enabling normal words, quoted phrases,
OR and exclusions.
"gaming keyboard" -wireless
Relevance ranking
Matching products are no longer merely true or false. PostgreSQL calculates a relevance score.
ts_rank_cd(vector, query)
Still no search index
V2 intentionally constructs
tsvector values during the
query. A dedicated GIN index arrives in
V3.
V2 → linguistic, unindexed
Deep dive: why V2 may still be slow +
PostgreSQL now understands words better, but without a full-text index it may still inspect large portions of the product table.
Scan product
→
Construct tsvector
→
Evaluate @@
→
Rank match
This separation is intentional: V2 measures the semantic improvement, while V3 will measure the effect of adding an inverted GIN index.