Re: Planning aggregates which require sorted or distinct
От | Simon Riggs |
---|---|
Тема | Re: Planning aggregates which require sorted or distinct |
Дата | |
Msg-id | 1169549526.3776.482.camel@silverbirch.site обсуждение исходный текст |
Ответ на | Re: Planning aggregates which require sorted or distinct ("Simon Riggs" <simon@2ndquadrant.com>) |
Список | pgsql-hackers |
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
В списке pgsql-hackers по дате отправления: