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 A350747610A for ; Fri, 7 Mar 2003 07:02:09 -0500 (EST) Received: (qmail 13957 invoked from network); 7 Mar 2003 12:02:11 -0000 Received: from station158.beierfeld.kabeljournal.de (192.168.128.158) by server.beierfeld.kabeljournal.de with SMTP; 7 Mar 2003 12:02:11 -0000 Date: Fri, 7 Mar 2003 13:02:23 +0100 From: Andre Schubert To: Achilleus Mantzios Cc: pgsql-sql@postgresql.org Subject: Re: Help on indexing timestamps Message-Id: <20030307130223.5a58c132.andre@km3.de> In-Reply-To: References: <20030307122719.0d160676.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/184 X-Sequence-Number: 12224 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.... [schnipp]