Is postgres able to share sorts required by common partition window functions? - Mailing list pgsql-general

Hi all,

I'm trying to optimize the following query on postgres 11.6 (running on Aurora)
select distinct
  c1,
  first_value(c2) OVER (PARTITION BY c1 order by c2) AS c2,
  first_value(c3) OVER (PARTITION BY c1 order by c3) AS c3,
  first_value(c4) OVER (PARTITION BY c1 order by c4) AS c4
from  
  t;

 

From the explain plan (attached at the end of the email) I see that postgresql is doing several sorts one per window function and one for the distinct that seems ok.

However all the window functions being on the same partition I would have expected postgresql to "share" a preliminary sort on c1 that would then be useful to reduce the work on all window functions but it doesn't.

I even created an index on c1 hoping that postgresql would be able to use it in order to minimize the cost of the sorts but I couldn't make it use it.

Is there something I am missing?

You can find below a script to set up a table and data to reproduce as well as the explain plan.