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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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
rows.

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
inter-relationship.

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

Responses

Browse pgsql-hackers by date

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