From: | "Dan Langille" <dan(at)langille(dot)org> |
---|---|
To: | Jean-Christophe ARNU (JX) <jc(dot)arnu(at)free(dot)fr> |
Cc: | pgsql-admin(at)postgresql(dot)org |
Subject: | Re: Timestamps and performances problems |
Date: | 2002-04-10 14:55:22 |
Message-ID: | 20020410145611.75C093F30@bast.unixathome.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
On 10 Apr 2002 at 10:44, JX wrote:
> Le Wed, 10 Apr 2002 09:27:09 -0400
> "Dan Langille" <dan(at)langille(dot)org> me disait que :
>
> > On 10 Apr 2002 at 9:13, JX wrote:
> >
> > > Le Wed, 10 Apr 2002 09:06:55 -0400
> > > "Dan Langille" <dan(at)langille(dot)org> me disait que :
> > >
> > > > On 10 Apr 2002 at 11:51, Gaetano Mendola wrote:
> > > >
> > > > > "Jean-Christophe ARNU (JX)" <jc(dot)arnu(at)free(dot)fr> wrote:
> > > > > > Hello all.
> > > > > > I've a performance problem on specific requests :
> > > > > >
> > > > > > When I use timestamps + interval in where clauses, query
> > > > > > performance is slowed down by a factor of 20 or 30!!!! For exemple
> > > > > > : select timestamp,value
> > > > > > from measure
> > > > > > where timestamp<now() and timestamp>(now() - '1 hour'::interval)
> > > >
> > > > Try where timestamp<now() and timestamp>(now() - '1
> > > > hour'::interval)::timestemp.
> > >
> > > What's the difference with the syntax above? It takes he same time
> > > than the query above. Bounded timestamps with "real" ISO timestamps
> > > strings are always up to about 200 times faster (with extensive test
> > > proof).
> >
> > It casts the value to a timestamp. I would prefer to discuss this on-
> > list.
> Okaye, but what's the incidence on preformance issues?
> Casting should only insure that given string is to be taken as a timestamp
> isn't it? Does it make an "instanciation" of the timestamp to be that would be
> applied for comparision clauses?
If there is an index on that field, casting to a timestamp may help the
optimization. Therefore I suggested that it be tried.
--
Dan Langille
The FreeBSD Diary - http://freebsddiary.org/ - practical examples
From | Date | Subject | |
---|---|---|---|
Next Message | Dan Langille | 2002-04-10 15:00:31 | Re: Timestamps and performances problems |
Previous Message | Jean-Christophe ARNU (JX) | 2002-04-10 14:44:25 | Re: Timestamps and performances problems |