Skip site navigation (1) Skip section navigation (2)

Basic Requirements for SQL Window Functions

From: Simon Riggs <simon(at)2ndquadrant(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Basic Requirements for SQL Window Functions
Date: 2004-11-29 15:16:57
Message-ID: 1101741416.2963.246.camel@localhost.localdomain (view raw or whole thread)
Lists: pgsql-hackers
An example of a window function is RANK or a moving AVG, though also
include ROW_NUMBER or CUME_DIST. They are a different kind of aggregate
introduced by/included in SQL:2003, which require a "sliding window" of

The SQL window functions seem to require an ordering for most of their
operations. It is possible that that could be provided by a sort node in
the execution plan. It also seems that this might have some effect on
the MAX/MIN handling issue - I raise this now in case there is some

I've started another thread to avoid opening Pandora's box again, but..

Earlier discussions around MAX/MIN handling mention this....

On Thu, 2004-11-11 at 15:24, Tom Lane wrote: 
> "Zeugswetter Andreas DAZ SD" <ZeugswetterA(at)spardat(dot)at> writes:
> >> How are you planning to represent the association between MIN/MAX and
> >> particular index orderings in the system catalogs?
> > Don't we already have that info to decide whether an index handles 
> > an "ORDER BY" without a sort node ?
> We know how to determine that an index matches an ORDER BY clause.
> But what has an aggregate called MAX() got to do with ORDER BY?  Magic
> assumptions about operators named "<" are not acceptable answers; there
> has to be a traceable connection in the catalogs.
> As a real-world example of why I won't hold still for hard-wiring this:
> a complex-number data type might have btree opclasses allowing it to be
> sorted either by real part or by absolute value.  One might then define
> max_real() and max_abs() aggregates on the type.  It should be possible
> to optimize such aggregates the same way as any other max() aggregate.

Are we OK to say that window functions will always need a sort node?

Is there an optimization that anyone can see that might lead us away
from that requirement, and if so do we need to solve the problem
described above?

Best Regards, Simon Riggs


pgsql-hackers by date

Next:From: Tom LaneDate: 2004-11-29 15:37:32
Subject: Re: Stopgap solution for table-size-estimate updatingproblem
Previous:From: Christopher Kings-LynneDate: 2004-11-29 14:48:40
Subject: Re: Error: column "nsptablespace" does not exist

Privacy Policy | About PostgreSQL
Copyright © 1996-2015 The PostgreSQL Global Development Group