Re: Case Insensitive searches

Terry Lee Tucker wrote:
> On Monday 04 August 2008 10:05, Richard Broersma wrote:
>> On Mon, Aug 4, 2008 at 6:54 AM, Mike Gould <mgould(at)allcoast(dot)net> wrote:
>>> In some db's if you
>>> use a lower() or upr() it will always do a table scan instead of using a
>>> index
>> True, this would also happen in PostgreSQL. However, you can overcome
>> this by creating a "functional" index:
>>
>> http://www.postgresql.org/docs/8.3/interactive/indexes-expressional.html
>>
>> This way all expression using where lower( column ) = 'a'. will always
>> use an index scan.
>>
>
> What about using the operator, ~* ?
>
> Does that cause a table scan as well?

Whether or not any query uses an index scan or seq scan depends on many
factors and is not always easily predictable.

Richard's statement about "will always use an index scan" is not
universally true. If the table is very small; a index scan is NOT used.
Table statistics could also indicate a seq scan is more efficient
(suppose 99% of rows had column='a').

The ~* operator is very likely to scan the entire table because it will
look for 'A' anywhere in the column (and will therefore match 'Joanne';
and I doubt that there is special code to handle case where length of
argument is exactly the same as column. However; ~* '^a' which anchors
search to first character is perhaps more likely to use an index scan.

Frank