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

> 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?)

> (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?

> 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?

--
Joel Burton, Director of Information Systems -*- jburton@scw.org
Support Center of Washington (www.scw.org)