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

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.