What I can say is that hibernate has "exists" in both HQL and criteria API (e.g. see http://www.cereslogic.com/pages/2008/09/22/hibernate-criteria-subqueries-exists/ for criteria). So, may be it's easier for you to tune your hibernate query to use exists
2013/4/30 Mark Hampton <m...@cleverdba.com> > I have a Hibernate-generated query (That's not going to change, so let's > just focus on the Postgres side for now) like this: > > SELECT * > from PERSON p > where p.PERSON_ID in ( > select distinct p2.PERSON_ID > from PERSON p2 > left outer join PERSON_ALIAS pa on > p2.PERSON_ID = pa.PERSON_ID > where (lower(p1.SURNAME) = 'duck' or > lower(pa.SURNAME) = 'duck') and > (lower(p1.FORENAME) = 'donald' or > lower(pa.FORENAME) = 'donald') > ) > order by p.PERSON_ID asc; > > There are function-based indexes on PERSON and PERSON_ALIAS as follows: > > CREATE INDEX PERSON_FORENAME_LOWER_FBIDX ON PERSON (LOWER(FORENAME) > VARCHAR_PATTERN_OPS); > CREATE INDEX PERSON_SURNAME_LOWER_FBIDX ON PERSON (LOWER(SURNAME) VARCHAR > _PATTERN_OPS); > CREATE INDEX PERSON_ALIAS_FORENAME_LOWER_FBIDX ON PERSON_ALIAS > (LOWER(FORENAME) VARCHAR_PATTERN_OPS); > CREATE INDEX PERSON_ALIAS_SURNAME_LOWER_FBIDX ON PERSON_ALIAS > (LOWER(SURNAME) VARCHAR_PATTERN_OPS); > > 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. > -- Best regards, Vitalii Tymchyshyn
