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
- window_prefix_sort.patch (application/octet-stream) patch
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
-
Order active window clauses for greater reuse of Sort nodes.
- 728202b63cdc 12.0 landed