Thread
Commits
-
Improve EXPLAIN's display of window functions.
- 8b1b342544b6 18.0 landed
-
Printing window function OVER clauses in EXPLAIN
Tom Lane <tgl@sss.pgh.pa.us> — 2025-03-08T21:39:15Z
While thinking about the discussion at [1], I got annoyed about how EXPLAIN still can't print a useful description of window functions' window clauses (it just emits "OVER (?)"). The difficulty is that there's no access to the original WindowClause anymore; else we could re-use the ruleutils.c code that dumps those. It struck me that we could fix that by making WindowAgg plan nodes keep the WindowClause as a sub-node, replacing their current habit of having most of the WindowClause's fields as loose fields in the WindowAgg node. A little bit later I had a working patch, as attached. I think this data structure change is about a wash for performance outside of EXPLAIN. It requires a few extra indirections during ExecInitWindowAgg, but there's no change in code used during the plan's execution. One thing that puzzled me a bit is that many of the outputs show "ROWS UNBOUNDED PRECEDING" in window functions where that definitely wasn't in the source query. Eventually I realized that that comes from window_row_number_support() and cohorts optimizing the query. While this isn't wrong, I suspect it will cause a lot of confusion and questions. I wonder if we should do something to hide the change? regards, tom lane [1] https://www.postgresql.org/message-id/flat/CABde6B5va2wMsnM79u_x%3Dn9KUgfKQje_pbLROEBmA9Ru5XWidw%40mail.gmail.com
-
Re: Printing window function OVER clauses in EXPLAIN
David Rowley <dgrowleyml@gmail.com> — 2025-03-09T01:15:25Z
On Sun, 9 Mar 2025 at 10:39, Tom Lane <tgl@sss.pgh.pa.us> wrote: > One thing that puzzled me a bit is that many of the outputs > show "ROWS UNBOUNDED PRECEDING" in window functions where that > definitely wasn't in the source query. Eventually I realized > that that comes from window_row_number_support() and cohorts > optimizing the query. While this isn't wrong, I suspect it > will cause a lot of confusion and questions. I wonder if we > should do something to hide the change? I suspect it might be more confusing if we were to show the user the original frame options. Isn't EXPLAIN meant to be a window into the plan that's been or would be executed? I think it would be misleading to display something different to what will be executed. Take the following case, for example: create table t1 as select 1 as a from generate_Series(1,1000000); vacuum freeze analyze t1; (btw, the patch is giving me ERROR: bogus varno: -3 with EXPLAIN VERBOSE on this) select a,row_number() over (order by a) from t1 limit 1; Time: 0.246 ms This performs a "ROWS UNBOUNDED PRECEDING" WindowAgg. If we add another WindowFunc with the same frame options: select a,row_number() over (order by a),sum(a) over (order by a) from t1 limit 1; Time: 159.420 ms This one performs a "RANGE UNBOUNDED PRECEDING" WindowAgg. A user might be surprised that the performance drops to this degree just by adding the SUM() aggregate using the same frame options as the row_number(). If we show the honest frame options as decided by the planner, then the performance drop is easier to understand. If too many users are confused with why the frame options aren't what they asked for, then maybe we'll need to document the optimisation. I think the planner does plenty of other things that change what's shown in EXPLAIN. Constant folding is one example. Partition pruning is another. Maybe those two are easier to understand than window agg frame options, however. David
-
Re: Printing window function OVER clauses in EXPLAIN
David G. Johnston <david.g.johnston@gmail.com> — 2025-03-09T02:31:57Z
On Sat, Mar 8, 2025 at 6:15 PM David Rowley <dgrowleyml@gmail.com> wrote: > On Sun, 9 Mar 2025 at 10:39, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > One thing that puzzled me a bit is that many of the outputs > > show "ROWS UNBOUNDED PRECEDING" in window functions where that > > definitely wasn't in the source query. Eventually I realized > > that that comes from window_row_number_support() and cohorts > > optimizing the query. While this isn't wrong, I suspect it > > will cause a lot of confusion and questions. I wonder if we > > should do something to hide the change? > > I suspect it might be more confusing if we were to show the user the > original frame options. Isn't EXPLAIN meant to be a window into the > plan that's been or would be executed? I think it would be misleading > to display something different to what will be executed. > > Looking at this example: SELECT empno, depname, row_number() OVER (PARTITION BY depname ORDER BY enroll_date) rn, rank() OVER (PARTITION BY depname ORDER BY enroll_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) rnk, count(*) OVER (PARTITION BY depname ORDER BY enroll_date RANGE BETWEEN CURRENT ROW AND CURRENT ROW) cnt FROM empsalary; The new output is: WindowAgg Output: empno, depname, (row_number() OVER (PARTITION BY depname ORDER BY enroll_date ROWS UNBOUNDED PRECEDING)), (rank() OVER (PARTITION BY depname ORDER BY enroll_date ROWS UNBOUNDED PRECEDING)), count(*) OVER (PARTITION BY depname ORDER BY enroll_date RANGE BETWEEN CURRENT ROW AND CURRENT ROW), enroll_date -> WindowAgg Output: depname, enroll_date, empno, row_number() OVER (PARTITION BY depname ORDER BY enroll_date ROWS UNBOUNDED PRECEDING), rank() OVER (PARTITION BY depname ORDER BY enroll_date ROWS UNBOUNDED PRECEDING) -> Sort Output: depname, enroll_date, empno Sort Key: empsalary.depname, empsalary.enroll_date -> Seq Scan on pg_temp.empsalary Output: depname, enroll_date, empno It is kinda annoying that row_number and rank have their entire expression output twice when the computation only happens once. But that is outside the scope; just making an observation. It just becomes even worse when we fill in the details. As for the optimization, any reason to not just show that it was done? In optimize_window_clauses arrange to save the existing_wc somewhere on the relevant window functions then, in explain, output something like: -> WindowAgg Output: depname, enroll_date, empno, row_number() OVER (...), rank() OVER (...) Reframed: row_number() from (default) RANGE => ROWS (I'm unsure whether we can write "default" here though, it isn't critical.) Reframed: rank() from UNBOUNDED FOLLOWING => CURRENT ROW (I initially put the entire frame clause, without omitting default frame_end, there but then figured it defeated the point. We should only show those elements (type, start, end) that actually are different between the parsed query and what gets executed.) Which does bring up the point, to what extent should the explain output rely on defaults versus being explicit? We are omitting frame_end of CURRENT ROW generally here. David J. -
Re: Printing window function OVER clauses in EXPLAIN
Alvaro Herrera <alvherre@alvh.no-ip.org> — 2025-03-09T14:12:25Z
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) -
Re: Printing window function OVER clauses in EXPLAIN
Tom Lane <tgl@sss.pgh.pa.us> — 2025-03-09T15:45:41Z
=?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 -
Re: Printing window function OVER clauses in EXPLAIN
Tom Lane <tgl@sss.pgh.pa.us> — 2025-03-09T22:19:49Z
I wrote: > I'll go try to code this up. OK, here's v2 done like that. I do like this output better. I backed off the idea of putting the WindowClause as such into the plan, partly because I didn't feel like debugging the setrefs.c problem that David discovered upthread. This way does require a bit more code, but I think it's less likely to have bugs. A couple of notes: * I made the Window: plan annotation come out unconditionally. We could alternatively print it only in VERBOSE mode, which would greatly reduce the number of regression test diffs. However, it seems fairly comparable to the sort keys of a Sort node or the group keys of a Group node, which we print unconditionally. Also, there are cases where a higher-level node unconditionally prints a reference to a window function output, which would mean that that output's reference to "windowN" would have no referent in the displayed data. * In passing, I editorialized on the order in which the Run Condition annotation comes out: case T_WindowAgg: + show_window_def(castNode(WindowAggState, planstate), ancestors, es); show_upper_qual(plan->qual, "Filter", planstate, ancestors, es); + show_upper_qual(((WindowAgg *) plan)->runConditionOrig, + "Run Condition", planstate, ancestors, es); if (plan->qual) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); - show_upper_qual(((WindowAgg *) plan)->runConditionOrig, - "Run Condition", planstate, ancestors, es); show_windowagg_info(castNode(WindowAggState, planstate), es); break; It seemed quite weird to me to have the Run Condition plan property come out in the middle of properties that only appear in EXPLAIN ANALYZE mode. Maybe there's a reason for this other than "people added new properties at the end", but I don't see it. regards, tom lane -
Re: Printing window function OVER clauses in EXPLAIN
David Rowley <dgrowleyml@gmail.com> — 2025-03-10T00:14:49Z
On Mon, 10 Mar 2025 at 11:19, Tom Lane <tgl@sss.pgh.pa.us> wrote: > OK, here's v2 done like that. I do like this output better. > I backed off the idea of putting the WindowClause as such > into the plan, partly because I didn't feel like debugging > the setrefs.c problem that David discovered upthread. > This way does require a bit more code, but I think it's less > likely to have bugs. This output is much nicer. The patch looks good to me. What are your thoughts on being a bit more brief with the naming and just prefix with "w" instead of "window"? Looking at window.out, I see that the EXPLAIN output does become quite a bit wider than before. I favour the idea of saving a bit of space. There is an example in src/sgml/advanced.sgml that has "OVER w", so it does not seem overly strange to me to name them "w1", "w2", etc. > * In passing, I editorialized on the order in which the Run Condition > annotation comes out: > > case T_WindowAgg: > + show_window_def(castNode(WindowAggState, planstate), ancestors, es); > show_upper_qual(plan->qual, "Filter", planstate, ancestors, es); > + show_upper_qual(((WindowAgg *) plan)->runConditionOrig, > + "Run Condition", planstate, ancestors, es); > if (plan->qual) > show_instrumentation_count("Rows Removed by Filter", 1, > planstate, es); > - show_upper_qual(((WindowAgg *) plan)->runConditionOrig, > - "Run Condition", planstate, ancestors, es); > show_windowagg_info(castNode(WindowAggState, planstate), es); > break; > > It seemed quite weird to me to have the Run Condition plan property > come out in the middle of properties that only appear in EXPLAIN > ANALYZE mode. Maybe there's a reason for this other than "people > added new properties at the end", but I don't see it. I did it that way because "Rows Removed by Filter" is a property of "Filter", so it makes sense to me for those to be together. It doesn't make sense to me to put something unrelated between them. If you look at BitmapHeapScan output, this keeps the related outputs together, i.e: -> Parallel Bitmap Heap Scan on ab (cost=111.20..82787.64 rows=1 width=8) (actual time=172.498..172.499 rows=0.00 loops=3) Recheck Cond: (a = 1) Rows Removed by Index Recheck: 705225 Filter: (b = 3) Rows Removed by Filter: 3333 What you're proposing seems equivalent to if we did it like: -> Parallel Bitmap Heap Scan on ab (cost=111.20..82787.64 rows=1 width=8) (actual time=172.498..172.499 rows=0.00 loops=3) Recheck Cond: (a = 1) Filter: (b = 3) Rows Removed by Index Recheck: 705225 Rows Removed by Filter: 3333 David -
Re: Printing window function OVER clauses in EXPLAIN
Tom Lane <tgl@sss.pgh.pa.us> — 2025-03-10T01:13:28Z
David Rowley <dgrowleyml@gmail.com> writes: > What are your thoughts on being a bit more brief with the naming and > just prefix with "w" instead of "window"? Looking at window.out, I see > that the EXPLAIN output does become quite a bit wider than before. I > favour the idea of saving a bit of space. There is an example in > src/sgml/advanced.sgml that has "OVER w", so it does not seem overly > strange to me to name them "w1", "w2", etc. OK by me, any objections elsewhere? >> * In passing, I editorialized on the order in which the Run Condition >> annotation comes out: > I did it that way because "Rows Removed by Filter" is a property of > "Filter", so it makes sense to me for those to be together. It doesn't > make sense to me to put something unrelated between them. Hmm, OK. Do you think it could be sensible to put Run Condition before Filter, then? On the same grounds of "keeping related things together", it could be argued that Run Condition is related to the Window property. Also, the Run Condition acts before the Filter does, if I've got my head screwed on straight. regards, tom lane
-
Re: Printing window function OVER clauses in EXPLAIN
David Rowley <dgrowleyml@gmail.com> — 2025-03-10T01:17:35Z
On Mon, 10 Mar 2025 at 14:13, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Hmm, OK. Do you think it could be sensible to put Run Condition > before Filter, then? On the same grounds of "keeping related > things together", it could be argued that Run Condition is > related to the Window property. Also, the Run Condition acts > before the Filter does, if I've got my head screwed on straight. Yes, directly after the "Window" property makes sense for the reason you stated. Thanks for thinking of that. David
-
Re: Printing window function OVER clauses in EXPLAIN
Alvaro Herrera <alvherre@alvh.no-ip.org> — 2025-03-10T06:08:39Z
On 2025-Mar-09, Tom Lane wrote: > David Rowley <dgrowleyml@gmail.com> writes: > > What are your thoughts on being a bit more brief with the naming and > > just prefix with "w" instead of "window"? Looking at window.out, I see > > that the EXPLAIN output does become quite a bit wider than before. I > > favour the idea of saving a bit of space. There is an example in > > src/sgml/advanced.sgml that has "OVER w", so it does not seem overly > > strange to me to name them "w1", "w2", etc. > > OK by me, any objections elsewhere? WFM. -- Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
-
Re: Printing window function OVER clauses in EXPLAIN
Tom Lane <tgl@sss.pgh.pa.us> — 2025-03-10T16:18:29Z
=?utf-8?Q?=C3=81lvaro?= Herrera <alvherre@alvh.no-ip.org> writes: > On 2025-Mar-09, Tom Lane wrote: >> David Rowley <dgrowleyml@gmail.com> writes: >>> What are your thoughts on being a bit more brief with the naming and >>> just prefix with "w" instead of "window"? >> OK by me, any objections elsewhere? > WFM. Here's a hopefully-final v3 that makes the two changes discussed. Now with a draft commit message, too. I looked for documentation examples that needed updates, but there don't seem to be any. Our documentation of EXPLAIN output is mighty thin anyway. I don't want to try to improve that situation as part of this patch. regards, tom lane
-
Re: Printing window function OVER clauses in EXPLAIN
David Rowley <dgrowleyml@gmail.com> — 2025-03-11T01:16:07Z
On Tue, 11 Mar 2025 at 05:18, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Here's a hopefully-final v3 that makes the two changes discussed. > Now with a draft commit message, too. Looks good to me. The only minor points I noted down while reviewing were 1) name_active_windows()'s newname variable could be halved in size and, 2) explain.sql's new test could name the window "w1" instead of "w" to exercise the name selection code a bit better. Both are minor points, but I thought I'd mention them anyway. David
-
Re: Printing window function OVER clauses in EXPLAIN
Tom Lane <tgl@sss.pgh.pa.us> — 2025-03-11T15:21:00Z
David Rowley <dgrowleyml@gmail.com> writes: > The only minor points I noted down while reviewing were 1) > name_active_windows()'s newname variable could be halved in size and, > 2) explain.sql's new test could name the window "w1" instead of "w" to > exercise the name selection code a bit better. Both are minor points, > but I thought I'd mention them anyway. Thanks, pushed with those points addressed. regards, tom lane