Re: Printing window function OVER clauses in EXPLAIN

On Sun, 9 Mar 2025 at 10:39, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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