Re: Help on indexing timestamps - Mailing list pgsql-sql
From | Achilleus Mantzios |
---|---|
Subject | Re: Help on indexing timestamps |
Date | |
Msg-id | Pine.LNX.4.44.0303071258150.21682-100000@matrix.gatewaynet.com Whole thread Raw |
In response to | Help on indexing timestamps (Andre Schubert <andre@km3.de>) |
Responses |
Re: Help on indexing timestamps
|
List | pgsql-sql |
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.89rows=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