From: Tom Lane <tgl@sss.pgh.pa.us>
To: Álvaro Herrera <alvherre@alvh.no-ip.org>
Cc: pgsql-hackers@lists.postgresql.org
Subject: Re: Printing window function OVER clauses in EXPLAIN
Date: Sun, 09 Mar 2025 11:45:41 -0400
Message-ID: <279724.1741535141@sss.pgh.pa.us> (raw)
In-Reply-To: <202503091412.mzlgmzmmzwim@alvherre.pgsql>
References: <202503091412.mzlgmzmmzwim@alvherre.pgsql>
=?utf-8?Q?=C3=81lvaro?= 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 (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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: tgl@sss.pgh.pa.us, alvherre@alvh.no-ip.org, pgsql-hackers@lists.postgresql.org
Subject: Re: Printing window function OVER clauses in EXPLAIN
In-Reply-To: <279724.1741535141@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox