Pavel Luzanov <p.luzanov@xxxxxxxxxxxxxx> 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
