On Sun, 9 Mar 2025 at 10:39, Tom Lane <t...@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
