Changes between Initial Version and Version 1 of AdvancedTopic

Timestamp:

05/26/26 13:19:28 (12 hours ago)

Author:

231550

Comment:

--

AdvancedTopic

@@ +1 @@
+= Full Text Search in VitaDiet =
+
+== Introduciton == 
+
+Full Text Search (FTS) is a technique for searching natural language text in a database. Unlike a simple LIKE search which matches exact character patterns, FTS understands language — it normalizes words to their root form, ignores common words like "the" and "a", and ranks results by relevance.
+
+For example a LIKE search for '%chicken%' will:
+*Scan every row in the table sequentially
+*Only match the exact string "chicken"
+*Return results in no particular order
+*Become slower as the table grows
+
+A FTS search for 'chicken' will:
+*Use a GIN index to find matches instantly
+*Match "chicken", "chickens", "chickened"
+*Rank results by relevance
+*Perform consistently regardless of table size
+
+For VitaDiet, FTS is used to power the food search feature, allowing users to search 350,000+ foods quickly and accurately.
+
+== How PostgreSQL FTS Works ==
+
+PostgreSQL FTS is built around two core data types:
+
+tsvector — a preprocessed representation of a text document. It stores the normalized lexemes (root forms of words) of a text column. For example:
+SELECT to_tsvector('english', 'Grilled Chicken Breast');
+Result: 'breast':3 'chicken':2 'grill':1
+*"Grilled" becomes "grill" (stemmed to root form)
+*Numbers indicate the position of each word
+*Stop words like "and", "the" are removed
+
+tsquery — a processed search query. It converts user input into a format that can be matched against a tsvector:
+SELECT plainto_tsquery('english', 'grilled chicken');
+Result: 'grill' & 'chicken'
+The @@ operator checks if a tsquery matches a tsvector:
+SELECT to_tsvector('english', 'Grilled Chicken Breast') 
+    @@ plainto_tsquery('english', 'grilled chicken');
+Result: true