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 ...
