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 16D089DC83D for ; Tue, 21 Feb 2006 12:18:04 -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 99934-04 for ; Tue, 21 Feb 2006 12:18:02 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from exchange.g2switchworks.com (mail.g2switchworks.com [63.87.162.25]) by postgresql.org (Postfix) with ESMTP id B68569DC82A for ; Tue, 21 Feb 2006 12:18:01 -0400 (AST) Received: from 10.10.1.37 ([10.10.1.37]) by exchange.g2switchworks.com ([10.10.1.2]) with Microsoft Exchange Server HTTP-DAV ; Tue, 21 Feb 2006 16:18:00 +0000 Received: from state.g2switchworks.com by mail.g2switchworks.com; 21 Feb 2006 10:18:00 -0600 Subject: Re: LIKE query on indexes From: Scott Marlowe To: Ibrahim Tekin Cc: pgsql-performance@postgresql.org In-Reply-To: References: Content-Type: text/plain Content-Transfer-Encoding: 7bit Message-Id: <1140538679.5777.3.camel@state.g2switchworks.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Tue, 21 Feb 2006 10:18:00 -0600 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.158 required=5 tests=[AWL=0.157, UNPARSEABLE_RELAY=0.001] X-Spam-Score: 0.158 X-Spam-Level: X-Archive-Number: 200602/328 X-Sequence-Number: 17314 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=C as an argument. Note that you then will NOT get locale specific matching and sorting.