Re: Worthwhile optimisation of position()?

[Tim Allen <tim(at)proximity(dot)com(dot)au>]

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(at)proximity(dot)com(dot)au
Proximity Pty Ltd  http://www.proximity.com.au/