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 A385E3A5425 for ; Mon, 24 Jan 2005 09:18:13 +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 73898-05 for ; Mon, 24 Jan 2005 09:18:10 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.193]) by svr1.postgresql.org (Postfix) with ESMTP id 919853A5419 for ; Mon, 24 Jan 2005 09:18:10 +0000 (GMT) Received: by wproxy.gmail.com with SMTP id 57so152972wri for ; Mon, 24 Jan 2005 01:18:10 -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:mime-version:content-type:content-transfer-encoding; b=mcQEcKxijcgcZfzXgwAnPRGwWs9EPoNcIqMsqafPinAJEGsvDkwa+rqpX/ykmOGHJ9NueCysBIEJHkr7X4za5UxSJzXiZkHd2SOATzz2b8btGRsWMQjiHx58pTZER+65RJ2l2CT6nNCF9UoEBY/JtC5Aw7b07MCi/bdoo7j679k= Received: by 10.54.26.35 with SMTP id 35mr117548wrz; Mon, 24 Jan 2005 01:18:10 -0800 (PST) Received: by 10.54.31.32 with HTTP; Mon, 24 Jan 2005 01:18:10 -0800 (PST) Message-ID: <2989532e05012401184b0c3ef9@mail.gmail.com> Date: Mon, 24 Jan 2005 14:48:10 +0530 From: Antony Paul Reply-To: Antony Paul To: pgsql-performance@postgresql.org Subject: How to boost performance of ilike queries ? Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.54 tagged_above=0 required=5 tests=FROM_ENDS_IN_NUMS, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200501/434 X-Sequence-Number: 10124 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 ?. rgds Antony Paul