Help on indexing timestamps

Поиск
Список
Период
Сортировка
От Andre Schubert
Тема Help on indexing timestamps
Дата
Msg-id 20030307112639.395720be.andre@km3.de
обсуждение исходный текст
Ответы Re: Help on indexing timestamps  (Achilleus Mantzios <achill@matrix.gatewaynet.com>)
Список 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 по дате отправления:

Предыдущее
От: Rajesh Kumar Mallah
Дата:
Сообщение: Re: sql question regarding count(*)
Следующее
От: Andreas Pflug
Дата:
Сообщение: How to notice column changes in trigger