Support indexing of regular-expression searches in contrib/pg_trgm.
This works by extracting trigrams from the given regular expression,
in generally the same spirit as the previously-existing support for
LIKE searches, though of course the details are far more complicated.
Currently, only GIN indexes are supported. We might be able to make
it work with GiST indexes later.
The implementation includes adding API functions to backend/regex/
to provide a view of the search NFA created from a regular expression.
These functions are meant to be generic enough to be supportable in
a standalone version of the regex library, should that ever happen.
Alexander Korotkov, reviewed by Heikki Linnakangas and Tom Lane

One day later Tom Lane added support for the same operations using GiST indexes (original patch was working only with GIN).

Ever since 9.1 we knew it is possible to speed up substring searches. Now – the same thing can be used to speed up regexps.

3 comments

A little note about regexp search vs tsearch2. I would prefere to not compare them, because they are different features for different use cases. Imagine you’re searching in logs or in some scientific data like DNA/protein sequences or chemical formulas. You probably could write a special parsers and special dictionaries for tsearch2. But you might as well use regexp search without such preliminary work.