Re: Help on indexing timestamps
От | Andre Schubert |
---|---|
Тема | Re: Help on indexing timestamps |
Дата | |
Msg-id | 20030307122719.0d160676.andre@km3.de обсуждение исходный текст |
Ответ на | Re: Help on indexing timestamps (Achilleus Mantzios <achill@matrix.gatewaynet.com>) |
Ответы |
Re: Help on indexing timestamps
(Achilleus Mantzios <achill@matrix.gatewaynet.com>)
|
Список | pgsql-sql |
On Fri, 7 Mar 2003 13:01:16 -0200 (GMT+2) Achilleus Mantzios <achill@matrix.gatewaynet.com> wrote: > 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 > Yes and it works if i write the dates by hand, every new month. But the query is executed automatically and i dont want to write in the dates before the query is executed. Maybe the the start and enddate should also be alculated with sql, because i want to create a view from this statement and execute it every month. Or did i miss something. Regards, as > > > > 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 > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
В списке pgsql-sql по дате отправления: