Date: Fri, 7 Mar 2003 13:01:16 -0200 (GMT+2)
From: Achilleus Mantzios <achill@matrix.gatewaynet.com>
To: Andre Schubert <andre@km3.de>
Cc: pgsql-sql@postgresql.org
Subject: Re: Help on indexing timestamps
In-Reply-To: <20030307112639.395720be.andre@km3.de>
Message-ID: <Pine.LNX.4.44.0303071258150.21682-100000@matrix.gatewaynet.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

On Fri, 7 Mar 2003, Andre Schubert wrote:

> Hi all,
> 
> i have a little problem on indexing a table which contains
> about 4 millions of traffic-data.
> My problem is, that a want to select all data from
> a specific month from a specific ip and this select should use the index.
> I use the following select:

Did you try to use BETWEEN ??
E.g.
... and time_stamp between '2003-01-01 00:00:00'::timestamp and 
'2003-02-01 00:00:00'::timestamp

> 
> db_nmkm3aue=# explain analyze select sum(inet_up+inet_down) from tbl_traffic where date_trunc('month',tbl_traffic.time_stamp)::timestamptz = date_trunc('month',now() - timespan('1 months')) and ip = '80.243.38.57';
> NOTICE:  QUERY PLAN:
> 
> Aggregate  (cost=116.30..116.30 rows=1 width=16) (actual time=1620.79..1620.79 rows=1 loops=1)
>   ->  Index Scan using idx_ip_time_stamp on tbl_traffic  (cost=0.00..116.30 rows=1 width=16) (actual time=1216.79..1579.89 rows=5232 loops=1)
> Total runtime: 1620.94 msec
> 
> But it takes a long time to select the traffic for all Ips.
> Is there a way to select these data with using the index correctly ?
> 
> Thanks in advance
> 
> BTW: Table-schema as follows
> 
> db_test=# \d tbl_traffic
>                 Table "tbl_traffic"
>    Column   |           Type           | Modifiers 
> ------------+--------------------------+-----------
>  time_stamp | timestamp with time zone | not null
>  ip         | inet                     | 
>  local_up   | bigint                   | not null
>  local_down | bigint                   | not null
>  inet_up    | bigint                   | not null
>  inet_down  | bigint                   | not null
> Indexes: idx_ip_time_stamp
> 
> db_test=# \d idx_ip_time_stamp
>        Index "idx_ip_time_stamp"
>    Column   |           Type           
> ------------+--------------------------
>  ip         | inet
>  time_stamp | timestamp with time zone
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
> 

-- 
==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel:    +30-210-8981112
fax:    +30-210-8981877
email:  achill@matrix.gatewaynet.com
        mantzios@softlab.ece.ntua.gr