Comment 1 for bug 76319

At least one of the subselects references its containing context, which means it needs to be executed once for every row in the containing context. The slow query needs to be rewritten. I'd give it a go, but I don't know what the question being asked actually is and would rather not reverse engineer the SQL.