Re: review: More frame options in window functions

Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com> writes:
> 2010/1/17 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>> That's broken, whether it passes regression tests or not. Not
>> canonicalizing will mean that you fail to recognize equality to
>> canonicalized pathkeys, and thus for example execute unnecessary
>> sorts.

> So why did you leave "canonicalize" argument in
> make_pathkeys_for_window()? I thought you'd thought it would be needed
> false in the future.

No, it has to be false in calls made before query_planner runs and true
afterwards. There's no flexibility there.

> In a RANGE offset mode query, for example:

> SELECT sum(ten) over (PARTITION BY four ORDER BY four RANGE BETWEEN 2
> PRECEDING AND 1 PRECEDING) FROM tenk1

> the frame is determined as "from the first row which has <four> value
> - 2 to the last row which has <four> value - 1" and executor should
> know <four> value *is* the sort column even if the column is not
> actually significant. But the planner removes that information.

Maybe we're just talking past each other. My point is that the planner
should record the fact that four is the sort column someplace where the
executor can find it easily. AFAICS that doesn't mean it can't be the
canonicalized form of the sort key. If a column is dropped out of the
canonical sort key then it's simply redundant, and hence not relevant to
determining the range.

regards, tom lane