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 A53849DCA94 for ; Fri, 3 Mar 2006 00:01:53 -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 51228-02 for ; Fri, 3 Mar 2006 00:01:51 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from stark.xeocode.com (stark.xeocode.com [216.58.44.227]) by postgresql.org (Postfix) with ESMTP id C5D4F9DCA8C for ; Fri, 3 Mar 2006 00:01:50 -0400 (AST) Received: from localhost ([127.0.0.1] helo=stark.xeocode.com) by stark.xeocode.com with smtp (Exim 3.36 #1 (Debian)) id 1FF1U3-0006Uf-00; Thu, 02 Mar 2006 23:01:39 -0500 To: "Jozsef Szalay" Cc: Subject: Re: Like 'name%' is not using index References: In-Reply-To: From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 02 Mar 2006 23:01:39 -0500 Message-ID: <877j7cuqfw.fsf@stark.xeocode.com> Lines: 25 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.4 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.13 required=5 tests=[AWL=0.130] X-Spam-Score: 0.13 X-Spam-Level: X-Archive-Number: 200603/17 X-Sequence-Number: 17454 "Jozsef Szalay" writes: > One would > think that Postgres will use the index to look up the matches, but > apparently that is not the case. It performs a full table scan. My > query looks something like this: > > SELECT * FROM table WHERE name LIKE 'smith%'; There are two possible answers here: First, what does this output on your database? db=> show lc_collate; If it's not "C" then the index can't be used. You would have to make a second special-purpose index specifically for use with LIKE. Secondly, please send "explain analyze" output for your query. It will show if the optimizer is simply estimating that the index won't help enough to be faster than the full table scan. -- greg