Speeding up LIKE queries

LIKE queries definitely cause some of the worst performance problems faced by people around the globe these days. In most database systems, LIKE is pretty slow and requires a sequential scan. In addition to that, end users quickly figure out that a fuzzy search will, in many cases, return better results than precise queries. A single type of LIKE query on a large table can, therefore, often cripple the performance of an entire database server if it is called often enough.

Fortunately, PostgreSQL offers a solution to the problem and the solution happens to be installed already:

test=# explain SELECT * FROM t_location WHERE name LIKE '%neusi%'; 
                               QUERY PLAN 
----------------------------------------------------------------- 
 Bitmap Heap Scan on t_location 
   (cost=4.33..19.05 rows=24 width=13) 
   Recheck Cond: (name ~~ '%neusi%'::text) 
   -> Bitmap Index Scan on idx_trgm (cost=0.00..4.32 rows=24 width=0) 
         Index Cond: (name ~~ '%neusi%'::text) 
(4 rows) 

The trigram index deployed in the previous section is also suitable to speed up LIKE. Note that the % symbols can be used at any point in the search string. This is a major advantage over standard b-trees, which just happen to speed up wildcards at the end of the query.