Re: LIKE, leading percent, bind parameters and indexes

["Mark Woodward" <pgsql(at)mohawksoft(dot)com>]

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

My solution is a function in one of my libraries called "strrev()" which
returns the reverse of a string. I make a function index of a
strrev(field). Then, just search where strrev('%the name') like
strrev(field);