Thread: Case Insensitive searches

Case Insensitive searches

From
"Mike Gould"
Date:
In the application that we are working on, all data searches must be case insensitive. 
 
Select * from test where column1 = 'a' and Select * from test where column1 = 'A' should always be the same and use the index if column1 is indexed.  In order to do this am I going to be required to use the lower() on all selects in order to make sure that they are case insensitive?  In some db's if you use a lower() or upr() it will always do a table scan instead of using a index


Best Regards,

Michael Gould, Manager Information Technology
All Coast Intermodal Services, Inc.
First Coast Intermodal Services, Inc.
First Coast Logistical Services, LLC.
904-226-0978
 
 

Re: Case Insensitive searches

From
"Richard Broersma"
Date:
On Mon, Aug 4, 2008 at 6:54 AM, Mike Gould <mgould@allcoast.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.


-- 
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug


Re: Case Insensitive searches

From
Terry Lee Tucker
Date:
On Monday 04 August 2008 10:05, Richard Broersma wrote:
> On Mon, Aug 4, 2008 at 6:54 AM, Mike Gould <mgould@allcoast.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?
-- 
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@turbocorp.com
www.turbocorp.com


Re: Case Insensitive searches

From
Frank Bax
Date:
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@allcoast.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
scanis 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


Re: Case Insensitive searches

From
Terry Lee Tucker
Date:
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@allcoast.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@turbocorp.com
www.turbocorp.com


Re: Case Insensitive searches

From
"Rafael Domiciano"
Date:
I have read the article... tnks, very helpful.

But, can I create a index using function like "substring"? I would like to create something like this:

CREATE INDEX indtest_01 ON table_01
((SUBSTRING(month_year, 3, 4) || SUBSTRING(month_year, 1, 2))

2008/8/4 Terry Lee Tucker <terry@chosen-ones.org>
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@allcoast.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@turbocorp.com
www.turbocorp.com

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: Case Insensitive searches

From
Terry Lee Tucker
Date:
On Wednesday 06 August 2008 07:59, Rafael Domiciano wrote:
> I have read the article... tnks, very helpful.
>
> But, can I create a index using function like "substring"? I would like to
> create something like this:

Actually, Richard Broersma is the one who commented on that approach. I have 
never done this but I have read about it. I'm sure it can be done.

>
> CREATE INDEX indtest_01 ON table_01
> ((SUBSTRING(month_year, 3, 4) || SUBSTRING(month_year, 1, 2))
>
> 2008/8/4 Terry Lee Tucker <terry@chosen-ones.org>
>
> > 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@allcoast.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@turbocorp.com
> > www.turbocorp.com
> >
> > --
> > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-sql

-- 
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@turbocorp.com
www.turbocorp.com