Re: Printing window function OVER clauses in EXPLAIN

Hello

Would it be possible and make sense to use notation of explicit WINDOW
clauses, for cases where multiple window functions invoke identical
window definitions? I'm thinking of something like

explain verbose SELECT
empno,
depname,
row_number() OVER testwin rn,
rank() OVER testwin rnk,
count(*) OVER testwin cnt
FROM empsalary
window testwin as
(PARTITION BY depname ORDER BY enroll_date ROWS BETWEEN
UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);

for which, with the patch, we'd get this

QUERY PLAN
─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
WindowAgg (cost=74.64..101.29 rows=1070 width=68)
Output: empno, depname, row_number() OVER (PARTITION BY depname ORDER BY enroll_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), rank() OVER (PARTITION BY depname ORDER BY enroll_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), count(*) OVER (PARTITION BY depname ORDER BY enroll_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), enroll_date
-> Sort (cost=74.54..77.21 rows=1070 width=44)
Output: depname, enroll_date, empno
Sort Key: empsalary.depname, empsalary.enroll_date
-> Seq Scan on pg_temp.empsalary (cost=0.00..20.70 rows=1070 width=44)
Output: depname, enroll_date, empno
(7 filas)

which is pretty ugly to read and requires careful tracking to verify
that they're all defined on the same window. Previously, we just get

QUERY PLAN
──────────────────────────────────────────────────────────────────────────────────────────────────
WindowAgg (cost=74.64..101.29 rows=1070 width=68)
Output: empno, depname, row_number() OVER (?), rank() OVER (?), count(*) OVER (?), enroll_date
-> Sort (cost=74.54..77.21 rows=1070 width=44)
Output: depname, enroll_date, empno
Sort Key: empsalary.depname, empsalary.enroll_date
-> Seq Scan on pg_temp.empsalary (cost=0.00..20.70 rows=1070 width=44)
Output: depname, enroll_date, empno
(7 filas)

so it didn't matter.

I'd imagine something like

QUERY PLAN
─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
Window testwin AS (PARTITION BY depname ORDER BY enroll_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
WindowAgg (cost=74.64..101.29 rows=1070 width=68)
Output: empno, depname, row_number() OVER testwin, rank() OVER testwin, count(*) OVER testwin, enroll_date
-> Sort (cost=74.54..77.21 rows=1070 width=44)
Output: depname, enroll_date, empno
Sort Key: empsalary.depname, empsalary.enroll_date
-> Seq Scan on pg_temp.empsalary (cost=0.00..20.70 rows=1070 width=44)
Output: depname, enroll_date, empno
(7 filas)

I imagine this working even if the user doesn't explicitly use a WINDOW
clause, if only because it makes the explain easier to read, and it's
much clearer if the user specifies two different window definitions.
So with David Johnston's example, something like

Window window1 AS (PARTITION BY depname ORDER BY enroll_date ROWS UNBOUNDED PRECEDING)
Window window2 AS (PARTITION BY depname ORDER BY enroll_date RANGE BETWEEN CURRENT ROW AND CURRENT ROW)
WindowAgg
Output: empno, depname, (row_number() OVER window1), rank() OVER window1, count(*) OVER window2, enroll_date
-> WindowAgg
Output: depname, enroll_date, empno, row_number() OVER window1, rank() OVER window1
-> Sort
Output: depname, enroll_date, empno
Sort Key: empsalary.depname, empsalary.enroll_date
-> Seq Scan on pg_temp.empsalary
Output: depname, enroll_date, empno

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

--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
Thou shalt study thy libraries and strive not to reinvent them without
cause, that thy code may be short and readable and thy days pleasant
and productive. (7th Commandment for C Programmers)