Re: Worthwhile optimisation of position()?

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

Thomas Hallgren wrote:
> Tom Lane wrote:
> > Tim Allen <tim(at)proximity(dot)com(dot)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