Re: Printing window function OVER clauses in EXPLAIN

=?utf-8?Q?=C3=81lvaro?= Herrera <alvherre(at)alvh(dot)no-ip(dot)org> writes:
> Would it be possible and make sense to use notation of explicit WINDOW
> clauses, for cases where multiple window functions invoke identical
> window definitions?

There's something to be said for that. We would have to assign
made-up names to windows that didn't have one. But then the
output might look like

WindowAgg (...)
Output: empno, depname, row_number() OVER (window1), rank() OVER (window1), count(*) OVER (window1), enroll_date
Window: window1 = PARTITION BY depname ORDER BY enroll_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

which is surely a lot nicer than 3x repetitions of the window spec.

After reading David's mail I'd been thinking of something like

WindowAgg (...)
Output: empno, depname, row_number() OVER (...), rank() OVER (...), count(*) OVER (...), enroll_date
Window: PARTITION BY depname ORDER BY enroll_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

which is shorter but vaguer. In particular, if you have more than one
WindowAgg, then with explicit names we'd have something like

WindowAgg (...)
Output: empno, depname, row_number() OVER (window1), rank() OVER (window1), (count(*) OVER (window2)), enroll_date
Window: window1 = PARTITION BY depname ORDER BY enroll_date ROWS UNBOUNDED PRECEDING
WindowAgg (...)
Output: empno, depname, count(*) OVER (window2), enroll_date
Window: window2 = PARTITION BY depname ORDER BY enroll_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

With "..." that would be confusing as heck to someone who didn't
understand the nuances of the extra parentheses.

> (Hmm, not sure if the Window clauses would be top-level or attached to
> each WindowAgg in its own level.)

IMO the obvious thing is to attach each WindowClause to the WindowAgg
node that implements it.

I'll go try to code this up.

regards, tom lane