Re: Strange behavior of function date_trunc

I will try to summarize what was said before.

We have discussed the details of executing STABLE functions in queries
of the form:
SELECT * FROM t WHERE col oper stable_func();

* Checking STABLE does not guarantee that the function will be executed
only once. If the table is scanned sequentially, the function is
executed for each row of the query.

* If the table has an index on the col column, the planner can choose to
scan the index. In this case, the STABLE mark gives the right to
calculate the function value once and use that value to search the index.

* In the case of a sequential scan, the total cost of the plan includes,
among other things, the cost of the function multiplied by the number of
rows.  For user-defined functions, the default cost is 100. It may be
worth changing this value for a more adequate estimate. Decreasing the
cost of a function will decrease the cost of a seq scan and vice versa.
Refining the function cost estimate will enable the planner to make a
more accurate choice between seq scan and index scan.

* If seq scan is preferred, you can avoid executing the function
multiple times by materializing the result of the function.

* There are two ways to materialize the result: a scalar subquery and a CTE.
    SELECT * FROM t WHERE col oper (SELECT stable_func();
    WITH m AS MATERIALIZED (SELECT stable_func() AS f) SELECT * FROM t,
m WHERE col oper m.f;

* When materializing a function result, the planner has no way to use
the function value to build the plan. Therefore, it will not be able to
use the statistics for the t.col to select the optimal plan.  The
generic algorithm will be used.

Thank you very much for sharing.

--
Pavel Luzanov
Postgres Professional: https://postgrespro.com
The Russian Postgres Company