Re: Performance of query (fwd)

"Dann Corbit" <DCorbit(at)connx(dot)com> writes:
>> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
>> But actually I suspect the easiest point of attack is not the
>> EXISTS subquery, but the timestamp comparison. Can you get
>> your application to supply a simple literal constant to
>> compare to the timestamp, viz '2003-06-10 21:44' rather than
>> now()-'2 hours'? The former gives the planner something to
>> compare to its statistics, the latter doesn't.

> In a case like that, wouldn't it be worthwhile having the planner
> actually evaluate the expression?

I've thought about that ... but am not sure whether it wouldn't create
as many problems as it solves. What are the consequences when the
planner's pre-evaluation yields a different result from what actually
happens at runtime? Hardly an unlikely scenario when dealing with
stuff like now(). (And for actually constant expressions, say
sin(pi), the result already does get folded to a constant. So only
the nontrivial cases are left to think about.)

In the case of functions with side-effects (think nextval()), the
planner *must not* speculatively evaluate the function. We could
introduce another function property-flag, like IMMUTABLE or STRICT,
to govern this ... but I'm not sure it's worth the trouble.

regards, tom lane