From: Thom Brown <thombrown@gmail.com>
To: AI Rumman <rummandba@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: index usage in not like
Date: Thu, 18 Feb 2010 12:14:03 +0000
Message-ID: <bddc86151002180414s2b080e5ar6d1054027fb9e047@mail.gmail.com> (raw)
In-Reply-To: <2a7905441002180406g571578admf467fd90f01420cd@mail.gmail.com>
References: <2a7905441002180355w6dc89a87y8643c0a7f881072a@mail.gmail.com>
<bddc86151002180400n920f56ah9fa3d68156c6b24a@mail.gmail.com>
<2a7905441002180406g571578admf467fd90f01420cd@mail.gmail.com>
> 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? 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@gmail.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: thombrown@gmail.com, rummandba@gmail.com
Subject: Re: index usage in not like
In-Reply-To: <bddc86151002180414s2b080e5ar6d1054027fb9e047@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox