Regular expressions

[Chris Angelico]

On Wed, Nov 4, 2015 at 2:12 PM, Tim Chase <python.list at tim.thechases.com> wrote:
> It's not as helpful as one might hope because you're stuck using a
> fixed regexp rather than an arbitrary regexp, but if you have a
> particular regexp you search for frequently, you can index it.
> Otherwise, you'd be doing full table-scans (or at least a full scan
> of whatever subset the active non-regexp'ed index yields) which can
> be pretty killer on performance.

If the regex anchors the start of the string, you can generally use an
index to save at least some effort. Otherwise, you're relying on some
kind of alternate indexing style, such as:

http://www.postgresql.org/docs/current/static/pgtrgm.html

which specifically mentions regex searches as being indexable.

Some more info, including 'explain' results:

http://www.depesz.com/2013/04/10/waiting-for-9-3-support-indexing-of-regular-expression-searches-in-contribpg_trgm/

But this kind of thing isn't widely supported across databases.

ChrisA