Search⌘ K
Home
Courses
The Art of PostgreSQL

Trigram Indexing

Explore how to create and utilize trigram indexes in PostgreSQL to speed up fuzzy searches and typo corrections. Understand query plans that demonstrate efficient index scans and learn to execute practical examples to optimize search performance in your applications.

We'll cover the following...

Of course, if we want to be able to use those suggestions directly from our user input facility, it needs to be as fast as possible. The usual answer to speed up specific SQL queries is indexing.

Indexing algorithms using trigrams

The pg_trgm extension comes with specific indexing algorithms to take care of searching for similarity. Moreover, it covers searching for regular expressions too. Here’s how to build our index:

PostgreSQL
create index on lastfm.track using gist(title gist_trgm_ops);

Using explain

We can explain our previous queries and see that they now use our new index:

PostgreSQL
explain (analyze, costs off)
select artist, title
  from lastfm.track
 where title ~* 'peace';

Here’s the query ...