It is very likely that the date_trunc function in the following example is executed for each line of the query. Although it marked as a STABLE and could only be called once.
It could, but that's just an option - the database may do that, but it's not required to do it. In this case it might be beneficial, but it'd make the planner more complex etc.
Yeah, there simply is not any provision for caching the results of stable functions in the way Pavel seems to be imagining. People have played around with patches for that, but nothing's been accepted.
Thank you for the clarification.It's not very obvious, at least for me.
--
Pavel Luzanov
Postgres Professional: https://postgrespro.com
The Russian Postgres Company