Thread: ~* OR LIKE?

I have a query that I could run a number of ways:
Like this:

  index_word.word ~*'radio.*'
  OR
  index_word.word ~*'spry.*'

Or I could use LIKE and the "%" comparison

If I toss out the need for comparisions I could just use: IN
('radio','spry')

What's the relative performance for:  "IN" "LIKE" or "~*"? I'm trying to
decide if the performance loss of doing extra string compares is worth
getting slighly more relevant results.

Matt Friedman

"Matt Friedman" <matt@daart.ca> writes:
> What's the relative performance for:  "IN" "LIKE" or "~*"?

If you don't have an index on the target column, or if the pattern is
not constant (eg you took it from another table), then these will all
reduce to sequential-scan-and-examine-every-tuple.  Simple equality
comparisons will take a little less CPU time than pattern matches, but
the tuple retrieval costs are going to dominate everything anyway.
In short: it hardly matters unless you can use an indexscan.

IN ('foo','bar','baz') reduces to x = 'foo' OR x = 'bar' OR x = 'baz'.
This can be done by a series of indexscans (one index probe per OR
clause), so it's pretty quick for small numbers of alternatives.

Case-sensitive LIKE and ~ can use indexscans if (at least part of) the
constant pattern is left-anchored.  For example, x LIKE 'foo%bar' can
only match values beginning with 'foo', so an index scan over the range
of such values can be used.  In regexp notation this'd be a pattern
anchored left with ^.

The above breaks down for case-insensitive matching, and it also breaks
down in non-C locales, where string sort ordering may not match the
semantics of pattern prefixes closely enough.  So in those cases you are
back to sequential scan.

            regards, tom lane