Re: sequential scans and the like operator

Dave Trombley <dtrom(at)bumba(dot)net> writes:
> Roderick A. Anderson wrote:
>> There is a discussion going on on the sql-ledger mailing list concerning
>> whether indexes will provide any performance improvements. The one that
>> caught my eye was whether using LIKE in a statement would force a
>> sequential scan.
>>
> You can always check exaclty what's being done in your queries by
> using the EXPLAIN command.

Alternatively, search the pgsql mailing list archives; LIKE performance
has been discussed more times than I care to think about. The present
state of play, I believe, is:

* LIKE and regexp match WHERE clauses are potentially indexable if the
pattern specifies a fixed prefix of one or more characters. The longer
the fixed prefix, the more selective the index condition (and hence the
greater the probability the planner will choose to use it).

As examples:

foo LIKE 'abc%bar' indexable (prefix is abc)
foo LIKE '_abc%bar' not indexable (first character not fixed)
foo ~ 'abc' not indexable (pattern not anchored left)
foo ~ '^abc' indexable (prefix is abc)
foo ILIKE 'abc%' not indexable (1st char could be A or a)

* If Postgres was compiled with LOCALE support and is using a non-C
locale, <blech>LIKE indexing is disabled</blech> because the sort order
of the index may not agree with what's needed to perform LIKE searches.

This last point is rather nasty since non-C locales are rapidly becoming
the usual default, even in the USA. Linuxen tend to default to en_US
locale, for example.

regards, tom lane