Re: LIKE, leading percent, bind parameters and indexes - Mailing list pgsql-hackers

From Mark Woodward
Subject Re: LIKE, leading percent, bind parameters and indexes
Date
Msg-id 18898.24.91.171.78.1148686363.squirrel@mail.mohawksoft.com
Whole thread Raw
In response to Re: LIKE, leading percent, bind parameters and indexes  ("Jim C. Nasby" <jnasby@pervasive.com>)
List pgsql-hackers
> 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);




pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: Inefficient bytea escaping?
Next
From: Tom Lane
Date:
Subject: Re: Question about "name" datatype