Re: Does iscachable work?

Fran Fabrizio <ffabrizio(at)mmrd(dot)com> writes:
> The query above should still see improvement since findregion() will be
> called with the same parameter multiple times within the same statement.

So? There is no cache.

> The other observation is that the author of PostgreSQL Developer's
> Handbook appears to indicate that it did cache across statements.

I do not know that book, and I will take no responsibility for its
author's claims.

What "iscachable" means is that the function *could* be cached, ie,
it will always return the same result given the same arguments.
Presently this is primarily used to enable reduction of constant
expressions, for example, "2 + 2" will be reduced to 4 if the function
associated with the + operator is marked cachable. "iscachable" is a
promise from the function author to the system, not an obligation on the
system to do anything.

(I have extremely strong doubts that a function-value cache such as you
seem to be envisioning would be profitable. For most of the functions
that Postgres deals with, the cycles spent probing/maintaining the cache
would exceed the execution time of the function, making it a net loss
even with very optimistic assumptions about the cache hit rate.)

Possibly the function attribute should have been named something else,
since it evidently confused both you and that book's author ...

regards, tom lane