Обсуждение: Case Insensitive searches
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
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
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
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
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
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))
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>
Frank,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
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
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