X-Original-To: pgsql-sql@postgresql.org Received: from matrix.gatewaynet.com (unknown [217.19.69.50]) by postgresql.org (Postfix) with ESMTP id 143A447610A for ; Fri, 7 Mar 2003 07:09:45 -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 h27GHbIs022136; Fri, 7 Mar 2003 14:17:37 -0200 Received: from localhost (achill@localhost) by matrix.gatewaynet.com (8.12.5/8.12.5/Submit) with ESMTP id h27GHbZo022132; Fri, 7 Mar 2003 14:17:37 -0200 Date: Fri, 7 Mar 2003 14:17:36 -0200 (GMT+2) From: Achilleus Mantzios To: Andre Schubert Cc: pgsql-sql@postgresql.org Subject: Re: Help on indexing timestamps In-Reply-To: <20030307130223.5a58c132.andre@km3.de> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200303/186 X-Sequence-Number: 12226 On Fri, 7 Mar 2003, Andre Schubert wrote: > On Fri, 7 Mar 2003 13:48:04 -0200 (GMT+2) > Achilleus Mantzios wrote: > > > 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? > > > > I'am not sure how to create such an index... > > First: create or replace function trunc_ip(timestamp with time zone) returns timestamptz as > 'select date_trunc(''month'',$1)' language 'sql' with (iscachable); > Then: create index idx_test on tbl_traffic using btree( trunc(time_stamp) ); > > Result: db_km3aue=# explain analyze select sum(inet_up+inet_down) from tbl_traffic where trunc(tbl_traffic.time_stamp) = trunc('2003-02-01'::timestamptz) and ip = '80.243.40.56'; > NOTICE: QUERY PLAN: > > Aggregate (cost=108.78..108.78 rows=1 width=16) (actual time=2278.48..2278.48 rows=1 loops=1) > -> Index Scan using idx_test on tbl_traffic (cost=0.00..108.78 rows=1 width=16) (actual time=0.23..2240.50 rows=5346 loops=1) > Total runtime: 2278.62 msec > > Maybe the problem is, that the index is created without ip as the second column.... Sorry, it sliped my mind that we cannot have compound indexes on functions. :( Anyway. Did the explicit BETWEEN gave you satisfactory performance? > > [schnipp] > -- ================================================================== 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