Persisted vector
Product search vectors already exist before a search begins.
Indexed Full-Text Retrieval
Same search semantics. Better retrieval architecture.
V3 keeps the linguistic retrieval introduced in V2, but moves document-vector construction to the write path and adds a PostgreSQL GIN inverted index for candidate lookup.
Try
GIN
Indexed linguistic retrieval
Search to inspect whether PostgreSQL chooses the GIN index, how many rows are retrieved, and how V3 compares with V2.
Trigger maintenance
PostgreSQL rebuilds the vector when relevant product fields change.
GIN retrieval
An inverted index helps PostgreSQL locate matching lexemes efficiently.
Running indexed full-text search
Querying PostgreSQL and inspecting the execution
plan.
!
Search failed
Something went wrong.
QUERY
Search report
Query
—
INDEX
Waiting for plan
GIN index status
An index existing does not guarantee the planner will use it.
GIN inverted index built over the persisted product search vector.
Determined from the actual PostgreSQL execution plan.
Bitmap, index-assisted or sequential retrieval.
Rows produced by the selected retrieval node.
POSTGRESQL
Waiting
Execution report
Human-readable interpretation of
EXPLAIN ANALYZE.
DB
Primary retrieval operation
—
Run a search to inspect PostgreSQL's selected execution strategy.
Time spent constructing the query plan.
PostgreSQL executor duration.
Rows rejected by filters after retrieval.
Pages found in PostgreSQL shared buffers.
Pages read into shared buffers.
Startup time for the selected node.
Total time spent inside the selected node.
EXECUTION FLOW
Expected V3 retrieval path
1
→
Parse query
websearch_to_tsquery()
2
→
GIN lookup
Bitmap Index Scan
3
→
Fetch rows
Bitmap Heap Scan
4
→
Rank
ts_rank_cd()
5
Sort
relevance DESC
Raw SQL query +
Raw EXPLAIN response +
WRITE PATH
Trigger-maintained search vector
V3 moves vector construction out of search requests and into product writes.
Product change
INSERT / UPDATE
→
PostgreSQL
BEFORE trigger
→
Function
update_product_search_vector()
→
Persisted
search_vector
→
Index
GIN maintained
BEFORE INSERT
New products receive a search vector before the row is stored.
BEFORE UPDATE
Search vectors are rebuilt when searchable product fields change.
UPDATE OF
Name, brand, category, tags and description trigger vector maintenance.
Search path
Triggers do not run during normal search requests.
BENCHMARK
V2 vs V3
Same FTS semantics. Different retrieval architecture.
| Version | Strategy | Results | App time | DB execution | Peak memory | Primary scan | Index |
|---|---|---|---|---|---|---|---|
| V2 | Runtime FTS vector | — | — | — | — | Sequential Scan | None |
| V3 | Persisted vector + GIN | — | — | — | — | — | — |
RESULTS
Ranked products
Matching products remain ordered using
ts_rank_cd().
0
shown
EXPLANATION
What changed in V3?
Persisted `tsvector`
V3 no longer rebuilds the complete product document vector during every search.
products.search_vector
Trigger maintenance
PostgreSQL keeps the stored vector synchronized with searchable fields.
BEFORE INSERT OR UPDATE
GIN inverted index
PostgreSQL can locate matching lexemes without beginning from every product row.
USING GIN(search_vector)
Ranking remains separate
GIN finds candidates. PostgreSQL still calculates relevance and sorts matches.
ts_rank_cd()
Why PostgreSQL may still choose a Sequential Scan +
An index does not force a particular execution plan. PostgreSQL estimates the cost of available strategies and may prefer a sequential scan when a query matches a very large percentage of the table.
Highly selective
120 / 100,000
GIN is likely valuable.
Medium selectivity
8,000 / 100,000
Planner evaluates tradeoffs.
Very broad
80,000 / 100,000
Sequential Scan may be cheaper.