Re: LIKE, leading percent, bind parameters and indexes

On Thu, May 25, 2006 at 08:41:17PM -0300, Rodrigo Hjort wrote:
> >
> >I think more exactly, the planner can't possibly know how to plan an
> >indexscan with a leading '%', because it has nowhere to start.
> >
>
> The fact is that index scan is performed on LIKE expression on a string not
> preceded by '%', except when bound parameter is used.
>
> select * from table where field like 'THE NAME%'; -- index scan
> select * from table where field like '%THE NAME%'; -- seq scan
> select * from table where field like :bind_param; -- seq scan (always)

Since I'm somewhat doubtful of coming up with a generic means for
dealing with plan changes based on different bound parameter values any
time soon...

How difficult would it be to make LIKE check the value of the bound
parameter for a starting % and use that information to decide on a query
plan? IMHO this is worth making into a special case in the planner,
because it's very easy to detect and makes a tremendous difference in
the query plan/performance.

Also, might a bitmap scan be a win for the %string case? Presumably it's
much faster to find matching rows via an index and then go back into the
heap for them; unless you're matching a heck of a lot of rows.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461