Re: Planning aggregates which require sorted or distinct

"Gavin Sherry" <swm(at)alcove(dot)com(dot)au> writes:

> Wow. What a coincidence! Windows are slightly more complex though. As you
> probably know, there are two ways of specifying the window frame: by an
> absolute number of rows (ROWS N PRECEDING, for example); or, by a 'range'
> (RANGE N PRECEDING), where the range, in the case of 'preceding', is
> determined by subtracted the range parameter from the value of the current
> field -- i.e., the window attribute.

Actually I think there's a third distinct subcase here as well. While in
theory "RANGE UNBOUNDED PRECEDING" could be done using the same logic as N
PRECEDING I think it makes more sense to treat it as a distinct case because
it is amenable to better plans.

For RANGE N PRECEDING in the general case we need to reapply the window
aggregates over the entire window partition for every record. There may be a
property some window aggregate functions have of being able to "remove" the
effects of an state transition which allows for an optimization (for example
avg() which keeps a running sum and count can easily subtract the old tuple
being aged out of the window). But not all aggregates can do this. RANK() I
believe will need to resort the entire window partition for every record.

However for RANGE UNBOUNDED PRECEDING we can apply a different plan. Keep the
state variable for each window aggregate around for the entire time. For each
record apply the state transition function then apply the FINAL function to
generate the result for that record but keep the state variable as it was for
the next record.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com