From: Mathieu Arnold <mat@mat.cc>
To: pgsql-sql@postgresql.org
Subject: optimyzing
Date: Mon, 01 Apr 2002 19:14:57 +0200
Message-ID: <295475750.1017688497@sauron> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mat@mat.cc
Subject: Re: optimyzing
In-Reply-To: <295475750.1017688497@sauron>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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