On 8/13/13, Craig Ringer <craig@xxxxxxxxxxxxxxx> 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
