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 42A173A42CD for ; Tue, 25 Jan 2005 09:53:46 +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 34733-03 for ; Tue, 25 Jan 2005 09:53:33 +0000 (GMT) Received: from pws.com.au (mail.pws.com.au [210.23.138.139]) by svr1.postgresql.org (Postfix) with SMTP id 40DD23A55B3 for ; Tue, 25 Jan 2005 09:53:33 +0000 (GMT) Received: (qmail 31625 invoked from network); 25 Jan 2005 08:53:09 -0000 Received: from unknown (HELO wizzard.pws.com.au) (russell@pws.com.au@138.217.55.142) by 210.23.138.139 with SMTP; 25 Jan 2005 08:53:09 -0000 From: Russell Smith To: Antony Paul Subject: Re: How to boost performance of ilike queries ? Date: Tue, 25 Jan 2005 19:49:12 +1100 User-Agent: KMail/1.7 References: <2989532e05012401184b0c3ef9@mail.gmail.com> <200501242058.54670.mr-russ@pws.com.au> <2989532e05012500235c78fd47@mail.gmail.com> In-Reply-To: <2989532e05012500235c78fd47@mail.gmail.com> Cc: pgsql-performance@postgresql.org MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200501251949.12874.mr-russ@pws.com.au> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.02 tagged_above=0 required=5 tests=AWL, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200501/455 X-Sequence-Number: 10145 On Tue, 25 Jan 2005 07:23 pm, 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) > > On googling I found this thread > > http://archives.postgresql.org/pgsql-sql/2004-11/msg00285.php > > It says that index is not used if the search string begins with a % symbol. What exactly are the type of like queries you are going? there is a solution for having the % at the start, but you can win everyway. > > rgds > Antony Paul > > On Mon, 24 Jan 2005 20:58:54 +1100, Russell Smith wrote: > > On Mon, 24 Jan 2005 08:18 pm, Antony Paul wrote: > > > Hi, > > > I have a query which is executed using ilike. The query values are > > > received from user and it is executed using PreparedStatement. > > > Currently all queries are executed as it is using iilike irrespective > > > of whether it have a pattern matching character or not. Can using = > > > instead of ilike boot performance ?. If creating index can help then > > > how the index should be created on lower case or uppercase ?. > > > > > It depends on the type of queries you are doing. > > > > changing it to something like lower(column) like lower('text%'), and > > creating an index on lower(column) will give you much better performance. > > > > If you have % in the middle of the query, it will still be slow, but I assume that is not > > the general case. > > > > I am not sure what the effect of it being prepared will be, however I've had much success > > with the method above without the queries being prepared. Others may be able to offer advice > > about if prepare will effect it. > > > > Regards > > > > Russell Smith > > > >