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