Thread: scalar plpgsql functions and their stability flags
Good day experts...
Question on scalar plpgsql functions stability flags (immutable, stable) regarding how it works in sql queries.
It is clear that for immutable/stable functions with constant parameters,
query planner could/should calculate value in a parse time and use it directly in query, or at least once per query.
But it is unclear for me what exactly should/can happens when parameters are bounded not to constant values but to query fields.
In such a case there could be some caching mechanics involved for parameters combinations and result values.
Like building a hash table for that or something similar.
Can someone give me guidance on this matter.
What limits the usefulness of such a mechanism, if it exists.
Thank you.
Question on scalar plpgsql functions stability flags (immutable, stable) regarding how it works in sql queries.
It is clear that for immutable/stable functions with constant parameters,
query planner could/should calculate value in a parse time and use it directly in query, or at least once per query.
But it is unclear for me what exactly should/can happens when parameters are bounded not to constant values but to query fields.
In such a case there could be some caching mechanics involved for parameters combinations and result values.
Like building a hash table for that or something similar.
Can someone give me guidance on this matter.
What limits the usefulness of such a mechanism, if it exists.
Thank you.
Victor Dobrovolsky <booby.stager@gmail.com> writes:
> It is clear that for immutable/stable functions with constant parameters,
> query planner could/should calculate value in a parse time and use it
> directly in query, or at least once per query.
Immutable, yes, stable, no.
Awhile back there was a draft patch to cache outputs of stable functions
after running them once in a query, but I don't think anyone's still
working on that. IIRC we were having a hard time convincing ourselves
that the extra bookkeeping would pay for itself.
> But it is unclear for me what exactly should/can happens when parameters
> are bounded not to constant values but to query fields.
> In such a case there could be some caching mechanics involved for
> parameters combinations and result values.
> Like building a hash table for that or something similar.
No such mechanism exists. Again, there would be a lot of tradeoffs
involved and it's difficult to say if it'd be a win.
regards, tom lane