Обсуждение: 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