> it seems like the subquery is getting re-run on every single row of the task queue table

Y'all might find the term "correlated subquery" helpful here.

When I want them, I always get them. Sometimes I get them when I don't want them. Sometimes it's even my fault, not the result of the query planner looking at me sideways.