Thread: 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 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

On Mon, 01 Apr 2002 19:14:57 +0200
Mathieu Arnold <mat@mat.cc> wrote:

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


It seems to me that the query can be rewritten without a sub-query,
even though the number of sampling per day is not always kept 
at a constant value.

  SELECT SUM(traffic)::float / COUNT(DISTINCT date(time))::float                       / 1024.0 / 86400.0 AS avg
FROM  stats  WHERE  ip = '212.43.217.66' AND date(time) < date('now')
 


Regards,
Masaru Sugawara