Re: Does iscachable work?

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