Received: from bilbo.in.mat.cc (bilbo.in.mat.cc [212.43.217.121]) by postgresql.org (Postfix) with ESMTP id D6F71475F5B for ; Mon, 1 Apr 2002 13:35:36 -0500 (EST) Received: from bilbo.in.org (localhost [127.0.0.1]) by bilbo.in.mat.cc (Postfix) with ESMTP id 9666A71184 for ; Mon, 1 Apr 2002 19:15:02 +0200 (CEST) Received: from sauron (sauron.in.mat.cc [212.43.217.122]) by bilbo.in.mat.cc (Postfix) with ESMTP id E05EA71184 for ; Mon, 1 Apr 2002 19:14:57 +0200 (CEST) Date: Mon, 01 Apr 2002 19:14:57 +0200 From: Mathieu Arnold To: pgsql-sql@postgresql.org Subject: optimyzing Message-ID: <295475750.1017688497@sauron> X-Mailer: Mulberry/2.2.0b3 (Win32) X-wazaaa: True, true X-GnuPG-keyfile: http://mat.cc/pgp/1E041B04.txt X-GnuPG-key: 0x1E041B04 X-GnuPG-key-Fingerprint: D7 46 95 99 3A EE B6 5D BF E8 A6 48 AD 66 61 C4 1E 04 1B 04 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Archive-Number: 200204/5 X-Sequence-Number: 7270 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