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

On 8/13/13, Craig Ringer <craig(at)2ndquadrant(dot)com> wrote:
> On 08/14/2013 06:05 AM, Robert James wrote:
>> I'm doing a JOIN which is very slow:
>>
>> JOIN t ON t.f1 LIKE (q.f1 || '%')
>>
>> t1 has an INDEX on (f1, f2) which I thought would help for this. But
>> Postgres seems to still use a (very slow) Nested Loop. What type of
>> index would be appropriate for this?
>
> You'll need a text_pattern_ops index.
>
> "The optimizer can also use a B-tree index for queries involving the
> pattern matching operators LIKE and ~ if the pattern is a constant and
> is anchored to the beginning of the string — for example, col LIKE
> 'foo%' or col ~ '^foo', but not col LIKE '%bar'. However, if your
> database does not use the C locale you will need to create the index
> with a special operator class to support indexing of pattern-matching
> queries; see Section 11.9 below."

I'm confused: What's the difference between
col LIKE 'foo%'
and
col LIKE f1 || '%'
?
Either way, it's anchored to the beginning of the string.

And, being that there is a difference (ie the pattern needs to be
constant): How will text_pattern_ops help? The only difference I see
is that text_patterns_ops is locale sensitive, needed for locale other
than C.