Re: LIKE, leading percent, bind parameters and indexes

On Fri, May 26, 2006 at 11:38:41AM -0500, Jim C. Nasby wrote:
> > 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)
>
> 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.

Planning doesn't work that way. Like is just a function invokation, the
planner doesn't ask or tell it where it is in the plan. And it's not
the function that determines how the query is planned.

> 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.

This is an interesting thought. Currently, AFAICS, the bitmap-scan code
only considers operators that are indexable, just like for narmal index
scans. However, in this case the query could scan the entire index,
apply the LIKE to each one and produce a bitmap of possible matches.
Then do a bitmap scan over the table to check the results.

Not just LIKE could use this, but any function marked STABLE. You'd
have to weigh up the cost of scanning the *entire* index (because we
don't have any actual restriction clauses) against avoiding a full
table scan.

Actually, if you're going to scan the whole index, maybe you can use
the recent changes that allow VACUUM to scan the index sequentially,
rather than by index order. Surely a sequential disk scan over the
index to create the bitmap would a big win.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.