Printing window function OVER clauses in EXPLAIN

Tom Lane <tgl@sss.pgh.pa.us>

From: Tom Lane <tgl@sss.pgh.pa.us>
To: pgsql-hackers@lists.postgresql.org
Date: 2025-03-08T21:39:15Z
Lists: pgsql-hackers

Attachments

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

Commits

  1. Improve EXPLAIN's display of window functions.