Re: lag() default value ignored for some window partition depending on table records count?

On Tue, Apr 26, 2022 at 3:59 PM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:

> On Wed, 27 Apr 2022 at 10:48, David G. Johnston
> <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> >
> > On Tue, Apr 26, 2022 at 3:22 PM Loïc Revest <l(dot)revest(at)apc(dot)fr> wrote:
> >>
> >>
> >> - For every of this date/numeric couple within their "window
> partitioning", we need to determine the preceding value, thus the use of
> lag(<value>, 1, 0::numeric), since it's relevant for us here to get the
> very first record of the partition having "0.00" as its preceding value;
> >
> >
> > I'm a bit out of my league on the promises that window functions give
> with respect to qual pushdown and the like (and your nested view
> structure's impact on that): but the fact that your window doesn't do
> partitioning would seem to be a factor here. It is perfectly fine for lag
> to return a null if, in this example, the previous peer_id's LAST
> graph.date has a null value for graph.agg_points and that is computed
> before removing all peer_id values except the one the in the query where
> clause.
>
> There can only be qual pushdown when the column in the qual is present
> in the PARTITION BY clause. In this case, there's no PARTITION BY
> clause, so can't be pushdowns.
>
>
Thanks. I got a bit confused by the working plan having a different
dataset than the not working one: until now when I realized that basically
what that plan shows is that if one does partition by peer_id (in this case
physically, by removing all other records except the peer_id you want) one
gets the correct result. The "Rows removed by filter" part of the explain
output is suppressed if the row count is zero - but it is still above the
window aggregate.

David J.