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
exceptthat we want better performance. The function was already optimized to the best of my abilities and changing the
structureof 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
willcache 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
haveto 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!