Re: ~* OR LIKE?

"Matt Friedman" <matt(at)daart(dot)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