Hi,
I have a n-to-n association, which is mapped to the database using a link-table: maintable - linktable - attributetable
However, when executing a query to know which main-entry has which attribute which contains memberof ("FROM maintable m where 13 memberof m.collection"), Hibernate generates a really strange and complex query:
Code:
select m.id from maintable s where (13 in (select attributetable.id from linktable l, attributetable a where m.id=l.mainid and l.attributeid=attributetable.id))
Although I've created indices whereever required, Postgresql fails to transform this query into some efficient plan.
Manually I would have written a way simpler query:
Code:
select m.id from maintable m left join linktable l ON (l.mainid=s.id) where l.attributeid=13
Which returns the same results and can be evaluated in next to no time using the index.
Any idea why Hibernate decides to create such a complex query?
Thank you in advance, Clemens
Login
FAQ
Search
