The Art of PostgreSQL/

...

/

Trigram Indexing

Trigram Indexing

Learn about indexing algorithms available in trigrams.

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:

Press + to interact
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:

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

Here’s the query ...