X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 7D0189DC9E5 for ; Thu, 23 Feb 2006 08:52:36 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 35484-07 for ; Thu, 23 Feb 2006 08:52:39 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from zproxy.gmail.com (zproxy.gmail.com [64.233.162.206]) by postgresql.org (Postfix) with ESMTP id 195BF9DC9B2 for ; Thu, 23 Feb 2006 08:52:32 -0400 (AST) Received: by zproxy.gmail.com with SMTP id j2so46294nzf for ; Thu, 23 Feb 2006 04:52:35 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=rDsHlYjV/Kxp88A8Lhs++ebsLhsqwhH/Tk/vkP+N26SFcx9NQdelrHYzt//3B76VslUbnADSuLYiSX04f9k5Xv4Qppkkc5fE24vdtLtF2C25M/DH+krgvnB9c0caZBTeCoAUOEX6oQT51zjPF0z/nk8+QI6+3LSMQ2UXNQNNgb8= Received: by 10.37.18.35 with SMTP id v35mr2293073nzi; Thu, 23 Feb 2006 04:52:35 -0800 (PST) Received: by 10.36.106.12 with HTTP; Thu, 23 Feb 2006 04:52:35 -0800 (PST) Message-ID: Date: Thu, 23 Feb 2006 14:52:35 +0200 From: "Ibrahim Tekin" To: "Brendan Duddridge" Subject: Re: LIKE query on indexes Cc: "Scott Marlowe" , pgsql-performance@postgresql.org In-Reply-To: <78873B6F-1572-467C-8F98-9715FC8AC4AC@clickspace.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_5700_33202965.1140699155449" References: <1140538679.5777.3.camel@state.g2switchworks.com> <20060221163416.GC6541@surnet.cl> <78873B6F-1572-467C-8F98-9715FC8AC4AC@clickspace.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.312 required=5 tests=[AWL=0.311, HTML_MESSAGE=0.001] X-Spam-Score: 0.312 X-Spam-Level: X-Archive-Number: 200602/363 X-Sequence-Number: 17349 ------=_Part_5700_33202965.1140699155449 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline hi, i ran a query with ILIKE but it doesn't use the index. but i tried following method, and it worked. there is 3 extra lower() overhead but i don't think it will effect the performance. CREATE INDEX index_name ON mytable (lower(column) varchar_pattern_ops); SELECT * FROM mytable WHERE lower(column) LIKE lower('beginswith%') if insert operations are high in database. you use only this index to searc= h case sensitive. say you want this: SELECT * FROM mytable WHERE column LIKE 'beGinsWith%' write this: SELECT * FROM mytable WHERE lower(column) LIKE lower('beGinsWith%') AND column LIKE 'beGinsWith%' than query planner will search on index, than scan the resulting bitmap heap. On 2/22/06, Brendan Duddridge wrote: > > Hi, > Can this technique work with case insensitive ILIKE? > > It didn't seem to use the index when I used ILIKE instead of LIKE. > Thanks, > * > *____________________________________________________________________ > *Brendan Duddridge* | CTO | 403-277-5591 x24 | brendan@clickspace.com > * > *ClickSpace Interactive Inc. > Suite L100, 239 - 10th Ave. SE > Calgary, AB T2G 0V9 > > http://www.clickspace.com > > On Feb 21, 2006, at 1:28 PM, Ibrahim Tekin wrote: > > this trick did the job. > thanks. > > On 2/21/06, Alvaro Herrera wrote: > > > > Scott Marlowe wrote: > > > On Tue, 2006-02-21 at 09:57, Ibrahim Tekin wrote: > > > > hi, > > > > i have btree index on a text type field. i want see rows which > > starts > > > > with certain characters on that field. so i write a query like this= : > > > > > > > > SELECT * FROM mytable WHERE myfield LIKE 'john%' > > > > > > > > since this condition is from start of the field, query planner > > should > > > > use index to find such elements but explain command shows me it wil= l > > > > do a sequential scan. > > > > > > > > is this lack of a feature or i am wrong somewhere? > > > > > > This is an artifact of how PostgreSQL handles locales other than > > ASCII. > > > > > > If you want such a query to use an index, you need to back up your > > > database, and re-initdb with --locale=3DC as an argument. > > > > ... or you can choose to create an index with the text_pattern_ops > > operator class, which would be used in a LIKE constraint regardless of > > locale. > > > > http://www.postgresql.org/docs/8.1/static/indexes-opclass.html > > > > -- > > Alvaro Herrera http://www.CommandPrompt.= com/ > > The PostgreSQL Company - Command Prompt, Inc. > > > > > > ------=_Part_5700_33202965.1140699155449 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline hi,

i ran a query with ILIKE but it doesn't use the index.

bu= t i tried following method, and it worked. there is 3 extra lower() overhea= d but i don't think it will effect the performance.

CREATE INDEX ind= ex_name ON mytable (lower(column) varchar_pattern_ops);

SELECT * FROM mytable WHERE lower(column) LIKE lower('beginswith%')=

if insert operations are high in database. you use only this index = to search case sensitive.

say you want this:
SELECT * FROM mytabl= e WHERE column LIKE 'beGinsWith%'

write this:
SELECT * FROM mytable WHERE lower(column) LIKE lower= ('beGinsWith%') AND column LIKE 'beGinsWith%'

than query planner wil= l search on index, than scan the resulting bitmap heap.


On 2/22/06, Brend= an Duddridge <brendan@clic= kspace.com> wrote:
Hi,

Can this technique work with case insensitive ILIKE?=

It didn't seem to use the index when I used ILIKE= instead of LIKE.

Thanks,

______________________________________= ______________________________
Brendan Duddridge | CTO | 403= -277-5591 x24 |   brendan@clickspace.com

ClickSpace Interactive Inc.
Suite L100, 239 - 10th Ave. SE
Calg= ary, AB  T2G 0V9

http://www.clickspace.com 

On Feb 21, 2006, = at 1:28 PM, Ibrahim Tekin wrote:

this trick did the job.
thanks.
=
On 2/21/06, Alvaro Herrera < alvherre@commandprompt.com> wrote:
Scott Marlowe wrote:
> On Tue, 2006-0= 2-21 at 09:57, Ibrahim Tekin wrote:
> > hi,
> > i have btree index on a text type field. i = want see rows which starts
> > with certain characters on that fie= ld. so i write a query like this:
> >
> > SELECT * FROM m= ytable WHERE myfield LIKE 'john%'=20
> >
> > since this condition is from start of the field,= query planner should
> > use index to find such elements but expl= ain command shows me it will
> > do a sequential scan.
> >= ;=20
> > is this lack of a feature or i am wrong somewhere?
>> This is an artifact of how PostgreSQL handles locales other than ASCI= I.
>
> If you want such a query to use an index, you need to ba= ck up your=20
> database, and re-initdb with --locale=3DC as an argument.

.= .. or you can choose to create an index with the text_pattern_ops
operat= or class, which would be used in a LIKE constraint regardless of
locale.= =20

http://www.postgresql.org/docs/8.1/static/indexes-opclass.html<= br>
--
Alvaro Herrera        &nb= sp;            =             http://www.CommandPrompt.com/
Th= e PostgreSQL Company - Command Prompt, Inc.




------=_Part_5700_33202965.1140699155449--