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 30DC73A546A for ; Mon, 24 Jan 2005 09:59:03 +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 81293-05 for ; Mon, 24 Jan 2005 09:58:58 +0000 (GMT) Received: from pws.com.au (mail.pws.com.au [210.23.138.139]) by svr1.postgresql.org (Postfix) with SMTP id 2BB1E3A5464 for ; Mon, 24 Jan 2005 09:58:57 +0000 (GMT) Received: (qmail 13716 invoked from network); 24 Jan 2005 09:58:55 -0000 Received: from unknown (HELO wizzard.pws.com.au) (russell@pws.com.au@138.217.55.142) by mail.pws.com.au with SMTP; 24 Jan 2005 09:58:55 -0000 From: Russell Smith To: Antony Paul Subject: Re: How to boost performance of ilike queries ? Date: Mon, 24 Jan 2005 20:58:54 +1100 User-Agent: KMail/1.7 Cc: pgsql-performance@postgresql.org References: <2989532e05012401184b0c3ef9@mail.gmail.com> In-Reply-To: <2989532e05012401184b0c3ef9@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200501242058.54670.mr-russ@pws.com.au> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.025 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200501/436 X-Sequence-Number: 10126 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