Avoid extra Sort nodes between WindowAggs when sorting can be reused

Daniel Gustafsson <daniel@yesql.se>

From: Daniel Gustafsson <daniel@yesql.se>
To: pgsql-hackers@lists.postgresql.org
Date: 2018-05-30T16:19:48Z
Lists: pgsql-hackers

Attachments

Currently, we can only reuse Sort nodes between WindowAgg nodes iff the
partitioning and ordering clauses are identical.  If a window Sort node
sortorder is a prefix of another window, we could however reuse the Sort node
to hopefully produce a cheaper plan.  In src/backend/optimizer/plan/planner.c
there is a comment alluding to this:

        * ...
        *
        * There is room to be much smarter here, for example detecting whether
        * one window's sort keys are a prefix of another's (so that sorting for
        * the latter would do for the former), or putting windows first that
        * match a sort order available for the underlying query.  For the moment
        * we are content with meeting the spec.
        */

The attached patch takes a stab at implementing the sorting on partitioning/
ordering prefix, inspired by a similar optimization in the Greenplum planner.
In testing the impact on planning time seems quite minimal, or within the error
margin.

cheers ./daniel

Commits

  1. Order active window clauses for greater reuse of Sort nodes.