Why full-text search matters in PostgreSQL - Challenge Your Understanding

Challenge - 5 Problems

❓ query_result

intermediate

Basic full-text search query output

Given a table articles with a content column, what is the output of this query?

SELECT id FROM articles WHERE to_tsvector('english', content) @@ to_tsquery('english', 'database & search');

Assume the table has:
- id=1, content='Database systems are essential for search functionality.'
- id=2, content='Database search engines use full-text search techniques.'
- id=3, content='Databases store data efficiently.'

A [1, 2]

B [1, 3]

C [2, 3]

D [1, 2, 3]

💡 Hint

Look for rows where both 'database' and 'search' appear in the content.

The query uses full-text search to find rows containing both 'database' and 'search'. Rows 1 and 2 contain both words, row 3 does not contain 'search'.

🧠 Conceptual

intermediate

Why use full-text search over LIKE?

Which of the following is the main advantage of using full-text search instead of the LIKE operator for searching text in PostgreSQL?

A LIKE is faster for large text searches.

B Full-text search only works with exact word matches.

C Full-text search can find words regardless of their order and supports stemming.

D LIKE supports ranking results by relevance automatically.

💡 Hint

Think about how full-text search understands word forms and order.

Full-text search supports stemming (finding word variations) and ignores word order, making it more flexible and powerful than LIKE, which only matches exact substrings.

📝 Syntax

advanced

Correct syntax for creating a full-text index

Which option correctly creates a full-text search index on the content column of the documents table using the English dictionary?

A CREATE INDEX idx_ft_content ON documents USING btree(to_tsvector('english', content));

B CREATE INDEX idx_ft_content ON documents USING gin(to_tsvector('english', content));

C CREATE INDEX idx_ft_content ON documents USING hash(to_tsvector(content));

D CREATE INDEX idx_ft_content ON documents USING gin(to_tsquery('english', content));

💡 Hint

Full-text indexes use GIN and tsvector, not tsquery or btree.

The correct syntax uses GIN index on the tsvector generated from the content column with the English dictionary. Btree and hash are not suitable for full-text search indexes.

❓ optimization

advanced

Improving full-text search performance

You notice your full-text search queries on a large articles table are slow. Which change will most improve query speed?

A Create a GIN index on to_tsvector('english', content).

B Switch to using LIKE '%word%' queries instead.

C Remove the full-text index to reduce overhead.

D Use a sequential scan instead of an index scan.

💡 Hint

Indexes help speed up searches on large tables.

Creating a GIN index on the tsvector column allows PostgreSQL to quickly find matching rows, greatly improving full-text search performance.

🔧 Debug

expert

Why does this full-text search query return no results?

Given the query:

SELECT * FROM posts WHERE to_tsvector('english', body) @@ to_tsquery('english', 'running & fast');

and the body contains 'Running fast is important', why does this query return no rows?

A The language 'english' is not supported in to_tsquery.

B The to_tsvector function does not tokenize the text properly.

C The & operator means OR, so it should return rows with either word.

D The query uses 'running' but the text contains 'running', and to_tsquery does not stem words automatically.

💡 Hint

Check how to_tsquery handles word forms and stemming.

to_tsquery does not stem words automatically, so 'running' does not match 'run'. The correct query should use 'run:*' to match word prefixes or use plainto_tsquery.