Menu
Suppose I have a STABLE SQL function taking one parameter (internally the function performs a SELECT on a bunch of tables, though as far as the caller is concerned, the function is really just a black box).
If this function is invoked multiple times with the same parameter within the same SELECT statement, is PostgreSQL smart enough to only actually call it once and cache the result, or do I have to perform the caching manually using WITH statements (common table expressions)?
842
Postgres manages a “Shared Buffer Cache”, which it allocates and uses internally to keep data and indexes in memory. This is usually configured to be about 25% of total system memory for a server running a dedicated Postgres instance, such as all Heroku Postgres instances.
Generally, only the contents of table and index files will be cached in the shared buffer space. Query plans are cached in some circumstances. The best way to ensure this is to PREPARE the query once, then EXECUTE it each time. The results of a query are not automatically cached.
No. A view is basically a macro - your view definition gets merged with the query against it and then executed. Indeed, caching the execution plan would make little sense.
A VOLATILE function can do anything, including modifying the database. It can return different results on successive calls with the same arguments. The optimizer makes no assumptions about the behavior of such functions.
PostgreSQL has no cache for result of stable functions (it has no cache for any function result). PostgreSQL try to minimize number of calls, but it has no cache. You can try to penalize these calls by higher COST attribute. Using WITH should be good idea.
91
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
