"Mitch Vincent" <mitch@venux.net> writes:
> I'm wondering if LIKE uses regex internally.
No, it's just a pretty brain-dead scanner. You can find the code in
backend/utils/adt/like.c if you care.
Perhaps it'd make sense to translate the LIKE pattern into an equivalent
regex and then use the regex code, but no one's done it.
> I'm trying to find the
> quickest / fastest way to search for any occurance of a given string in a
> stored field.
> query: select * from applicants where firstname LIKE '%mitch%';
> query: select * from applicants where firstname ~ lower('mitch');
I think the actual operator processing is going to be swamped by
tuple-fetching overhead, so there's not going to be much difference.
What you ought to be thinking about is how you could replace the
brute-force scan with an index lookup. Possibly you could adapt the
ideas in contrib/fulltextindex.
regards, tom lane