X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id AF4413A5413 for ; Tue, 25 Jan 2005 14:01:34 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 63319-07 for ; Tue, 25 Jan 2005 14:01:23 +0000 (GMT) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr1.postgresql.org (Postfix) with ESMTP id 597FF3A4FB8 for ; Tue, 25 Jan 2005 14:01:25 +0000 (GMT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id AE9D135856; Tue, 25 Jan 2005 06:01:25 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id AD0CB3583B; Tue, 25 Jan 2005 06:01:25 -0800 (PST) Date: Tue, 25 Jan 2005 06:01:25 -0800 (PST) From: Stephan Szabo To: Antony Paul Cc: Russell Smith , pgsql-performance@postgresql.org Subject: Re: How to boost performance of ilike queries ? In-Reply-To: <2989532e05012500235c78fd47@mail.gmail.com> Message-ID: <20050125055555.J11415@megazone.bigpanda.com> References: <2989532e05012401184b0c3ef9@mail.gmail.com> <200501242058.54670.mr-russ@pws.com.au> <2989532e05012500235c78fd47@mail.gmail.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.01 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200501/462 X-Sequence-Number: 10152 On Tue, 25 Jan 2005, Antony Paul wrote: > Creating an index and using lower(column) does not change the explain > plan estimates. > It seems that it is not using index for like or ilike queries > irrespective of whether it have a pattern matching character in it or > not. (using PostgreSQL 7.3.3) I believe in 7.3.x an index is only considered for like in "C" locale, I think the *_pattern_op opclasses were added in 7.4 for which you can make indexes that are considered for non wildcard starting search strings in non "C" locales. And it may have trouble doing estimates before 8.0 on the functional index because of lack of statistics. You may want to consider an upgrade once 8.0 shakes out a bit.