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(at)crankycanuck(dot)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
>