Exploring the pg_trgm extension
Now let’s go back to Chapter 13, Indexes and Performance Optimization, in the Indexes section. When we talked about indexing, we learned how to make our queries faster through the use of indexes. However, B-tree indexes do not index all types of operations. Now let’s consider textual data types (char, varchar, or text). Now, we will see that the B-tree, using the varchar_pattern_ops operator class, is able to index text queries for sentences that begin with search%, but cannot index text queries for sentences that end in %search or contain %search%:
- Before diving into our example, let’s
set enable_seqscantooffin order to force PostgreSQL to use an index if it exists. We need to do this because, in our example case, PostgreSQL would always use sequential scanning by default, because we only have a few records in our table and because all data that is present in the table is stored on a single page:forumdb=...