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:content-transfer-encoding;
	b=J4DztlzmoYi2Jr+QhdqMSK6ayjAsFTHbI/LWE4QDXenl6x68rIb68QHUHzzaX2LmHl
	4eiE5IDiKLjeAcbiKJwgprEZRuMcT9QgUBbPZaXWxAkq9cFv6lcHgGNdTX+ZJF8pjZoT
	sTWK4auUsCvJCEUjPM0vI0Rog/CBs1cjzAR/A=
MIME-Version: 1.0
In-Reply-To: <20100218121810.GG23676@a-kretschmer.de>
References: <2a7905441002180355w6dc89a87y8643c0a7f881072a@mail.gmail.com>
	<bddc86151002180400n920f56ah9fa3d68156c6b24a@mail.gmail.com>
	<20100218121810.GG23676@a-kretschmer.de>
From: Thom Brown <thombrown@gmail.com>
Date: Thu, 18 Feb 2010 12:40:23 +0000
Message-ID: <bddc86151002180440u153b1d95oc1d3de40762d95bf@mail.gmail.com>
Subject: Re: index usage in not like
To: "A. Kretschmer" <andreas.kretschmer@schollglas.com>
Cc: pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

On 18 February 2010 12:18, A. Kretschmer
<andreas.kretschmer@schollglas.com> wrote:
> In response to Thom Brown :
>> On 18 February 2010 11:55, AI Rumman <rummandba@gmail.com> 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? =A0If 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.
>
> That's one reason, an other reason, i think, is, that a btree-index can't
> search with an 'not like' - operator.
>

Erm.. yes.  Now that you say it, it's obvious. :S

Thom