From: Tom Lane <tgl@sss.pgh.pa.us>
To: =?utf-8?Q?=C3=81lvaro?= Herrera <alvherre@alvh.no-ip.org>
cc: pgsql-hackers@lists.postgresql.org
Subject: Re: Printing window function OVER clauses in EXPLAIN
In-reply-to: <202503091412.mzlgmzmmzwim@alvherre.pgsql>
References: <202503091412.mzlgmzmmzwim@alvherre.pgsql>
Comments: In-reply-to =?utf-8?Q?=C3=81lvaro?= Herrera
 <alvherre@alvh.no-ip.org>
	message dated "Sun, 09 Mar 2025 15:12:25 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <279723.1741535141.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Sun, 09 Mar 2025 11:45:41 -0400
Message-ID: <279724.1741535141@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/279724.1741535141%40sss.pgh.pa.us>
Precedence: bulk

=3D?utf-8?Q?=3DC3=3D81lvaro?=3D Herrera <alvherre@alvh.no-ip.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 (wind=
ow1), count(*) OVER (window1), enroll_date
    Window: window1 =3D PARTITION BY depname ORDER BY enroll_date ROWS BET=
WEEN 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 (...), co=
unt(*) OVER (...), enroll_date
    Window: PARTITION BY depname ORDER BY enroll_date ROWS BETWEEN UNBOUND=
ED 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 (wind=
ow1), (count(*) OVER (window2)), enroll_date
    Window: window1 =3D PARTITION BY depname ORDER BY enroll_date ROWS UNB=
OUNDED PRECEDING
    WindowAgg  (...)
      Output: empno, depname, count(*) OVER (window2), enroll_date
        Window: window2 =3D 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