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: Avoid extra Sort nodes between WindowAggs when sorting can be reused
Date: 2018-05-30 16:19:48
Message-ID: 124A7F69-84CD-435B-BA0E-2695BE21E5C2@yesql.se
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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

cheers ./daniel

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


Browse pgsql-hackers by date

  From Date Subject
Next Message Jeff Janes 2018-05-30 16:23:10 ALTER CONSTRAINT change action
Previous Message Teodor Sigaev 2018-05-30 16:17:42 Re: POC: GROUP BY optimization