I have an abstract solution for a problem in postgresql's
handling of what should be constant data.
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
;
(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
by applying the expression and rewriting the query.
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.
Here, let's break up:
'2000-12-07 14:27:24-08'::timestamp - '24 hours'::timespan
What we have is:
timestamp(const) - timespan(const)
we have timestamp defined like so:
const timestamp(const string)
non-const timestamp(non-const)
and timespan like so:
const timespan(const string)
non-const timespan(non-const)
So now we have:
const timestamp((const string)'2000-12-07 14:27:24-08')
- const timespan((const string)'24 hours')
-----------------------------------------------------------
const
- const
----------------
const
then eval the query.
You may want to allow a function to have a hook where it can
eval a const because depending on the const it may or may not
be able to return a const, for instance if some string
you passed to timestamp() caused it to return non-const data.
Or maybe this is fixed in 7.1?
--
-Alfred Perlstein - [[EMAIL PROTECTED]|[EMAIL PROTECTED]]
"I have the heart of a child; I keep it in a jar on my desk."
