On Wed, 27 Apr 2022 at 10:48, David G. Johnston <david.g.johnston@gmail.com> wrote: > > On Tue, Apr 26, 2022 at 3:22 PM Loïc Revest <l.revest@apc.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.
