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 nulltime | timestamp
withouttime zone | not nullpacket | bigint | not nulltraffic | 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.79816880972002-03-26 | 961.15854843702002-03-27 |
984.36130754262002-03-28| 912.57711450652002-03-29 | 885.42811841052002-03-30 | 800.65096134892002-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=146width=16) -> Sort (cost=1365.34..1365.34 rows=146 width=16) ->
IndexScan using stats_ip on stats
(cost=0.00..1360.07 rows=146 width=16)
--
Mathieu Arnold
