PostgreSQL Full-Text Search Functions

Calling `to_tsvector` inside a WHERE clause forces PostgreSQL to re-parse and normalize every row on every query, making full-table scans the only option. Store the vector in a generated column and index it instead.

✓ Instead: -- Bad: recomputed on every query SELECT * FROM articles WHERE to_tsvector('english', body) @@ plainto_tsquery('search'); -- Good: generated column + GIN index ALTER TABLE articles ADD COLUMN tsv tsvector GENERATED ALWAYS AS (to_tsvector('english', coalesce(body,''))) STORED; CREATE INDEX articles_tsv_gin ON articles USING GIN(tsv); SELECT * FROM articles WHERE tsv @@ plainto_tsquery('english', 'search');

`to_tsquery` requires strict tsquery syntax — a bare space or unsupported character raises an error at runtime. Never pass unvalidated user input to it. Use `websearch_to_tsquery` or `plainto_tsquery` for user-facing search.

✓ Instead: -- Bad: user types 'dogs running' → ERROR: syntax error in tsquery SELECT * FROM articles WHERE tsv @@ to_tsquery('english', user_input); -- Good: safe for arbitrary user input SELECT * FROM articles WHERE tsv @@ websearch_to_tsquery('english', user_input);

`plainto_tsquery` always treats all words as AND — users cannot express 'cat OR dog' or exact phrases. Offer `websearch_to_tsquery` for search boxes where users expect Google-style syntax.

✓ Instead: -- Bad: user types 'cat OR dog' → treated as AND, finds nothing with all three words SELECT * FROM docs WHERE tsv @@ plainto_tsquery('english', 'cat OR dog'); -- Parsed as: 'cat' & 'or' & 'dog' (or just 'cat' & 'dog' if 'or' is stop word) -- Good: respects OR keyword SELECT * FROM docs WHERE tsv @@ websearch_to_tsquery('english', 'cat OR dog');

`to_tsquery` raises an error on plain user input (spaces, special characters). For any search box that accepts free-form text, `websearch_to_tsquery` is the safe, correct choice — it never throws on unexpected input.

✓ Instead: -- Bad: crashes if user types 'quick brown fox' (space not valid in to_tsquery) SELECT * FROM articles WHERE tsv @@ to_tsquery('english', user_input); -- Good: handles any user input gracefully SELECT * FROM articles WHERE tsv @@ websearch_to_tsquery('english', user_input);

`ts_rank` does not filter rows — it only scores them. Without a `WHERE tsv @@ query` clause, PostgreSQL computes a rank for every row in the table (including non-matching rows), then sorts all of them. Always pair `ts_rank` with a `@@` filter.

✓ Instead: -- Bad: scores and sorts every row in the table SELECT title, ts_rank(tsv, to_tsquery('english', 'database')) AS rank FROM articles ORDER BY rank DESC; -- Good: filter first, then rank SELECT title, ts_rank(tsv, q) AS rank FROM articles, to_tsquery('english', 'database') AS q WHERE tsv @@ q ORDER BY rank DESC LIMIT 20;

`ts_headline` is CPU-intensive — it re-parses and tokenises the raw document text. Applying it to thousands of rows returned before a LIMIT multiplies the cost. Always paginate or LIMIT before calling `ts_headline`.

✓ Instead: -- Bad: ts_headline runs on every matching row before LIMIT SELECT ts_headline('english', body, q) FROM articles, ... WHERE tsv @@ q; -- Good: limit first using a CTE or subquery, then generate headlines WITH ranked AS ( SELECT id, ts_rank(tsv, q) AS rank FROM articles, websearch_to_tsquery('english', $1) AS q WHERE tsv @@ q ORDER BY rank DESC LIMIT 10 ) SELECT a.title, ts_headline('english', a.body, websearch_to_tsquery('english', $1), 'StartSel=<mark>, StopSel=</mark>, MaxFragments=2') FROM articles a JOIN ranked r ON a.id = r.id ORDER BY r.rank DESC;

Building `setweight(...) || setweight(...)` inside the WHERE clause forces every concatenation to happen at query time on every row, preventing GIN index use. Store the pre-computed concatenation in a generated column.

✓ Instead: -- Bad: concatenation at query time — full table scan, no index benefit SELECT * FROM articles WHERE (setweight(to_tsvector('english', title), 'A') || setweight(to_tsvector('english', body), 'B')) @@ plainto_tsquery('english', $1); -- Good: store and index the concatenated vector ALTER TABLE articles ADD COLUMN tsv tsvector GENERATED ALWAYS AS ( setweight(to_tsvector('english', coalesce(title,'')), 'A') || setweight(to_tsvector('english', coalesce(body,'')), 'B') ) STORED; CREATE INDEX articles_tsv_gin ON articles USING GIN(tsv);

Calling `setweight(tsvector1 || tsvector2, 'A')` applies the weight to the already-concatenated result, overwriting any individual weights. Apply `setweight` to each part before concatenating.

✓ Instead: -- Bad: sets 'A' on everything — individual weights lost SELECT setweight(to_tsvector('english', title) || to_tsvector('english', body), 'A'); -- Good: weight each part independently, then concatenate SELECT setweight(to_tsvector('english', title), 'A') || setweight(to_tsvector('english', body), 'B');

`plainto_tsquery('english', 'machine learning')` produces `'machin' & 'learn'` — it matches documents with both words anywhere, not necessarily adjacent. Use `phraseto_tsquery` when the exact sequence of words is required.

✓ Instead: -- Bad: matches docs with 'machine' and 'learning' anywhere, in any order SELECT * FROM docs WHERE tsv @@ plainto_tsquery('english', 'machine learning'); -- Good: requires adjacent sequence SELECT * FROM docs WHERE tsv @@ phraseto_tsquery('english', 'machine learning');