pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
optimyzing
2+ messages / 2 participants
[nested] [flat]

* optimyzing
@ 2002-04-01 17:14  Mathieu Arnold <mat@mat.cc>
  0 siblings, 1 reply; 2+ messages in thread

From: Mathieu Arnold @ 2002-04-01 17:14 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: optimyzing
@ 2002-04-02 15:44  Masaru Sugawara <rk73@sea.plala.or.jp>
  parent: Mathieu Arnold <mat@mat.cc>
  0 siblings, 0 replies; 2+ messages in thread

From: Masaru Sugawara @ 2002-04-02 15:44 UTC (permalink / raw)
  To: Mathieu Arnold <mat@mat.cc>; +Cc: pgsql-sql

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





^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2002-04-02 15:44 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-04-01 17:14 optimyzing Mathieu Arnold <mat@mat.cc>
2002-04-02 15:44 ` Masaru Sugawara <rk73@sea.plala.or.jp>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox