Re: CTE Materialization

On Tue, Dec 7, 2021 at 6:40 PM Дмитрий Иванов <firstdismay@gmail.com> wrote:

I beg your pardon.
The problem is more or less clear to me, but the solution is not. What does the "hack is to add an "offset 0" to the query" suggest? Thank you.

A subquery with a LIMIT clause cannot have where clause expressions in upper parts of the query tree pushed down it without changing the overall query result - something the planner is not allowed to do.  For the hack, since adding an actual LIMIT clause doesn't make sense you omit it, but still add the related OFFSET clause so the planner still treats the subquery as a LIMIT subquery.  And since you don't want to skip any rows you specify 0 for the offset.

David J.