optimyzing

Hi

I was wondering if it could be possible to optimise such a query :

select
avg(traffic) AS avg
from
(select
date(time),
sum(traffic) / 1024 / 86400 AS traffic
FROM stats
WHERE ip = '212.43.217.66' AND date(time) < date('now')
GROUP BY date(time)
) AS subselect

having :
Table "stats"
Column | Type | Modifiers
---------+-----------------------------+-----------
ip | inet | not null
time | timestamp without time zone | not null
packet | bigint | not null
traffic | bigint | not null

I have a cron which fills this table every X minute with the total traffic
by ip for this period.

the subselect gives me the average traffic for an IP by day (in KB/s) :

date | traffic
------------+----------------
2002-03-25 | 988.7981688097
2002-03-26 | 961.1585484370
2002-03-27 | 984.3613075426
2002-03-28 | 912.5771145065
2002-03-29 | 885.4281184105
2002-03-30 | 800.6509613489
2002-03-31 | 673.6354859190

and I can't get a query to have the average of that without having to do
this subselect.

avg
----------------
886.6585292820

and if it is useful :

Aggregate (cost=1366.11..1366.11 rows=1 width=16)
-> Subquery Scan subselect (cost=1365.34..1366.07 rows=15 width=16)
-> Aggregate (cost=1365.34..1366.07 rows=15 width=16)
-> Group (cost=1365.34..1365.71 rows=146 width=16)
-> Sort (cost=1365.34..1365.34 rows=146 width=16)
-> Index Scan using stats_ip on stats
(cost=0.00..1360.07 rows=146 width=16)

--
Mathieu Arnold