From: Andre Schubert <andre@km3.de>
To: Tomasz Myrta <jasiek@klaster.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Help on indexing timestamps
Date: Wed, 12 Mar 2003 07:27:45 +0100
Message-ID: <20030312072745.480790d0.andre@km3.de> (raw)
In-Reply-To: <20030310152409.56ff79f1.andre@km3.de>
References: <20030307112639.395720be.andre@km3.de>
<3E6C56EF.6050609@klaster.net>
<20030310152409.56ff79f1.andre@km3.de>
On Mon, 10 Mar 2003 15:24:09 +0100
Andre Schubert <andre@km3.de> wrote:
> On Mon, 10 Mar 2003 10:12:15 +0100
> Tomasz Myrta <jasiek@klaster.net> wrote:
>
> > 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:
> > >
> > > 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
> > >
> > I have one more solution - try to rewrite your where clause to NOT USE
> > function on time_stamp. If your query will look like:
> > select ... where time_stamp between (function with now() returning first
> > day) and (function with now() returning last day);
> > your index will work fine.
> >
> Thanks for the hint, i will test this and report to this list.
>
I have tested and it doesnt work as it should do.
I think its the same problem posted by Tom Lane,
and it should go away after an update to 7.3.
Regards, as
Thanks as
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, jasiek@klaster.net
Subject: Re: Help on indexing timestamps
In-Reply-To: <20030312072745.480790d0.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