Searching text with LIKE '%word%' is slow and crude: it cannot use a normal index, ignores word boundaries, and treats 'running' and 'run' as unrelated. PostgreSQL includes a real full-text search engine that understands language — stemming words to their roots, ignoring noise words, and ranking results by relevance — all inside the database, with no external search server required for many use cases.
Full-text search revolves around two data types. A tsvector is a processed, normalised representation of a document: a sorted list of lexemes (word roots) with their positions. A tsquery is a processed search expression of lexemes combined with operators. The match operator @@ tests whether a tsvector satisfies a tsquery, and a GIN index makes that test fast over millions of documents.
The power comes from text-processing functions and configurations. to_tsvector parses and stems text according to a language configuration; to_tsquery and its friendlier variants parse search input; and ranking functions order matches by relevance. Together they turn PostgreSQL into a capable search engine for product catalogues, articles, and documents.
Analogy🏏Cricket
🏏 Think of it like cricket: Just as a selection decision can depend on a derived benchmark — 'pick batters whose average exceeds the squad's average', which itself must first be computed — a subquery computes an inner result that the outer query then uses. The insight is that some questions are inherently two-stage: you must establish the benchmark before you can judge against it, and composing queries is how SQL expresses that dependency. Watch the selector actually do it: first he tallies every batter's runs and computes the squad average — that inner computation stands alone, needing nothing from the final decision — and only then does he walk the list judging each player against the number he just derived. That independence is what makes it an uncorrelated subquery: the database can compute the benchmark once, keep it, and reuse it for every row, exactly as the selector does not recompute the squad average per player. The composition also comes in shapes: a benchmark producing one number slots in where a value goes (a scalar subquery in WHERE), while a computed shortlist of qualifying players is itself a table the outer query can select from — a subquery in FROM. Two-stage question, two nested queries, dependency flowing inward-out.
🏏 Showing the Cricket analogy — a Cricket version isn’t available for this concept yet.