Re: CTE Materialization

On Tue, Dec 7, 2021 at 6:40 PM Дмитрий Иванов <firstdismay(at)gmail(dot)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.