Re: LIKE, leading percent, bind parameters and indexes

These are two confusing issues.

One is the use of a leading percent sign.

What Tom pointed out was with a bound parameter the planner can't  
make any assumptions about indexes.

Leading percent signs can be made to use indexes by creating a  
functional index on the column which reverses the order of the  
column, then using the same function in the select

Dave
On 25-May-06, at 1:46 PM, Andrew Sullivan wrote:

> On Thu, May 25, 2006 at 02:18:10PM -0300, Rodrigo Hjort wrote:
>> make a index scan. Otherwise, i.e. using leading '%' on static  
>> text or bound
>> paremeter, makes the planner always do a sequential scan. Is that the
>> scenario?
>
> I think more exactly, the planner can't possibly know how to plan an
> indexscan with a leading '%', because it has nowhere to start.
>
> Think of it this way: if you go to the public library, and say, "I
> want a book.  I can't remember its name exactly, but it starts with
> 'daytime'," you can find it by going to the title index and browsing
> for things that start that way.  If you go to the public library, and
> say, "There's this book I want, but I can't remember the title.  It's
> red," you're going to have a lot of books to look through.  Maybe all
> of them.
>
> If it were important enough -- say you left a $10,000 cheque inside
> -- you might just start looking.  Maybe you'll get lucky, and hit  
> it.  
>
> A
>
> -- 
> Andrew Sullivan  | ajs@crankycanuck.ca
> I remember when computers were frustrating because they *did*  
> exactly what
> you told them to.  That actually seems sort of quaint now.
>         --J.D. Baldwin
>
> ---------------------------(end of  
> broadcast)---------------------------
> TIP 6: explain analyze is your friend
>