Karsten Hilbert <Karsten.Hilbert@gmx.net> writes:
>> Regexes are optimized the same way as equivalent LIKE expressions. In
>> particular, the pattern has to be left-anchored to consider using it
>> with an index. In LIKE that means no wildcard at the start of the
>> pattern, in regex it means there has to be a ^.
> What about "^.*oobar" in a regex ? I mean, it seems impossible
> to use an index on that, right ?
Right. You need ^ immediately followed by some constant text. The
planner extracts the "fixed prefix" of the pattern to use with the index.
regards, tom lane