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 CE7B19DC83D for ; Tue, 21 Feb 2006 13:40:23 -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 12252-04 for ; Tue, 21 Feb 2006 13:40:22 -0400 (AST) Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 923E89DC871 for ; Tue, 21 Feb 2006 13:40:21 -0400 (AST) Received: from mail.envysolutions.com (mark.mielke.cc [206.248.142.186]) by svr4.postgresql.org (Postfix) with ESMTP id B36F15AF078 for ; Tue, 21 Feb 2006 17:40:19 +0000 (GMT) Received: from localhost (localhost.localdomain [127.0.0.1]) by mail.envysolutions.com (Postfix) with ESMTP id 6A2451CCF5; Tue, 21 Feb 2006 12:40:48 -0500 (EST) Received: from mail.envysolutions.com ([127.0.0.1]) by localhost (mark.mielke.cc [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 07931-05; Tue, 21 Feb 2006 12:40:48 -0500 (EST) Received: by mail.envysolutions.com (Postfix, from userid 500) id 545541CE15; Tue, 21 Feb 2006 12:40:48 -0500 (EST) Date: Tue, 21 Feb 2006 12:40:48 -0500 From: mark@mark.mielke.cc To: Ibrahim Tekin Cc: pgsql-performance@postgresql.org Subject: Re: LIKE query on indexes Message-ID: <20060221174048.GA8108@mark.mielke.cc> References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.4.2.1i X-Virus-Scanned: amavisd-new at mail.envysolutions.com X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.659 required=5 tests=[AWL=0.109, NO_REAL_NAME=0.55] X-Spam-Score: 0.659 X-Spam-Level: X-Archive-Number: 200602/331 X-Sequence-Number: 17317 On Tue, Feb 21, 2006 at 05:57:12PM +0200, Ibrahim Tekin wrote: > 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? Is the query fast enough? How big is your table? What does explain analyze select tell you? Cheers, mark -- mark@mielke.cc / markm@ncf.ca / markm@nortel.com __________________________ . . _ ._ . . .__ . . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/ |_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/