From: Andre Schubert <andre@km3.de>
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Help on indexing timestamps
Date: Fri, 7 Mar 2003 12:27:19 +0100
Message-ID: <20030307122719.0d160676.andre@km3.de> (raw)
In-Reply-To: <Pine.LNX.4.44.0303071258150.21682-100000@matrix.gatewaynet.com>
References: <20030307112639.395720be.andre@km3.de>
<Pine.LNX.4.44.0303071258150.21682-100000@matrix.gatewaynet.com>
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.
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: andre@km3.de, achill@matrix.gatewaynet.com
Subject: Re: Help on indexing timestamps
In-Reply-To: <20030307122719.0d160676.andre@km3.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox