Re: Custom cache implemented in a postgresql C function

On 21 Oct 2010, at 1:44, Gabi Julien wrote:

> Hi,
>
> Here is my problem: I have a postgresql C function that looks like this:
>
> Datum filter(PG_FUNCTION_ARGS);
>
> It takes identifiers and queries a bunch of tables and ends up returning true or false. So far nothing difficult except that we want better performance. The function was already optimized to the best of my abilities and changing the structure of the database would not help. However, having a

That sounds like your function would classify as a STABLE function within Postgres, did you define it as such? Postgres will cache the results of STABLE (and IMMUTABLE) functions all by itself, in which case you may not need your custom cache. The default is to classify a function as VOLATILE, meaning the results aren't suitable for caching.

Another possible solution is to store the results of your function (or of the queries it performs) in a separate table[1] that would function as a cache of sorts. The benefit is that the table gets managed by Postgres, so you won't have to worry about stuff like spilling to disk if the cache grows too large to fit in (available) memory.

[1] A TEMP TABLE wouldn't work, as it isn't visible to other sessions, although you could create one per session of course.

Of course, with a custom cache you have more control over how it behaves, so that may still be your best solution.

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.

!DSPAM:737,4cc01f6410281645420170!