Re: Strange behavior of function date_trunc

Hello,

On 06.05.2021 16:44, Tom Lane wrote:
> 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.

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