Re: Worthwhile optimisation of position()?

[Thomas Hallgren <thomas(at)tada(dot)se>]

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