What type of index do I need for this JOIN?

From: Robert James <srobertjames(at)gmail(dot)com>
To: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: What type of index do I need for this JOIN?
Date: 2013-08-13 22:05:26
Message-ID: CAGYyBgjO1ci8Di7G7nqpR0Jwa+Oywwb4Hs-1QeDBaCgknrHpdA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

(My goal is to join on a substring starting from the first character)

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Merlin Moncure 2013-08-13 22:13:53 Re: [GENERAL] Possible bug with row_to_json
Previous Message Robert James 2013-08-13 22:02:43 Materializing a view by hand