Re: more support for various frame types of window functions

On Mon, Nov 9, 2009 at 3:01 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
>> One idea is to take a copy of the state datum after each row. Then,
>> instead of initializing the aggregate from scratch, you can "roll back"
>> to an earlier copied state. It doesn't always help, but might be a part
>> of the solution.
>
> That requires that you know how to copy the aggregate's state.  You do
> not.  (In some cases the aggregate function has extra state besides the
> nominal transition datum...)

That's relatively unusual and usually a bad idea, imho. We could add a
flag to indicate whether that's the case and only do the optimization
if it's not set. It would rarely be set.

We already faced this problem with aggregates over windows with range
unbounded preceding to the current row. I lost track of the current
way of handling the state for those when they're repeatedly finalized.

Actually I had assumed the approach would be different. Instead of
reinitializing the aggregate I had pictured keeping a rolling window
of aggregate states. Ie, if you're doing a rolling average of the last
five values you keep five aggregate states which started 1 row ago, 2
rows ago, ... up to 5 rows ago. You finalize the last one and roll the
others down using the state transition function.

My instinct was that that would be more efficient but working through
it in detail it looks to me like it would do precisely the same work
and have difficulties if the window doesn't fit in work_mem. So
perhaps it's a bad idea, but I wanted to mention it anyways.

--
greg