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 866003A5697 for ; Tue, 25 Jan 2005 08:23:37 +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 19369-06 for ; Tue, 25 Jan 2005 08:23:26 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.194]) by svr1.postgresql.org (Postfix) with ESMTP id C9B6C3A5501 for ; Tue, 25 Jan 2005 08:23:27 +0000 (GMT) Received: by wproxy.gmail.com with SMTP id 57so34342wri for ; Tue, 25 Jan 2005 00:23:26 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:references; b=UnDF2q83B+fBFOmdRCuyJmT4KWx2JoNpTvpaetHUBHYqBhzj9ILi6LgZPGHixgz+xO/aUlMcDHg9xwoZmstDsld52Mi5LQPwE5qlvdyLBzatSrNCun6bj8TbW0eQCCyD0inhKSMxEHtmWxsB9npHDlBmrq2rxqschaKhu7PsBBU= Received: by 10.54.36.22 with SMTP id j22mr191975wrj; Tue, 25 Jan 2005 00:23:26 -0800 (PST) Received: by 10.54.31.32 with HTTP; Tue, 25 Jan 2005 00:23:26 -0800 (PST) Message-ID: <2989532e05012500235c78fd47@mail.gmail.com> Date: Tue, 25 Jan 2005 13:53:26 +0530 From: Antony Paul Reply-To: Antony Paul To: Russell Smith Subject: Re: How to boost performance of ilike queries ? Cc: pgsql-performance@postgresql.org In-Reply-To: <200501242058.54670.mr-russ@pws.com.au> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit References: <2989532e05012401184b0c3ef9@mail.gmail.com> <200501242058.54670.mr-russ@pws.com.au> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.54 tagged_above=0 required=5 tests=AWL, FROM_ENDS_IN_NUMS, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200501/453 X-Sequence-Number: 10143 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. 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 >