Re: Help on indexing timestamps
От | Achilleus Mantzios |
---|---|
Тема | Re: Help on indexing timestamps |
Дата | |
Msg-id | Pine.LNX.4.44.0303071344410.21847-100000@matrix.gatewaynet.com обсуждение исходный текст |
Ответ на | Re: Help on indexing timestamps (Andre Schubert <andre@km3.de>) |
Ответы |
Re: Help on indexing timestamps
(Andre Schubert <andre@km3.de>)
|
Список | pgsql-sql |
On Fri, 7 Mar 2003, Andre Schubert wrote: > 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. You could have an index on the whole date_trunc('month',tbl_traffic.time_stamp),ip How does it perform? > > 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 > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > -- ================================================================== 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
В списке pgsql-sql по дате отправления: