Re: Planning aggregates which require sorted or distinct

On Sat, 2007-01-20 at 14:20 +0000, Simon Riggs wrote:
> On Sat, 2007-01-20 at 23:54 +1100, Gavin Sherry wrote:

> > 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.
>
> Sure. The MJ situation is to have the MergeJoin node call a Materialize
> node which calls the tuplestore functions. The MJ node does all the
> clever stuff, which includes a variable size buffer according to the
> values arriving. Seems OK to have a Window node that does its own brand
> of clever stuff, while the Materialize node just does whats its told in
> managing the tuplestore.
>
> Rows type calls can do a read next/mark at same time, so they always
> maintain a fixed lead/lag as they go.
>
> Range type calls can do a read, then some processing to determine if it
> should mark yet, just as MJ does. Or at least we can support an
> additional call to make RangeWindowStart hunt for the appropriate row to
> set as the end of the window and then read forward until the end of the
> range is found.

Gavin,

Following earlier thoughts, I thought I'd make some notes about how the
new tuplestore changes could be used for preparing Windows for use with
Windowed aggregate functionality.

The new tuplestore commands are
tuplestore_moveBufferStartForwards() == mark
tuplestore_rewindToBufferStart() == restore

(happy to change the names...)

They'd be used something like this, in simplified pseudo code that would
be executed by a Window? node.

With ROWS, for each new tuple:
-----------------------------
tuplestore_puttupleslot() // add one new tuple
tuplestore_moveBufferStartForwards(++markpos) // move start forwards one

// position Window for processing
tuplestore_rewindToBufferStart() to position Window for processing

With RANGE, for each new row:
----------------------------
// locate new range start
tuplestore_rewindToBufferStart()
...step forward until start of new range found...
tuplestore_moveBufferStartForwards() at that point

// locate new range end
while (!end-of-new-range)
{
if (!eof)
tuplestore_gettuple() // move forwards
else
tuplestore_puttupleslot() // add new tuple
}

// position Window for processing
tuplestore_rewindToBufferStart()

(The above is simplified to remove boundary conditions.)

So AFAICS, there's not actually any additional work to do, over and
above the changes I'm working on to support a circular buffer in
tuplestore for merge joins.

The above makes the assumption that for RANGE windows, the range start
of tuple i+1 is always greater than or equal to the range start of tuple
i - could be very interesting if its not true.

Anyway, some options for you to consider, at least.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com