alt Hacker NewsThat is correct and our curiosities are very much aligned here. I wrote this post in passing on a bus, so it def lacks some critical detail :D. I can very much solve this with a AS MATERIALIZED like you pointed, and as also mentioned in - https://news.ycombinator.com/item?id=43886522
I will look into a more thought out post perhaps. Thanks for sharing all the comments and feedback. Really enjoying the discussions.
Also, I believe using an `ORDER BY` is only as good as `=` where you are giving the planner a hint on the route to choose, but it doesn't necessarily solve the issue like using `AS MATERIALIZED` does. The issue being - it should only delete the number of rows and return them as authored by the value to `LIMIT`. So, using `ORDER BY` and `LIMIT`, will ensure the right set of IDs are returned, but it's not stopping the sub query from getting executed more than once.
It is still a good rule of those to pair your LIMITs with ORDER, however, yeah.