The problem is that the above query doesn't use the indexes. The "or" clauses across the outer-join seem to be the culprit. If I rewrite the query as follows, Postgres will use the index:
SELECT *
from PERSON p
where (p.PERSON_ID in (
select p2.PERSON_ID
from TRAVELER.PERSON p2
join TRAVELER.OTHER_NAME pa on p2.PERSON_ID =
pa.PERSON_ID
where lower(p2.SURNAME) = 'duck' and
lower(pa.FORENAME) = 'donald'
) or
p.PERSON_ID in (
select p2.PERSON_ID
from TRAVELER.PERSON p2
join TRAVELER.OTHER_NAME pa on p2.PERSON_ID =
pa.PERSON_ID
where lower(pa.SURNAME) = 'duck' and
lower(p2.FORENAME) = 'donald'
) or
p.PERSON_ID in (
select p2.PERSON_ID
from TRAVELER.PERSON p2
where lower(p2.SURNAME) = 'duck' and
lower(p2.FORENAME) = 'donald'
) or
p.PERSON_ID in (
select p2.PERSON_ID
from TRAVELER.OTHER_NAME pa
where lower(pa.SURNAME) = 'duck' and
lower(pa.FORENAME) = 'donald'
))
order by p.PERSON_ID asc;
So my question is this: Is there a way to get the Postgres optimizer "rewrite" the query execution plan to use the equivalent, but much more efficient latter form?
And before you ask; yes, there are better ways of writing this query. But we're dealing with Java developers and Hibernate here. It's a legacy system, and the policy is to avoid hand-written SQL, so for the moment let's not go down that rabbit hole, and focus on the issue of what the optimizer can and cannot do.