gQuest

Full-text search

Draft

LIKE and ILIKE match characters: '%indexing%' never finds "Indexes". Full-text search matches words. It reduces text to lexemes, so "indexing", "indexes" and "index" are the same word, understands search-box syntax, ranks the results, and has an index. It is built into PostgreSQL: no extension needed.

Same eight documents as the vectors lesson, without the embeddings.

The documents

CREATE TABLE documents (
  id SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  body TEXT NOT NULL
);
Loaded Database:full-text-search.database-init
INSERT INTO documents (title, body)
VALUES
  ('SQL indexes', 'Indexes help PostgreSQL find rows without reading the whole table.'),
  ('JOIN patterns', 'Joins combine rows from related tables into one result.'),
  ('Query tuning', 'Slow queries get faster with the right index and a better plan.'),
  ('React components', 'Components split a user interface into small reusable pieces.'),
  ('CSS grid layouts', 'Grid and flexbox position elements on a web page.'),
  ('Neural networks', 'Neural networks learn patterns from training data.'),
  ('Chatbots', 'Language models answer questions written in natural language.'),
  ('Vector search', 'Embeddings let a database find documents with a similar meaning.');

Text becomes lexemes

to_tsvector splits text into words, drops stop words like 'the' and 'without', and reduces each word to its stem: 'Indexes' becomes 'index', 'reading' becomes 'read'. The numbers are word positions.

Loaded Database:full-text-search.database-init
SELECT title, to_tsvector('english', title || ' ' || body) AS lexemes
FROM documents
LIMIT 3;
titlelexemes
SQL indexes'find':6 'help':4 'index':2,3 'postgresql':5 'read':9 'row':7 'sql':1 'tabl':12 'whole':11 'without':8
JOIN patterns'combin':4 'join':1,3 'one':10 'pattern':2 'relat':7 'result':11 'row':5 'tabl':8
Query tuning'better':13 'faster':6 'get':5 'index':10 'plan':14 'queri':1,4 'right':9 'slow':3 'tune':2
Limit  (cost=0.00..0.83 rows=3 width=64)
  ->  Seq Scan on documents  (cost=0.00..235.25 rows=850 width=64)

Match with @@

The query goes through the same stemming, so 'indexing' matches 'Indexes' and 'index'. ILIKE '%indexing%' would find nothing.

Loaded Database:full-text-search.database-init
SELECT title
FROM documents
WHERE to_tsvector('english', title || ' ' || body)
   @@ plainto_tsquery('english', 'indexing');
title
SQL indexes
Query tuning
Seq Scan on documents  (cost=0.00..237.38 rows=4 width=32)
  Filter: (to_tsvector('english'::regconfig, ((title || ' '::text) || body)) @@ '''index'''::tsquery)

Search like a search box

websearch_to_tsquery understands what users type in search boxes: "quoted phrases", OR, and -word to exclude. Here: documents about finding, but not the PostgreSQL one.

Loaded Database:full-text-search.database-init
SELECT title
FROM documents
WHERE to_tsvector('english', title || ' ' || body)
   @@ websearch_to_tsquery('english', 'find -postgresql');
title
Vector search
Seq Scan on documents  (cost=0.00..237.38 rows=4 width=32)
  Filter: (to_tsvector('english'::regconfig, ((title || ' '::text) || body)) @@ '''find'' & !''postgresql'''::tsquery)

Rank the results

ts_rank scores how well each row matches. With title words weighted A and body words B, a match in the title counts more.

Loaded Database:full-text-search.database-init
SELECT
  title,
  round(ts_rank(
    setweight(to_tsvector('english', title), 'A') ||
    setweight(to_tsvector('english', body), 'B'),
    query
  )::numeric, 3) AS rank
FROM documents, websearch_to_tsquery('english', 'search or find') AS query
WHERE (
  setweight(to_tsvector('english', title), 'A') ||
  setweight(to_tsvector('english', body), 'B')
) @@ query
ORDER BY rank DESC;
titlerank
Vector search0.426
SQL indexes0.122
Sort  (cost=456.24..456.26 rows=8 width=64)
  Sort Key: (round((ts_rank((setweight(to_tsvector('english'::regconfig, documents.title), 'A'::"char") || setweight(to_tsvector('english'::regconfig, documents.body), 'B'::"char")), '''search'' | ''find'''::tsquery))::numeric, 3)) DESC
  ->  Seq Scan on documents  (cost=0.00..456.12 rows=8 width=64)
        Filter: ((setweight(to_tsvector('english'::regconfig, title), 'A'::"char") || setweight(to_tsvector('english'::regconfig, body), 'B'::"char")) @@ '''search'' | ''find'''::tsquery)

Highlight the matches

ts_headline returns the text with matching words wrapped in <b> tags, ready for a results page.

Loaded Database:full-text-search.database-init
SELECT title, ts_headline('english', body, query) AS snippet
FROM documents, websearch_to_tsquery('english', 'rows') AS query
WHERE to_tsvector('english', title || ' ' || body) @@ query;
titlesnippet
SQL indexesIndexes help PostgreSQL find <b>rows</b> without reading the whole table.
JOIN patternsJoins combine <b>rows</b> from related tables into one result.
Seq Scan on documents  (cost=0.00..238.38 rows=4 width=64)
  Filter: (to_tsvector('english'::regconfig, ((title || ' '::text) || body)) @@ '''row'''::tsquery)

A search column and an index

The examples above call to_tsvector on every row, for every search. In a real table, store the tsvector in a generated column, and index it:

Loaded Database:full-text-search.indexed-database-init
-- Computed once on INSERT/UPDATE instead of on every search.
-- setweight labels title words A and body words B, so ts_rank can
-- rank a title match above a body match.
ALTER TABLE documents ADD COLUMN search tsvector
GENERATED ALWAYS AS (
  setweight(to_tsvector('english', title), 'A') ||
  setweight(to_tsvector('english', body), 'B')
) STORED;

-- GIN: an inverted index, lexeme -> rows that contain it.
-- Create it after a bulk load: building it once is faster than updating it per row.
CREATE INDEX documents_search_idx ON documents USING gin (search);

Store it and index it

5,008 documents, a stored search column, and a GIN index. The plan is a Bitmap Index Scan: the index returns the 62 rows containing both lexemes, and only those are read.

Loaded Database:full-text-search.indexed-database-init
SELECT title
FROM documents
WHERE search @@ websearch_to_tsquery('english', 'redis chatbots');
title
Redis chatbots note 63
Redis chatbots note 143
Redis chatbots note 223
Redis chatbots note 303
Redis chatbots note 383
Redis chatbots note 463
Redis chatbots note 543
Redis chatbots note 623
Redis chatbots note 703
Redis chatbots note 783
Redis chatbots note 863
Redis chatbots note 943
Redis chatbots note 1023
Redis chatbots note 1103
Redis chatbots note 1183
Redis chatbots note 1263
Redis chatbots note 1343
Redis chatbots note 1423
Redis chatbots note 1503
Redis chatbots note 1583
Redis chatbots note 1663
Redis chatbots note 1743
Redis chatbots note 1823
Redis chatbots note 1903
Redis chatbots note 1983
Redis chatbots note 2063
Redis chatbots note 2143
Redis chatbots note 2223
Redis chatbots note 2303
Redis chatbots note 2383
Redis chatbots note 2463
Redis chatbots note 2543
Redis chatbots note 2623
Redis chatbots note 2703
Redis chatbots note 2783
Redis chatbots note 2863
Redis chatbots note 2943
Redis chatbots note 3023
Redis chatbots note 3103
Redis chatbots note 3183
Redis chatbots note 3263
Redis chatbots note 3343
Redis chatbots note 3423
Redis chatbots note 3503
Redis chatbots note 3583
Redis chatbots note 3663
Redis chatbots note 3743
Redis chatbots note 3823
Redis chatbots note 3903
Redis chatbots note 3983
Redis chatbots note 4063
Redis chatbots note 4143
Redis chatbots note 4223
Redis chatbots note 4303
Redis chatbots note 4383
Redis chatbots note 4463
Redis chatbots note 4543
Redis chatbots note 4623
Redis chatbots note 4703
Redis chatbots note 4783
Redis chatbots note 4863
Redis chatbots note 4943
Bitmap Heap Scan on documents  (cost=21.84..128.86 rows=62 width=24)
  Recheck Cond: (search @@ '''redi'' & ''chatbot'''::tsquery)
  ->  Bitmap Index Scan on documents_search_idx  (cost=0.00..21.82 rows=62 width=0)
        Index Cond: (search @@ '''redi'' & ''chatbot'''::tsquery)

What it can't do

A typo, 'postgress', becomes the lexeme 'postgress', which no document contains. Full-text search matches words, not spellings or meanings.

Loaded Database:full-text-search.database-init
SELECT title
FROM documents
WHERE to_tsvector('english', title || ' ' || body)
   @@ plainto_tsquery('english', 'postgress');

No rows

Seq Scan on documents  (cost=0.00..237.38 rows=4 width=32)
  Filter: (to_tsvector('english'::regconfig, ((title || ' '::text) || body)) @@ '''postgress'''::tsquery)

Limits

Full-text search knows words and their forms, nothing more. It doesn't forgive typos: fuzzy search does. It doesn't know that "speed up" and "faster" mean the same thing: vector search does. The language matters too: 'english' stems English words; Postgres ships configurations for about 30 languages, and 'simple' for no stemming at all.

Text search in Postgres compares all the approaches and combines full-text with vector search.

Try Yourself

Loaded Database:full-text-search.database-init

The database for this lesson is already loaded. Write any query you want and run it directly in your browser.

Exercises

0 OF 2 DONE
1.Return the titles of the documents whose title or body matches the plain query 'table', ordered by title.
2.Use websearch_to_tsquery to find the documents that mention patterns but not networks. Return their titles.

What We Learned

  • tsvectora document as a sorted list of normalized words (lexemes).
  • tsquerythe search, built with plainto_tsquery or websearch_to_tsquery, matched with @@.
  • ts_rankscores matches; setweight makes title words count more.
  • ts_headlinehighlights the matching words in a snippet.
  • GIN indexan inverted index from each lexeme to the rows that contain it.