Re: Strange behavior of function date_trunc - Mailing list pgsql-general

From Pavel Luzanov
Subject Re: Strange behavior of function date_trunc
Date
Msg-id 4f5d539a-4a76-a184-eb4f-ef4dce2c0858@postgrespro.ru
Whole thread Raw
In response to Re: Strange behavior of function date_trunc  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Strange behavior of function date_trunc
List pgsql-general
Hello,

On 06.05.2021 16:44, Tom Lane wrote:
Pavel Luzanov <p.luzanov@postgrespro.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

pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: trigger impacting insertion of records
Next
From: Tom Lane
Date:
Subject: Re: Strange behavior of function date_trunc