Re: Hash Anti Join performance degradation

panam <panam@gmx.net> 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