PostgreSQL Tutorial: Tuning LIKE filters using indexes

Summary: In this tutorial, you will learn how to tune LIKE filters using indexes in PostgreSQL.

Table of Contents

Introduction

The SQL LIKE operator very often causes unexpected performance behavior because some search terms prevent efficient index usage. That means that there are search terms that can be indexed very well, but others can not. It is the position of the wild card characters that makes all the difference.

Example

The following example uses the % wild card in the middle of the search term:

SELECT first_name, last_name, date_of_birth
  FROM employees
 WHERE UPPER(last_name) LIKE 'WIN%D'

                                QUERY PLAN
----------------------------------------------------------------------------
Index Scan using emp_up_name on employees  (cost=0.01..8.29 rows=1 width=17)
   Index Cond: (upper((last_name)::text) ~>=~ 'WIN'::text)
           AND (upper((last_name)::text) ~<~  'WIO'::text)
       Filter: (upper((last_name)::text) ~~ 'WIN%D'::text)

LIKE filters can only use the characters before the first wild card during tree traversal. The remaining characters are just filter predicates that do not narrow the scanned index range. A single LIKE expression can therefore contain two predicate types: (1) the part before the first wild card as an access predicate; (2) the other characters as a filter predicate.

Caution

The LIKE operator works on a character-by-character basis while collations can treat multiple characters as a single sorting item. Thus some collations prevent using indexes for LIKE.

How it works …

The more selective the prefix before the first wild card is, the smaller the scanned index range becomes. That, in turn, makes the index lookup faster. The following figure illustrates this relationship using three different LIKE expressions. All three select the same row, but the scanned index range—and thus the performance—is very different.

Various LIKE Searches

The first expression has two characters before the wild card. They limit the scanned index range to 18 rows. Only one of them matches the entire LIKE expression—the other 17 are fetched but discarded. The second expression has a longer prefix that narrows the scanned index range down to two rows. With this expression, the database just reads one extra row that is not relevant for the result. The last expression does not have a filter predicate at all: the database just reads the entry that matches the entire LIKE expression.

Important

Only the part before the first wild card serves as an access predicate.

The remaining characters do not narrow the scanned index range—non-matching entries are just left out of the result.

The opposite case is also possible: a LIKE expression that starts with a wild card. Such a LIKE expression cannot serve as an access predicate. The database has to scan the entire table if there are no other conditions that provide access predicates.

Tip

Avoid LIKE expressions with leading wildcards (e.g., '%TERM').

LIKE condition with bind parameter

The position of the wild card characters affects index usage—at least in theory. In reality the optimizer creates a generic execution plan when the search term is supplied via bind parameters. In that case, the optimizer has to guess whether or not the majority of executions will have a leading wild card.

PostgreSQL assumes there is a leading wild card when optimizing a LIKE condition with bind parameter. PostgreSQL just does not use an index in that case. The only way to get an index access for a LIKE expression is to make the actual search term visible to the optimizer. If you do not use a bind parameter but put the search term directly into the SQL statement, you must take other precautions against SQL injection attacks!

Even if the database optimizes the execution plan for a leading wild card, it can still deliver insufficient performance. You can use another part of the where clause to access the data efficiently in that case. If there is no other access path, you might use the full-text index as the solution.

PostgreSQL offers the @@ operator to implement full-text searches. See “Full Text Search” in the PostgreSQL documentation.

See more

PostgreSQL Optimization