pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedoptimyzing
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