Thread: Does iscachable work?

Does iscachable work?

From
root
Date:
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, consistently.  The table only
has 20000 rows or so.  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


Re: Does iscachable work?

From
Jeff Eckermann
Date:
If "entityid" has a lot of unique values, you won't
get much gain from caching.  My experience is that the
gain can be stunning in cases where there are few
distinct values in a column.  The best way to know the
benefit in a given case is to test it.

If your function is highly computation-intensive, or
(for example) makes selects against the database that
require full table scans, then you will see slow
results no matter what.

--- root <ffabrizio@exchange.webmd.net> wrote:
>
> 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,
> consistently.  The table only
> has 20000 rows or so.  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
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org


__________________________________________________
Do You Yahoo!?
Yahoo! Sports - Coverage of the 2002 Olympic Games
http://sports.yahoo.com