Thread: Worthwhile optimisation of position()?
Is it worth allowing this:
select count(*) from users_users where position('ch' in username) = 0;
To be able to use an index, like:
select count(*) from users_users where username like 'ch%';
At the moment the position() syntax will do a seqscan, but the like
syntax will use an index.
Chris
Christopher Kings-Lynne wrote:
> Is it worth allowing this:
>
> select count(*) from users_users where position('ch' in username) = 0;
>
> To be able to use an index, like:
>
> select count(*) from users_users where username like 'ch%';
>
> At the moment the position() syntax will do a seqscan, but the like
> syntax will use an index.
>
You must compare position('ch' in username) to '%ch%' instead of 'ch%' in this respect.
The position function must look for 'ch' everywhere in the string so there's no way it can
use an index.
Regards,
Thomas Hallgren
Thomas Hallgren wrote:
> Christopher Kings-Lynne wrote:
>
>> Is it worth allowing this:
>>
>> select count(*) from users_users where position('ch' in username) = 0;
>>
>> To be able to use an index, like:
>>
>> select count(*) from users_users where username like 'ch%';
>>
>> At the moment the position() syntax will do a seqscan, but the like
>> syntax will use an index.
>>
> You must compare position('ch' in username) to '%ch%' instead of 'ch%'
> in this respect.
>
> The position function must look for 'ch' everywhere in the string so
> there's no way it can use an index.
I think the '= 0' bit is what Chris was suggesting could be the basis
for an optimisation.
Tim
--
-----------------------------------------------
Tim Allen tim@proximity.com.au
Proximity Pty Ltd http://www.proximity.com.au/
Tim Allen <tim@proximity.com.au> writes:
> Thomas Hallgren wrote:
>> The position function must look for 'ch' everywhere in the string so
>> there's no way it can use an index.
> I think the '= 0' bit is what Chris was suggesting could be the basis
> for an optimisation.
Yeah. AFAICS the transformation Chris suggested is valid. I'm really
dubious that it's worth expending planner cycles to look for it though.
LIKE is something that everybody and his brother uses, but who uses this
position()=0 locution?
regards, tom lane
Tom Lane wrote:
> Tim Allen <tim@proximity.com.au> writes:
>
>> Thomas Hallgren wrote:
>>
>>> The position function must look for 'ch' everywhere in the string so
>>> there's no way it can use an index.
>>>
>
>
>> I think the '= 0' bit is what Chris was suggesting could be the basis
>> for an optimisation.
>>
>
> Yeah. AFAICS the transformation Chris suggested is valid. I'm really
> dubious that it's worth expending planner cycles to look for it though.
> LIKE is something that everybody and his brother uses, but who uses this
> position()=0 locution?
>
> regards, tom lane
>
The documentation says: position('om' in 'Thomas') == 3 so i assumed
that the returned index was 1-based and that a zero meant 'not found'.
If I'm wrong ,perhaps the docs need to be updated?
Regards,
Thomas Hallgren
> Yeah. AFAICS the transformation Chris suggested is valid. I'm really > dubious that it's worth expending planner cycles to look for it though. > LIKE is something that everybody and his brother uses, but who uses this > position()=0 locution? One of our junior developers :) Which is why I noticed it. Chris
Thomas Hallgren wrote:
> Tom Lane wrote:
>> Tim Allen <tim@proximity.com.au> writes:
>>
>>> Thomas Hallgren wrote:
>>>
>>>> The position function must look for 'ch' everywhere in the string so
>>>> there's no way it can use an index.
>>>>
>>
>>
>>> I think the '= 0' bit is what Chris was suggesting could be the basis
>>> for an optimisation.
>>>
>>
>> Yeah. AFAICS the transformation Chris suggested is valid. I'm really
>> dubious that it's worth expending planner cycles to look for it though.
>> LIKE is something that everybody and his brother uses, but who uses this
>> position()=0 locution?
>>
>> regards, tom lane
>>
> The documentation says: position('om' in 'Thomas') == 3 so i assumed
> that the returned index was 1-based and that a zero meant 'not found'.
> If I'm wrong ,perhaps the docs need to be updated?
>
The docs are correct so my initial point was correct. "position('ch' in user) = 0" is
equivalent to "user NOT LIKE '%ch%'" and there's no way you can index that.
Regards,
Thomas Hallgren
> The docs are correct so my initial point was correct. "position('ch' in
> user) = 0" is equivalent to "user NOT LIKE '%ch%'" and there's no way
> you can index that.
Well = 1 then.
Chris
On Fri, Mar 24, 2006 at 02:58:54PM +0800, Christopher Kings-Lynne wrote: > >Yeah. AFAICS the transformation Chris suggested is valid. I'm really > >dubious that it's worth expending planner cycles to look for it though. > >LIKE is something that everybody and his brother uses, but who uses this > >position()=0 locution? > > One of our junior developers :) Which is why I noticed it. Sounds like time to bust out the cluebat. :) -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461