title: Bad optimization/planning on Postgres window-based queries (partition by(, group by?)) - 1000x speedup
body: <p>We are running Postgres 9.3.5. (07/2014)
  We have quite some complex datawarehouse/reporting setup in place (ETL, materialized views, indexing, aggregations, analytical functions, ...).</p>
  
  <p>What I discovered right now may be difficult to implement in the optimizer (?), but it makes a huge difference in performance (only sample code with huge similarity to our query to reduce unnecessary complexity):</p>
  
  
  
  <pre class="lang-sql prettyprint-override"><code>create view foo as
  select
    sum(s.plan) over w_pyl as pyl_plan,      -- money planned to spend in this pot/loc/year
    sum(s.booked) over w_pyl as pyl_booked,  -- money already booked in this pot/loc/year
  
    -- money already booked in this pot/loc the years before (stored as sum already)
    last_value(s.booked_prev_years) over w_pl as pl_booked_prev_years,    
  
    -- update 2014-10-08: maybe the following additional selected columns
    -- may be implementation-/test-relevant since they could potentially be determined
    -- by sorting within the partition:
    min(s.id) over w_pyl,
    max(s.id) over w_pyl,
  
    -- ... anything could follow here ...
    x.*,
    s.*
  from
    pot_location_year x  -- may be some materialized view or (cache/regular) table
    left outer join  spendings s 
      on (s.pot = x.pot and s.loc = x.loc and s.year = x.year)
  window
    w_pyl  as (partition by  x.pot, x.year, x.loc)
    w_pl   as (partition by  x.pot, x.loc  order by x.year)
  </code></pre>
  
  <p>We have these two relevant indexes in place:</p>
  
  <pre class="lang-sql prettyprint-override"><code>pot_location_year_idx__p_y_l  -- on pot, year, loc
  pot_location_year_idx__p_l_y  -- on pot, loc, year
  </code></pre>
  
  <p>Now we run an explain for some test query</p>
  
  <pre class="lang-sql prettyprint-override"><code>explain select * from foo fetch first 100 rows only
  </code></pre>
  
  <p>This shows us some very <strong>bad performance, because the <em>pyl</em> index is used</strong>, where the <strong>result set has to be unnecessarily sorted twice :-(</strong> (the <strong>outmost <code>WindowAgg/Sort</code> step sorts <em>ply</em> because this is necessary for our <code>last_value(..) as pl_booked_prev_years</code></strong>):</p>
  
  <pre class="lang-sql prettyprint-override"><code> Limit  (cost=289687.87..289692.12 rows=100 width=512)
     -&gt;  WindowAgg  (cost=289687.87..292714.85 rows=93138 width=408)
           -&gt;  Sort  (cost=289687.87..289920.71 rows=93138 width=408)
                 Sort Key: x.pot, x.loc, x.year
                 -&gt;  WindowAgg  (cost=1.25..282000.68 rows=93138 width=408)
                       -&gt;  Nested Loop Left Join  (cost=1.25..278508.01 rows=93138 width=408)
                             Join Filter: ...
                             -&gt;  Nested Loop Left Join  (cost=0.83..214569.60 rows=93138 width=392)
                                   -&gt;  Index Scan using pot_location_year_idx__p_y_l on pot_location_year x  (cost=0.42..11665.49 rows=93138 width=306)
                                   -&gt;  Index Scan using ...  (cost=0.41..2.17 rows=1 width=140)
                                         Index Cond: ...
                             -&gt;  Index Scan using ...  (cost=0.41..0.67 rows=1 width=126)
                                   Index Cond: ...
  </code></pre>
  
  <p>So the obvious problem is, that the <strong>planner should choose the existing <em>ply</em> index instead, to not have to sort twice</strong>.</p>