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=H7j74CI1HSVAkhp+BbvSNkg3RdzrtO8X0kpRIkz7XlJf0bww0K5hGdlRkwq9lPA1rc
	w0NBBD2wxXncSMI47RyT/I+Enmd6hpbyX2j2EH8J3qaHzGwe0WhD0ht2+YFmjjcRIPLD
	0MUo44634Ad0uJqfifIkSmidS3uxZj9k43RZ8=
MIME-Version: 1.0
In-Reply-To: <2a7905441002180406g571578admf467fd90f01420cd@mail.gmail.com>
References: <2a7905441002180355w6dc89a87y8643c0a7f881072a@mail.gmail.com>
	<bddc86151002180400n920f56ah9fa3d68156c6b24a@mail.gmail.com>
	<2a7905441002180406g571578admf467fd90f01420cd@mail.gmail.com>
From: Thom Brown <thombrown@gmail.com>
Date: Thu, 18 Feb 2010 12:14:03 +0000
Message-ID: <bddc86151002180414s2b080e5ar6d1054027fb9e047@mail.gmail.com>
Subject: Re: index usage in not like
To: AI Rumman <rummandba@gmail.com>
Cc: pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

> On Thu, Feb 18, 2010 at 6:00 PM, Thom Brown <thombrown@gmail.com> wrote:
>>
>> 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.
>>
>> Thom
>
On 18 February 2010 12:06, AI Rumman <rummandba@gmail.com> wrote:
> vtigercrm504=3D# explain analyze select * from vtiger_contactscf where
> lower(cf_1253)  like 'customer';
>
>                                             QUERY
> PLAN
> -------------------------------------------------------------------------=
---------------------------------------------------------------------------=
--------
>  Index Scan using vtiger_contactscf_cf_1253_idx on vtiger_contactscf
> (cost=3D0.00..146.54 rows=3D6093 width=3D179) (actual time=3D0.083..29.86=
8 rows=3D5171
> loops=3D1)
>    Index Cond: (lower((cf_1253)::text) ~=3D~ 'customer'::character varyin=
g)
>    Filter: (lower((cf_1253)::text) ~~ 'customer'::text)
>  Total runtime: 34.956 ms
> (4 rows)
> vtigercrm504=3D# explain analyze select * from vtiger_contactscf where
> lower(cf_1253)  like 'customer';
>
> QUERY
> PLAN
> -------------------------------------------------------------------------=
---------------------------------------------------------------------------=
--------
>  Index Scan using vtiger_contactscf_cf_1253_idx on vtiger_contactscf
> (cost=3D0.00..146.54 rows=3D6093 width=3D179) (actual time=3D0.083..29.86=
8 rows=3D5171
> loops=3D1)
>    Index Cond: (lower((cf_1253)::text) ~=3D~ 'customer'::character varyin=
g)
>    Filter: (lower((cf_1253)::text) ~~ 'customer'::text)
>  Total runtime: 34.956 ms
> (4 rows)

Could you do the same again for a "not like" query?

Thom