On 06.05.2021
16:44, Tom Lane wrote:
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.
I think I'm starting to understand! ))
I knew that the STABLE mark was not a guarantee for the value to be cached. The planner has the right to execute the function once, but this is not required. Now it is clear under what conditions this happens. Stable functions can be executed once, when they are used in an index _expression_. In other cases (in a select list, _expression_ for seq scan) they are evaluated for each row.
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.)
Yes, if the cost of the function is reduced to 3 (or less), than seq scan begins to be used. And the function is executed for each row.
It's clear now.
One thing remains unclear.
Why, if a scalar subquery is used to materialize the function value(even constant), then an inefficient index scan is chosen:
EXPLAIN (ANALYZE, SETTINGS)
SELECT * FROM t
WHERE t.x >= (SELECT '2021-01-01'::timestamptz);
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Index Scan using t_x_idx on t (cost=0.45..194740.46 rows=4348742 width=31) (actual time=2.831..26947.394 rows=13046401 loops=1)
Index Cond: (x >= $0)
InitPlan 1 (returns $0)
-> Result (cost=0.00..0.01 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=1)
Settings: random_page_cost = '1.1'
Planning Time: 0.077 ms
JIT:
Functions: 4
Options: Inlining false, Optimization false, Expressions true, Deforming true
Timing: Generation 0.720 ms, Inlining 0.000 ms, Optimization 0.184 ms, Emission 2.429 ms, Total 3.333 ms
Execution Time: 27262.793 ms
-- Pavel Luzanov Postgres Professional: https://postgrespro.com The Russian Postgres Company
