Re: What type of index do I need for this JOIN?

Robert James <srobertjames(at)gmail(dot)com> wrote:
> On 8/14/13, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
>> Robert James <srobertjames(at)gmail(dot)com> wrote:
>>
>>> I'm confused: What's the difference between
>>>   col LIKE  'foo%'
>>> and
>>>   col LIKE f1 || '%'
>>> ?
>>
>> The planner knows that 'foo%' doesn't start with a wildcard.
>>
>>> Either way, it's anchored to the beginning of the string.
>>
>> Not necessarily.
>
> I see, yes, f1 might include a wildcard.

Exactly.

> Is there a way to escape f1 so that wildcards aren't interpreted?
> That's anyway the behavior I need, of course.  And will that help
> the planner?

I don't think there is any way to get that to work in general, and
in particular you are trying to have the right-hand side of the
LIKE treated as having escapes; it would be a lot to ask of the
planner to somehow recognize that part of the result of the
(concatenation) expression should be treated as escaped and part
not.

> What type of index will help the planner here?

Well, with the query as you have it, you might get a trigram index
to be of some help, but I think you might want to give up on LIKE
or regular expressions.  Perhaps you could do a range test
directly, rather than wrangling the wildcards:

  (col >= f1 AND col <= (f1 || 'zzz'))

You would probably want to write a function to calculate that
ending value; I'm just trying to give a rough suggestion here.
The idea is to avoid the danger of wildcards in the f1 values by
constructing the range without scanning for special characters and
basing the test on that.

You could *also* do the LIKE test if desired.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company