postgresql - SQL window functions: Performance impact of returning the same avg() many times? -

i select bunch of rows table a, along results of aggregate functions avg(a.price) , avg(a.distance).

now, select query takes bit of time, don't want run 1 query rows, , other averages. if did that, i'd running query select appropriate rows twice.

but looking @ postgresql window function documentation (http://www.postgresql.org/docs/9.1/static/tutorial-window.html), seems using window function return results of aggregate functions want use alongside returned rows means every single row returned contain results of aggregate functions. , in case, since aggregation on rows returned main select query , not subset of rows, seems wasteful.

what performance implications of returning same avg() many times, given i'm selecting subset of rows in doing aggregate queries across entire subset? in particular, postgres recompute average every time, or cache average somehow?

by way of analogy: if @ window function docs , pretend depname 'develop' every row returned select query, , average same every row because average computed across returned rows. how many times average computed?

you can use cte want. according postgres documentation:

a useful property of queries evaluated once per execution of parent query, if referred more once parent query or sibling queries. thus, expensive calculations needed in multiple places can placed within query avoid redundant work. possible application prevent unwanted multiple evaluations of functions side-effects. however, other side of coin optimizer less able push restrictions parent query down query ordinary sub-query. query evaluated stated, without suppression of rows parent query might discard afterwards. (but, mentioned above, evaluation might stop if reference(s) query demand limited number of rows.)

you can structure final results using structure such as:

with cte (your basic select goes here) select * cte cross join      (select averages here       cte      ) const < row filter here>