Re: Printing window function OVER clauses in EXPLAIN - Mailing list pgsql-hackers

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.