X-Original-To: pgsql-sql@postgresql.org Received: from matrix.gatewaynet.com (unknown [217.19.69.50]) by postgresql.org (Postfix) with ESMTP id 305364760CC for ; Fri, 7 Mar 2003 06:40:13 -0500 (EST) Received: from matrix.gatewaynet.com (matrix.gatewaynet.com [217.19.69.50] (may be forged)) by matrix.gatewaynet.com (8.12.5/8.12.5) with ESMTP id h27Fm5Is022002; Fri, 7 Mar 2003 13:48:05 -0200 Received: from localhost (achill@localhost) by matrix.gatewaynet.com (8.12.5/8.12.5/Submit) with ESMTP id h27Fm4Q8021998; Fri, 7 Mar 2003 13:48:04 -0200 Date: Fri, 7 Mar 2003 13:48:04 -0200 (GMT+2) From: Achilleus Mantzios To: Andre Schubert Cc: pgsql-sql@postgresql.org Subject: Re: Help on indexing timestamps In-Reply-To: <20030307122719.0d160676.andre@km3.de> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200303/183 X-Sequence-Number: 12223 On Fri, 7 Mar 2003, Andre Schubert wrote: > On Fri, 7 Mar 2003 13:01:16 -0200 (GMT+2) > Achilleus Mantzios 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.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 > > > > > > ---------------------------(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