Re: Timestamps and performances problems

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)

is 20 to 30 times longer than

select timestamp,value
from measure
where timestamp<'2002-04-10 10:00' and timestamp>='2002-04-10 9:00';

So where is the bottleneck?
A paradigm seems that now() and (now() - '1hour'::interval) is evaluated for
each row comparison... Am I right? Thus is there a way to make SQL
interpreter evaluate this by rewriting them before launching any comparisons?

Or do I have to rewrite all my application queries and calculate each time
now() and interval predicates?

Thanks by advance

--
Jean-Christophe ARNU
s/w developer
Paratronic France
MR: J'ai beaucoup entendu parler de fcol, mais je n'y suis jamais alle
MR: jeter un oeil.... c'est quoi l'adresse ?
CL: Tu viens d'y poster :)
-+- in Guide de linuxien pervers : "Termes abscons..." -+-

"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)
>
> is 20 to 30 times longer than
>
> select timestamp,value
> from measure
> where timestamp<'2002-04-10 10:00' and timestamp>='2002-04-10 9:00';
>
> So where is the bottleneck?
> A paradigm seems that now() and (now() - '1hour'::interval) is evaluated
for
> each row comparison... Am I right? Thus is there a way to make SQL
> interpreter evaluate this by rewriting them before launching any
comparisons?
>
> Or do I have to rewrite all my application queries and calculate each
time
> now() and interval predicates?

I have the same problem, but in my case I use this query in a view so I
can't store the value now()
in a variable temp, I hope that this problem have another solution.

May be I can create a function that return a data set and I do the select
inside
with a temp variable for store now() but I don't know if is just a
quick & dirty solution.

Ciao
Gaetano.

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.

--
Dan Langille
The FreeBSD Diary - http://freebsddiary.org/ - practical examples

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?

Thanks

--
Jean-Christophe ARNU
s/w developer
Paratronic France
«Dès que je clique sur "mount", il ne fait rien et le cdrom
reste "unmount".
Quel est le pb ?»
-+- Popol in Guide du linuxien pervers : "De l'avantage des interfaces..." -+-

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

On 10 Apr 2002 at 10:55, Dan Langille wrote:

> If there is an index on that field, casting to a timestamp may help the
> optimization. Therefore I suggested that it be tried.

And using "explain" will show the plan for that command. I recommend
using that too.
--
Dan Langille
The FreeBSD Diary - http://freebsddiary.org/ - practical examples

Jean-Christophe ARNU (JX) <jc(dot)arnu(at)free(dot)fr> writes:
> 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)

> is 20 to 30 times longer than

> select timestamp,value
> from measure
> where timestamp<'2002-04-10 10:00' and timestamp>='2002-04-10 9:00';

> So where is the bottleneck?

Did you compare EXPLAIN output? I suspect that the second query is
using an index on the timestamp column and the first isn't.

The reason it isn't is that now() isn't a constant, and the system is
not smart enough to realize that it's safe to optimize the query into
an indexscan anyway.

For 7.3 we've fixed this by introducing a new concept of "constant within
a query", which now() does satisfy. In the meantime you could hack
around it by writing a user-defined function that calls now() and is
marked isCachable --- which is a lie, but you can get away with it in
interactive queries. (But don't try calling such a function in views,
or queries in plpgsql, 'cause you'll get burnt.)

regards, tom lane

Hi Folks

I am trying to write a function that should archive old values to an
archiving table before update on the orignal table but it inserts both old
and new values to the archiving table here is the code:

CREATE FUNCTION fn_archive_01() RETURNS OPAQUE AS '
BEGIN
/* TG_OP is the function (UPDATE, DELETE, SELECT) */
INSERT INTO customer_archive
VALUES
(OLD.id, OLD.name,current_user,now(),TG_OP);

IF TG_OP = ''UPDATE''
THEN
RETURN NEW;
END IF;

RETURN OLD;
END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER tr_customer_archive_del BEFORE DELETE
ON customer FOR EACH ROW
EXECUTE PROCEDURE fn_archive_01();

Thanks for your help
Tariq