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

Reply via email to