Why is this SQL query with subquery very slow?

select b.*
from transaction_batch b
inner join (
    select max(id) as id
    from transaction_batch
    where status_id in (1, 2)
    group by status_id
) bm on b.id = bm.id

The inner query is run seperatly for every row of your table over and over again.

As there is no reference to the outer query in the inner query, I suggest you split those two queries and just insert the results of the inner query in the WHERE clause.