Mastering PostgreSQL 12 - Third Edition

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