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, ...).
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):
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)
We have these two relevant indexes in place:
pot_location_year_idx__p_y_l -- on pot, year, loc
pot_location_year_idx__p_l_y -- on pot, loc, year
Now we run an explain for some test query
explain select * from foo fetch first 100 rows only
This shows us some very bad performance, because the pyl index is used, where the result set has to be unnecessarily sorted twice :-( (the outmost WindowAgg/Sort
step sorts ply because this is necessary for our last_value(..) as pl_booked_prev_years
):
Limit (cost=289687.87..289692.12 rows=100 width=512)
-> WindowAgg (cost=289687.87..292714.85 rows=93138 width=408)
-> Sort (cost=289687.87..289920.71 rows=93138 width=408)
Sort Key: x.pot, x.loc, x.year
-> WindowAgg (cost=1.25..282000.68 rows=93138 width=408)
-> Nested Loop Left Join (cost=1.25..278508.01 rows=93138 width=408)
Join Filter: ...
-> Nested Loop Left Join (cost=0.83..214569.60 rows=93138 width=392)
-> Index Scan using pot_location_year_idx__p_y_l on pot_location_year x (cost=0.42..11665.49 rows=93138 width=306)
-> Index Scan using ... (cost=0.41..2.17 rows=1 width=140)
Index Cond: ...
-> Index Scan using ... (cost=0.41..0.67 rows=1 width=126)
Index Cond: ...
So the obvious problem is, that the planner should choose the existing ply index instead, to not have to sort twice.