Mastering PostgreSQL 9.6

Handling regular expressions

However, this is still not everything. Trigram indexes are even capable of speeding up simple regular expressions. The following example shows how this can be done:

test=# SELECT * FROM t_location WHERE name ~ '[A-C].*neu.*';      name      --------------  Bruckneudorf (1 row)

test=# explain SELECT * FROM t_location WHERE name ~ '[A-C].*neu.*';                                  QUERY PLAN                                  -----------------------------------------------------------------  Index Scan using idx_trgm on t_location  (cost=0.14..8.16     rows=1 width=13)    Index Cond: (name ~ '[A-C].*neu.*'::text) (2 rows)

PostgreSQL will inspect the regular expression and use the index to answer the question.

Internally, PostgreSQL can transform the regular expression into a graph and traverse ...