Re: Help on indexing timestamps

On Fri, 7 Mar 2003, Andre Schubert wrote:

> On Fri, 7 Mar 2003 13:01:16 -0200 (GMT+2)
> Achilleus Mantzios <achill(at)matrix(dot)gatewaynet(dot)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?

>
> 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(at)postgresql(dot)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(at)matrix(dot)gatewaynet(dot)com
> > mantzios(at)softlab(dot)ece(dot)ntua(dot)gr
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)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(at)matrix(dot)gatewaynet(dot)com
mantzios(at)softlab(dot)ece(dot)ntua(dot)gr