Re: BUG #16968: Planner does not recognize optimization

On Sun, 16 May 2021 at 02:34, Eugen Konkov <kes-kes(at)yandex(dot)ru> wrote:
> I found a case when `not assigning a ressortgroupref to the whole-row var` cause
> wrong window function calculations.
>
> I use same query. The difference come when I wrap my query into
> function. (see full queries in attachment)
>
> 1.
> SELECT *
> FROM agreement_totals( tstzrange( '2020-07-01', '2020-08-01' ) )
> WHERE agreement_id = 161::int AND (o).period_id = 10::int
>
> 2.
> SELECT *
> sum( .... ) over wagreement
> FROM ....
> WHERE agreement_id = 161::int AND (o).period_id = 10::int
> WINDOW wagreement AS ( PARTITION BY agreement_id )
>
> For first query window function calculates SUM over all agreements,
> then some are filtered out by (o).period_id condition.

This is unrelated to the optimisation that you were asking about before.

All that's going on here is that WHERE is evaluated before SELECT.
This means that your filtering is done before the window functions are
executed. This is noted in the docs in [1]:

> The rows considered by a window function are those of the “virtual table” produced by the query's FROM clause as filtered by its WHERE, GROUP BY, and HAVING clauses if any. For example, a row removed because it does not meet the WHERE condition is not seen by any window function. A query can contain multiple window functions that slice up the data in different ways using different OVER clauses, but they all act on the same collection of rows defined by this virtual table.

If you want to filter rows after the window functions are evaluated
then you'll likely want to use a subquery.

David

[1] https://www.postgresql.org/docs/13/tutorial-window.html