panam <panam@xxxxxxx> wrote: > I cannot use it because of the way that query is generated > (by hibernate). > > The (simplyfied) base query is just > > SELECT b.id from box > > the subquery > > (SELECT m1.id FROM message m1 > LEFT JOIN message m2 > ON (m1.box_id = m2.box_id AND m1.id < m2.id ) > WHERE m2.id IS NULL AND m1.box_id = b.id) as lastMessageId > > is due to a hibernate formula (containing more or less plain SQL) > to determine the last message id for that box. It ought to return > just one row, not multiple. So I am constrained to the subquery in > all optimization attemps (I cannot combine them as you did), at > least I do not see how. If you have an idea for a more performant > subquery though, let me know, as this can easily be replaced. Maybe: (SELECT max(m1.id) FROM message m1 WHERE m1.box_id = b.id) -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
