X-Original-To: pgsql-sql@postgresql.org Received: from server.beierfeld.kabeljournal.de (pD900DD2D.dip.t-dialin.net [217.0.221.45]) by postgresql.org (Postfix) with SMTP id 7FDEB4762F6 for ; Fri, 7 Mar 2003 06:27:06 -0500 (EST) Received: (qmail 12634 invoked from network); 7 Mar 2003 11:27:08 -0000 Received: from station158.beierfeld.kabeljournal.de (192.168.128.158) by server.beierfeld.kabeljournal.de with SMTP; 7 Mar 2003 11:27:08 -0000 Date: Fri, 7 Mar 2003 12:27:19 +0100 From: Andre Schubert To: Achilleus Mantzios Cc: pgsql-sql@postgresql.org Subject: Re: Help on indexing timestamps Message-Id: <20030307122719.0d160676.andre@km3.de> In-Reply-To: References: <20030307112639.395720be.andre@km3.de> Organization: km3 teledienst GmbH X-Mailer: Sylpheed version 0.8.6 (GTK+ 1.2.10; i586-pc-linux-gnu) Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Archive-Number: 200303/182 X-Sequence-Number: 12222 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. 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