X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 515FED1B48D for ; Fri, 19 Dec 2003 16:01:33 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 25138-10 for ; Fri, 19 Dec 2003 12:00:44 -0400 (AST) Received: from webware.ee (webware.ee [212.7.7.3]) by svr1.postgresql.org (Postfix) with ESMTP id 3AC97D1B471 for ; Fri, 19 Dec 2003 12:00:30 -0400 (AST) Received: from webware.ee (pc234.host4.starman.ee [62.65.196.234]) by webware.ee (Postfix) with ESMTP id D7DF1378100; Fri, 19 Dec 2003 18:01:34 +0200 (EET) Message-ID: <3FE32099.40901@webware.ee> Date: Fri, 19 Dec 2003 18:00:25 +0200 From: =?ISO-8859-1?Q?Erki_Kaldj=E4rv?= Organization: Webware =?ISO-8859-1?Q?O=DC?= User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-GB; rv:1.0.1) Gecko/20020823 Netscape/7.0 X-Accept-Language: et,en MIME-Version: 1.0 To: Tom Lane Cc: pg@fastcrypt.com, Christopher Kings-Lynne , pgsql-performance@postgresql.org Subject: Re: is it possible to get the optimizer to use indexes References: <3FE1F5A1.6080908@geizhals.at> <60n09q9bqg.fsf@dev6.int.libertyrms.info> <1071789528.1629.10.camel@localhost.localdomain> <3FE256AA.8000707@familyhealth.com.au> <1071803317.1629.16.camel@localhost.localdomain> <6121.1071805440@sss.pgh.pa.us> <1071827412.1627.25.camel@localhost.localdomain> <9224.1071844694@sss.pgh.pa.us> Content-Type: multipart/alternative; boundary="------------040301000406030407010001" X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/277 X-Sequence-Number: 5137 --------------040301000406030407010001 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit Hello, i got indexes to work with "text_pattern_ops" for locale et_EE. So instead of: create index some_index_name on some_table(some_text_field); nor create index some_index_name on some_table(some_text_field text_ops); try to create index as follows: create index some_index_name on some_table(some_text_field text_pattern_ops); Note that text_pattern_ops is available pg >= 7.4. Regards, Erki Kaldjärv Webware OÜ www.webware.ee Tom Lane wrote: >Dave Cramer writes: > > >>So even in a north-american locale, such as en_CA this will be a >>problem? >> >> > >If it's not "C" we won't try to optimize LIKE. I know en_US does not >work (case-insensitive, funny rules about spaces, etc) and I would >expect en_CA has the same issues. > >If you're using 7.4 you have the option to create a special index >instead of re-initdb'ing your whole database. > > regards, tom lane > >---------------------------(end of broadcast)--------------------------- >TIP 4: Don't 'kill -9' the postmaster > > --------------040301000406030407010001 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit Hello,

i got indexes to work with "text_pattern_ops" for locale et_EE.

So instead of:
create index some_index_name on some_table(some_text_field);

nor

create index some_index_name on some_table(some_text_field text_ops);

try to create index as follows:
create index some_index_name on some_table(some_text_field text_pattern_ops);

Note that text_pattern_ops is available pg >= 7.4.

Regards,

Erki Kaldjärv
Webware OÜ
www.webware.ee

Tom Lane wrote:
Dave Cramer <pg@fastcrypt.com> writes:
  
So even in a north-american locale, such as en_CA this will be a
problem?
    

If it's not "C" we won't try to optimize LIKE.  I know en_US does not
work (case-insensitive, funny rules about spaces, etc) and I would
expect en_CA has the same issues.

If you're using 7.4 you have the option to create a special index
instead of re-initdb'ing your whole database.

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
  

--------------040301000406030407010001--