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
- v1-show-window-clauses-in-EXPLAIN.patch (text/x-diff) patch v1
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
-
Improve EXPLAIN's display of window functions.
- 8b1b342544b6 18.0 landed