Changes between Version 2 and Version 3 of AdvancedTopic

Timestamp:

05/26/26 16:02:12 (11 hours ago)

Author:

231550

Comment:

--

AdvancedTopic

@@ -70 +70 @@
 
 * Activates BEFORE INSERT or UPDATE so the **search_vector** is set before the row is written to disk
-* Only fires on UPDATE OF name — if other columns like **calories_per_100g** are updated the trigger is skipped, avoiding unnecessary recomputation
+* Only fires on UPDATE OF name if other columns like **calories_per_100g** are updated the trigger is skipped, avoiding unnecessary recomputation
 * COALESCE(NEW.name, '') handles null names gracefully by treating them as empty strings
 * NEW refers to the row being inserted or updated
@@ -88 +88 @@
     calories        INT,
     rank_score      REAL,
-    rank_normalized REAL
 ) AS $$
 DECLARE
@@ -101 +100 @@
         ts_headline(
             'english', f.name,v_tsquery,
-            'StartSel=<b>, StopSel=</b>, MaxWords=10, MinWords=1'
+            '!StartSel=<b>, !StopSel=</b>, !MaxWords=10, !MinWords=1'
         ) AS highlighted,
         c.name,
         f.calories_per_100g,
         ts_rank(f.search_vector, v_tsquery) AS rank_score,
-        ts_rank_cd(f.search_vector, v_tsquery) AS rank_normalized
     FROM Food f
     LEFT JOIN Category c ON f.category_id = c.category_id
@@ -115 +113 @@
 END;
 $$ LANGUAGE plpgsql;
+
+**Parameters:**
+* **p_query** - the search term entered by the user e.g. **'chicken breast'**
+* **p_limit** - how many results to return (default 20) for pagination
+* **p_offset** - how many results to skip (default 0) for pagination. For page 2 with 20 results per page, pass **p_offset = 20**
+
+**Key components:**
+* **plainto_tsquery('english', p_query)** - converts the user's raw input into a tsquery. It handles natural language input gracefully so users don't need to know any special syntax. For example 'chicken breast' becomes 'chicken' & 'breast'.
+
+* **f.search_vector @@ v_tsquery** - the match operator. Filters only foods whose search vector matches the query. This is where the GIN index is used.
+
+* **ts_rank(f.search_vector, v_tsquery)** - scores each result by relevance. Foods where the search terms appear more frequently get a higher score. Results are ordered by this score so the most relevant foods appear first.
+
+* **ts_headline(...)** - highlights matching terms in the food name by wrapping them in HTML tags. For example searching 'chicken' on 'Grilled Chicken Breast' returns 'Grilled <b>Chicken</b> Breast'. This is used by the frontend to bold matching words in search results.
+
+== Conclusion ==
+
+The FTS implementation in !VitaDiet provides a fast, accurate and user-friendly food search experience. When a user types a search query like "chicken breast", **plainto_tsquery** first converts it into a normalized **tsquery ('chicken' & 'breast')**, stripping out stop words and stemming each word to its root form. This **tsquery** is then matched against the precomputed **search_vector** column on the Food table using the **@@** operator, which uses the GIN index to find matching foods near-instantly without scanning the entire table. Each matching food is then scored by **ts_rank** based on how frequently and prominently the search terms appear in the food name, ensuring the most relevant results appear at the top. Finally **ts_headline** wraps the matching terms in HTML tags so the frontend can display them in bold, helping users immediately see why each result matched their search. The results are then paginated using LIMIT and OFFSET so only a manageable number of results are returned at a time.
+
+The trigger **trg_food_search_vector** ensures this entire pipeline stays accurate by automatically recomputing the **search_vector** whenever a food's name is inserted or updated, so the GIN index never becomes stale.
+
+**Advantages**
+* GIN index makes search fast regardless of table size
+* Stemming handles word variations automatically
+* Relevance ranking returns the most useful results first
+* Highlighting improves the user experience
+* **plainto_tsquery** handles natural language input without requiring special syntax from users
+
+**Disadvantages**
+* **search_vector** column adds storage overhead to the **Food** table
+* The trigger adds a small overhead to every INSERT and UPDATE on **Food**
+* FTS is language-specific - the **'english'** configuration may not handle non-English food names perfectly.