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 1A14B9DCA1C for ; Tue, 21 Feb 2006 16:28:15 -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 40217-02 for ; Tue, 21 Feb 2006 16:28:15 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from zproxy.gmail.com (zproxy.gmail.com [64.233.162.193]) by postgresql.org (Postfix) with ESMTP id 3E7369DCA4B for ; Tue, 21 Feb 2006 16:28:10 -0400 (AST) Received: by zproxy.gmail.com with SMTP id n1so1334284nzf for ; Tue, 21 Feb 2006 12:28:12 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=dMLXK7yZO+VL98f6XeQSuuV8yfLR9MCo2qZCsOaNj/y1zTzKVI/4ZKRXFS9hDcOfJtlMl63KhvSfWBoU5vFn+LeeVnLB0EVJDa2qLZ6amMThLR2y68e5z4rtM93DzL07hb+1f/BexkiiOfxasxkSFI9PaZF7oHy7E2HdZdoLybA= Received: by 10.36.247.33 with SMTP id u33mr4982047nzh; Tue, 21 Feb 2006 12:28:09 -0800 (PST) Received: by 10.36.106.12 with HTTP; Tue, 21 Feb 2006 12:28:09 -0800 (PST) Message-ID: Date: Tue, 21 Feb 2006 22:28:09 +0200 From: "Ibrahim Tekin" To: "Scott Marlowe" , "Ibrahim Tekin" , pgsql-performance@postgresql.org Subject: Re: LIKE query on indexes In-Reply-To: <20060221163416.GC6541@surnet.cl> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_25486_19611329.1140553689280" References: <1140538679.5777.3.camel@state.g2switchworks.com> <20060221163416.GC6541@surnet.cl> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.36 required=5 tests=[AWL=0.359, HTML_MESSAGE=0.001] X-Spam-Score: 0.36 X-Spam-Level: X-Archive-Number: 200602/334 X-Sequence-Number: 17320 ------=_Part_25486_19611329.1140553689280 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline 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 will > > > 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_25486_19611329.1140553689280 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline this trick did the job.
thanks.

On 2/21/06, Alvaro Herrera <alvherre@commandprompt.com> w= rote:

Scott Marl= owe 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 whic= h starts
> > with certain characters on that field. so i write a q= uery like this:
> >
> > SELECT * FROM mytable WHERE myfie= ld LIKE 'john%'
> >
> > 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.
> >= ;
> > 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
> 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.

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

------=_Part_25486_19611329.1140553689280--