alt Hacker NewsHmm, it seems like the subquery is getting re-run on every single row of the task queue table, which seems like a performance issue in addition to correctness.
Personally, if I were going to put any part of that query in a CTE, it would have been (just) the select, as I generally prefer CTE's to subqueries.
I'm really not sure what motivated the author to create the CTE in the first place, as the final select seems to be essentially the identity function.
Replies
> 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.
"I'm really not sure what motivated the author to create the CTE in the first place, as the final select seems to be essentially the identity function."
We're likely seeing a simplified example. All queries should be able to be embedded in a CTE without integrity loss.