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. http://www.postgresql.org/docs/current/static/indexes-opclass.html http://www.postgresql.org/docs/current/static/indexes-types.html "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." -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
