Alvaro Herrera <alvherre@commandprompt.com> writes:
> Kurapica wrote:
>> So I want to know whether pgsql's regex
>> processor can optimize regexes such as:
>> Nebraska|Nevada|North Carolina
>> to
>> N(e(braska|vada)|orth Carolina)
> Compared to the use of indexes to skip whole table scanning, this
> optimization is going to have very little impact. So don't worry about
> it.
Well, if you were able to extract a long enough common prefix to make an
index optimization possible/useful, then it would have some value. But
that seems unlikely. What I think would be considerably more
interesting is a conversion to an OR form:
state ~ '(^Nebraska)|(^Nevada)|(^North Carolina)'
to
state ~ '^Nebraska' OR state ~ '^Nevada' OR state ~ '^North Carolina'
which could be planned as three separate, very-selective indexscans ---
unlike the rewritten version proposed above.
But Oleg's suggestion of using pg_trgm or some other full-text searching
mechanism is probably at least as good, and it requires no new coding.
regards, tom lane