Re: Avoid extra Sort nodes between WindowAggs when sorting can be reused

From: Daniel Gustafsson <daniel(at)yesql(dot)se>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Re: Avoid extra Sort nodes between WindowAggs when sorting can be reused
Date: 2018-06-12 13:10:50
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

> On 30 May 2018, at 18:19, Daniel Gustafsson <daniel(at)yesql(dot)se> wrote:
> 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.

Attached is a rebased v2 addressing off-list review comments and including a
test. Parking this in the commitfest.

cheers ./daniel

Attachment Content-Type Size
window_prefix_sort-v2.patch application/octet-stream 9.9 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Ashutosh Bapat 2018-06-12 13:22:40 Re: Remove mention in docs that foreign keys on partitioned tables are not supported
Previous Message Andrew Dunstan 2018-06-12 12:49:58 Re: late binding of shared libs for C functions