Help on indexing timestamps
От | Andre Schubert |
---|---|
Тема | Help on indexing timestamps |
Дата | |
Msg-id | 20030307112639.395720be.andre@km3.de обсуждение исходный текст |
Ответы |
Re: Help on indexing timestamps
|
Список | 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_stampon 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 nullip | inet | local_up | bigint | not nulllocal_down | bigint | not nullinet_up | bigint | not nullinet_down | bigint | not null Indexes: idx_ip_time_stamp db_test=# \d idx_ip_time_stamp Index "idx_ip_time_stamp" Column | Type ------------+--------------------------ip | inettime_stamp | timestamp with time zone
В списке pgsql-sql по дате отправления: