Mastering PostgreSQL 9.6

Speed 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                                ----------------------------------------------------------------- ...