Re: Strange behavior of function date_trunc

Pavel Luzanov <p(dot)luzanov(at)postgrespro(dot)ru> writes:
> Does having an index allow the function value to be cached?

For an indexscan, the comparison value is evaluated once and used to
search the index. The point of the "stable" marking is actually to
promise that this will give the same result as the naive interpretation
of a WHERE clause, ie that the WHERE expression is notionally evaluated
at every row.

This case is the reason we invented the "stable" attribute to begin
with. People have since misinterpreted it as authorizing caching of
function results, but that's not what it was intended for.

> The second question. What is the reason for choosing an index scan?

Probably the planner is picking that precisely to reduce the number
of calls of the user-defined function. Since you left the function's
cost as default, which for PL functions is 100x the default cost of
a built-in function, that could well be a large enough number to
change the plan choice. (You could experiment with altering the
COST property to see where the plan changes.)

regards, tom lane