From: | Andre Schubert <andre(at)km3(dot)de> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Help on indexing timestamps |
Date: | 2003-03-07 10:26:39 |
Message-ID: | 20030307112639.395720be.andre@km3.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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:
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
From | Date | Subject | |
---|---|---|---|
Next Message | Andreas Pflug | 2003-03-07 10:30:37 | How to notice column changes in trigger |
Previous Message | Rajesh Kumar Mallah | 2003-03-07 07:55:26 | Re: sql question regarding count(*) |