Re: abstract: fix poor constant folding in 7.0.x, fixed in 7.1?

* 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."