* Joel Burton <jburton@scw.org> [001207 15:52] wrote:
> > We had problem with a query taking way too long, basically
> > we had this:
> >
> > select
> > date_part('hour',t_date) as hour,
> > transval as val
> > from st
> > where
> > id = 500
> > AND hit_date >= '2000-12-07 14:27:24-08'::timestamp - '24
> > hours'::timespan AND hit_date <= '2000-12-07 14:27:24-08'::timestamp
> > ;
> >
> > turning it into:
> >
> > select
> > date_part('hour',t_date) as hour,
> > transval as val
> > from st
> > where
> > id = 500
> > AND hit_date >= '2000-12-07 14:27:24-08'::timestamp
> > AND hit_date <= '2000-12-07 14:27:24-08'::timestamp
> > ;
>
> Perhaps I'm being daft, but why should hit_date be both >= and <=
> the exact same time and date? (or did you mean to subtract 24
> hours from your example and forgot?)
Yes, typo.
> > (doing the -24 hours seperately)
> >
> > The values of cost went from:
> > (cost=0.00..127.24 rows=11 width=12)
> > to:
> > (cost=0.00..4.94 rows=1 width=12)
> >
> > By simply assigning each sql "function" a taint value for constness
> > one could easily reduce:
> > '2000-12-07 14:27:24-08'::timestamp - '24 hours'::timespan
> > to:
> > '2000-12-07 14:27:24-08'::timestamp
>
> You mean '2000-12-06', don't you?
Yes, typo. :)
>
> > Each function should have a marker that explains whether when given a
> > const input if the output might vary, that way subexpressions can be
> > collapsed until an input becomes non-const.
>
> There is "with (iscachable)".
>
> Does
>
> CREATE FUNCTION YESTERDAY(timestamp) RETURNS timestamp AS
> 'SELECT $1-''24 hours''::interval' WITH (iscachable)
>
> work faster?
It could be, but it could be done in the sql compiler/planner
explicitly to save me from myself, no?
--
-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]
"I have the heart of a child; I keep it in a jar on my desk."
