Re: very slow execution of stored procedures

"Richard Huxton" <dev@archonet.com> writes:
> Tom Lane <tgl@sss.pgh.pa.us> said:
> 2. Mark the timestamp() conversions as cachable
>>
>> This would be a BAD idea.  Likely consequences include timestamp('now')
>> being evaluated at first use of a function, and not changing thereafter.
>> Probably not what you wanted...

> I did say it might well break things - actually, does this mean you
> could mark fna() as cachable, use it in fnb(), mark fna() uncachable
> again and fnb() will never notice?

Until the next time fnb gets compiled, anyway --- but sooner or later we
are going to have logic to flush cached compiled functions as soon as
something they depend on changes.

What I have seen people do is make wrapper or alias functions that are
marked cachable just to fool the planner.  If fnx is marked cachable and
is applied to a constant, then the constant-folding logic will evaluate
fnx and replace the call by a constant --- no matter what might lurk
inside fnx's body.

> So - am I right in thinking the rules run:

> cachable_fn(constant)        = cached
> cachable_fn(variable)        = not cached
> cachable_fn(non_cachable_fn) = not cached

> with the "cachable" flag filtering up through the stack?

It's not so much "cachable" as "is it constant?".  If a function is
marked cachable and is applied to constant arguments, replace it by
a constant.  Apply this rule recursively throughout the expression.

There is a slightly different use of the cachability flag that is
actually what was creating the problem in this example.  A WHERE clause
like "indexed_var = expression" can be used as an indexscan bound if
the expression is constant, *or* if the expression contains function
parameters and constants but does not contain any noncachable function
calls.  The idea here is that the expression must be constant for the
duration of any one query --- if it can vary from one row to the next
then we can't use it as an indexscan bound.  Given a function parameter
p, "p" or "p+1" can be an indexscan bound --- but "timestamp(p)" fails
the test.

> Although I can see that something like timestamp('now') is always going to
> cause trouble because as text 'now' is constant and it's not until you
> evaluate it as a timestamp that it can vary.

Exactly --- the 'now' case is the reason why text-to-timestamp can't
be marked cachable.

There is a weaker notion of cachability that timestamp() *does* satisfy:
given the same input, its output won't change within a query (because
'now' actually means the start of the transaction, not realtime).
So in the case of "WHERE timestamp_col = timestamp(p)", it's really OK
to compute timestamp(p) once at the start of the query and use the
result for an indexscan bound.  We don't currently have any such
intermediate notion of cachability, unfortunately.  This omission is the
reason why you can rewrite a function as

        timestamp tp;

        tp := timestamp(p);
        SELECT ... WHERE timestamp_col = tp;

and get a speedup without breaking the logic: essentially, you've forced
the system to evaluate timestamp(p) just once per call, which is really
OK even though the system wouldn't risk it by itself.

            regards, tom lane