Re: index usage in not like

> On Thu, Feb 18, 2010 at 6:00 PM, Thom Brown <thombrown(at)gmail(dot)com> wrote:
>>
>> On 18 February 2010 11:55, AI Rumman <rummandba(at)gmail(dot)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?  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
>
On 18 February 2010 12:06, AI Rumman <rummandba(at)gmail(dot)com> wrote:
> vtigercrm504=# 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=0.00..146.54 rows=6093 width=179) (actual time=0.083..29.868 rows=5171
> loops=1)
> Index Cond: (lower((cf_1253)::text) ~=~ 'customer'::character varying)
> Filter: (lower((cf_1253)::text) ~~ 'customer'::text)
> Total runtime: 34.956 ms
> (4 rows)
> vtigercrm504=# 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=0.00..146.54 rows=6093 width=179) (actual time=0.083..29.868 rows=5171
> loops=1)
> Index Cond: (lower((cf_1253)::text) ~=~ 'customer'::character varying)
> 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