Does PostgreSQL cache function calls?

Question

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

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
– Clockwork-Muse, Commented Dec 21, 2013 at 14:44
@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
– Erwin Brandstetter, Commented Dec 23, 2013 at 19:57

Pavel Stehule · Accepted Answer · 2013-12-21 11:58:35Z

16

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.

answered Dec 21, 2013 at 11:58

Pavel Stehule

46.6k6 gold badges103 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Radim Vansa Over a year ago

Is this answer still valid in 2020?

Pavel Stehule Over a year ago

There was some patches, but it was not merged, and I did some simple test, and it I think so it is still valid.

Marmite Bomber Over a year ago

Seems to be still valid on 14.1 see similar answer and discussion here

Collectives™ on Stack Overflow

Does PostgreSQL cache function calls?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related