Re: Help on indexing timestamps - Mailing list pgsql-sql

On Fri, 7 Mar 2003, Andre Schubert wrote:

> On Fri, 7 Mar 2003 13:48:04 -0200 (GMT+2)
> Achilleus Mantzios <achill@matrix.gatewaynet.com> wrote:
> 
> > 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?
> > 
> 
> 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=5346loops=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