Alvaro Herrera <alvherre@xxxxxxxxxxxxxxxxx> 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
