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



view thread (2+ messages)  latest in thread

Message-ID: <295475750.1017688497@sauron>
Permalink:  ../295475750.1017688497@sauron/
Also on:    postgresql.org/message-id/295475750.1017688497@sauron

 · 

reply

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