Re: Case Insensitive searches

On Monday 04 August 2008 11:09, Frank Bax wrote:
> 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

Frank,

Thanks for the response. Actually, from within the applicaion, we use ~* and
it is anchored with whatever they've typed in the widget as search criteria.

Anyway, thanks for the helpful response...
--
Terry Lee Tucker
Turbo's IT Manager
Turbo, division of Ozburn-Hessey Logistics
2251 Jesse Jewell Pkwy NE
Gainesville, GA 30501
Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987
terry(at)turbocorp(dot)com
www.turbocorp.com