Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f9CHWjQ23712 for ; Fri, 12 Oct 2001 13:32:45 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id f9CHWFI27333; Fri, 12 Oct 2001 13:32:15 -0400 (EDT) To: Patrik Kudo cc: "Ross J. Reedstrom" , pgsql-sql@postgresql.org Subject: Re: indexing and LIKE In-reply-to: <20011012100747.I99068-100000@tb303.partitur.se> References: <20011012100747.I99068-100000@tb303.partitur.se> Comments: In-reply-to Patrik Kudo message dated "Fri, 12 Oct 2001 10:16:33 +0200" Date: Fri, 12 Oct 2001 13:32:15 -0400 Message-ID: <27330.1002907935@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200110/153 X-Sequence-Number: 5108 Patrik Kudo writes: > Thanks for your respons, but I'm afraid it didn't help. I've succeeded > with indexing my table using functional indices, but the problem is that > the index I create won't work with my query. Works for me: test71=# create table person (last_name text); CREATE test71=# create index person_lower_lname_idx on person (lower(last_name)); CREATE test71=# explain select * from person where lower(last_name) like 'kud%'; NOTICE: QUERY PLAN: Index Scan using person_lower_lname_idx on person (cost=0.00..8.16 rows=10 width=12) EXPLAIN The difference is probably a locale problem: if you aren't in C locale then the index LIKE optimization is disabled because it doesn't work reliably. See the list archives for more info. regards, tom lane