Thread: Does iscachable work?

Hello,

I'm running PostgreSQL 7.1.3, and I have a query that uses one of my
functions:

select findregion(entityid) from msg200;

The findregion function is set to be 'with (iscachable)'.  However, the
query takes upwards of 40 seconds to run, the first time and every time
thereafter, consistently.  It doesn't seem like the caching is working.

The only other time I tried to use function results caching, it actually
consistently increased my execution time.  Anything I need to be doing
differently?

Thanks,
Fran

root <ffabrizio@mmrd.com> writes:
> I'm running PostgreSQL 7.1.3, and I have a query that uses one of my
> functions:

> select findregion(entityid) from msg200;

> The findregion function is set to be 'with (iscachable)'.  However, the
> query takes upwards of 40 seconds to run, the first time and every time
> thereafter, consistently.  It doesn't seem like the caching is working.

I'm not sure what you think "iscachable" does ... but there certainly is
not a cache that remembers the results of functions across statements.

            regards, tom lane

Tom Lane wrote:

> root <ffabrizio@mmrd.com> writes:
>
>>I'm running PostgreSQL 7.1.3, and I have a query that uses one of my
>>functions:
>>
>
>>select findregion(entityid) from msg200;
>>
>
>>The findregion function is set to be 'with (iscachable)'.  However, the
>>query takes upwards of 40 seconds to run, the first time and every time
>>thereafter, consistently.  It doesn't seem like the caching is working.
>>
>
> I'm not sure what you think "iscachable" does ... but there certainly is
> not a cache that remembers the results of functions across statements.


Hrmmm. Ok. I have two observations to share in light of that...

The query above should still see improvement since findregion() will be
called with the same parameter multiple times within the same statement.
      However, I just tested that and in practice, it runs at an average
of 23 seconds with caching, and 21 without.  The nature of the data is
such that there's 20000 rows and only 115 possible parameters to the
function, so I would have expected to see a good deal of improvement
(somewhere around 99.5% of the function calls should have been eligible
to be pre-evaluated in this case).

The other observation is that the author of PostgreSQL Developer's
Handbook appears to indicate that it did cache across statements.  The
text and examples on page 235 clearly show that this is his intended
usage.

-- QUOTE FROM BOOK --

Let's see how it works:
performance=# SELECT geomean(94,57);
geomean
-------
109.9317970380
(1 row)

Simply add WITH (iscachable) to the definition of the function, and the
results will be cached.

-- END QUOTE --

If it doesn't persist across statements, his example is quite useless.
=) That's quite misleading if it doesn't really work that way.

However, does my first observation seem odd to anyone else?  And if not,
can someone give me an example of where using iscachable does help?

Thanks,
Fran

Fran Fabrizio <ffabrizio@mmrd.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