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(at)scw(dot)org
Support Center of Washington (www.scw.org)