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 this, 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 this 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 that we deployed in the previous section is also suitable for speeding 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.