-- I'm thinking the OFFSET 0 create an optimization barrier that prevents the planner from collapsing that sub-query into the top query, and enforces ordering in the query?
That's my understanding. I think it is an optimizer hint by another name. I used to put things in a CTE (which is always materialized until v12, which will change it to inlined unless keyword MATERIALIZED is included) or I would create a temp table if the dataset is expected to contain many rows such that I can do ANALYZE pg_temp.table_table; so the optimizer has stats to make good decisions.
Note- Replying to messages with a full quote of the conversation below your comment (aka top-posting) is discouraged on these mailing lists. Please quote the portion you are responding to and that's it.