Problem Statement
Users search names with LIKE '%son'. Plain B-tree does not help this suffix pattern. Outline options to speed it up.
Explanation
A leading wildcard prevents a seek. Use specialized text search: trigram or n-gram indexes (e.g., Postgres pg_trgm with GIN/GIST) or full-text search for token queries. For pure suffix matches, maintain a reverse-indexed column (store reversed strings) and seek on 'nos%'.
If search can be constrained to prefix, change UX to anchor the left side (LIKE 'son%') which is index-friendly with B-tree.
Code Solution
SolutionRead Only
-- Postgres trigram CREATE EXTENSION IF NOT EXISTS pg_trgm; CREATE INDEX idx_trgm_name ON people USING GIN(name gin_trgm_ops); -- or store reversed_name and query WHERE reversed_name LIKE 'nos%';
Practice Sets
This question appears in the following practice sets:
