Re: Complex filters -> Bad row estimates -> bad query plan

-- 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.