11

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)?

Jon Smark
  • 2,528
  • 24
  • 31
  • Attempting to manually "cache" it with a CTE isn't guaranteed to keep from calling it multiple times. Even on systems that _can_ cache procedure calls, you usually can't do it if it executes SQL on a table - because the optimizer may not have a good way to tell if relevant rows have been added to a table without re-running the query again (at which point is has the answer...). – Clockwork-Muse Dec 21 '13 at 14:44
  • 1
    @Clockwork-Muse: These considerations do *not* apply to Postgres, where all CTEs of a single query are based on the same snapshot - independent of concurrent transactions. So, yes, a CTE (WITH query) is useful to avoid repeated execution. Subqueries can also do the job and a often cheaper. – Erwin Brandstetter Dec 23 '13 at 19:57

1 Answers1

13

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.

Pavel Stehule
  • 42,331
  • 5
  • 91
  • 94