Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 64E0163273C for ; Thu, 18 Feb 2010 08:02:45 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 85497-06 for ; Thu, 18 Feb 2010 12:02:27 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-fx0-f219.google.com (mail-fx0-f219.google.com [209.85.220.219]) by mail.postgresql.org (Postfix) with ESMTP id B4C97632343 for ; Thu, 18 Feb 2010 08:00:54 -0400 (AST) Received: by fxm19 with SMTP id 19so1380216fxm.3 for ; Thu, 18 Feb 2010 04:00:52 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :from:date:message-id:subject:to:cc:content-type; bh=L1Mp2NwVEjj8CZ8kAugzc7blzbetz7MOFTNcUSCfNNw=; b=v1WkFLKFI6oBz9jQQKCZSezQ2Du4YbE0pyroYVcQFEavg20iFSjzNdsrgdESxXtXNh 3rfKYqCpvYLb0az7l75d50vUEOieJg2ysOkUqMHzWcKalaHRHlIezJ3UPGN76S3YZ3Rq U92lsBKWiLVJSnUV6A6Fk7j7ZEXAf6sVIEhvI= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc:content-type; b=OK0YwVGjw0a6TmJTRCbRMHXsZqa6bpV8rCd4zvPAA8ssbQxWUCnxuGJObi9X4kb3FP KAmfs6duSEE+mAFNXAERGPlXi9wkLFoA6IL5tYaAYxf3CaIMO4w0kGdQ4fHh6pz/AQLP 4NvuJyCBc47MNz2pt2ah87euFZu0kOPA+TvN8= MIME-Version: 1.0 Received: by 10.102.174.11 with SMTP id w11mr6886900mue.17.1266494449176; Thu, 18 Feb 2010 04:00:49 -0800 (PST) In-Reply-To: <2a7905441002180355w6dc89a87y8643c0a7f881072a@mail.gmail.com> References: <2a7905441002180355w6dc89a87y8643c0a7f881072a@mail.gmail.com> From: Thom Brown Date: Thu, 18 Feb 2010 12:00:29 +0000 Message-ID: Subject: Re: index usage in not like To: AI Rumman Cc: pgsql-performance@postgresql.org Content-Type: text/plain; charset=ISO-8859-1 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.425 tagged_above=-10 required=5 tests=AWL=-0.685, BAYES_20=-0.74 X-Spam-Level: X-Archive-Number: 201002/271 X-Sequence-Number: 37692 On 18 February 2010 11:55, AI Rumman wrote: > "Not like" operation does not use index. > > select * from vtiger_contactscf where lower(cf_1253) not like > lower('Former%') > > I created index on lower(cf_1253). > > How can I ensure index usage in not like operation? > Anyone please help. > How many rows do you have in your table? If there are relatively few, it probably guesses it to be cheaper to do a sequential scan and calculate lower values on-the-fly rather than bother with the index. Thom