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 40e1d393-0ab7-6da9-5005-0648b7bce0ce@postgrespro.ru
Whole thread Raw
In response to Re: Strange behavior of function date_trunc  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-general
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





pgsql-general by date:

Previous
From: Thomas Boussekey
Date:
Subject: Re: [RPM/CentOS7] Need to disable repo_gpgcheck on pgdg-common when using RPM version 42.0-17.1
Next
From: Pavel Luzanov
Date:
Subject: Re: Strange behavior of function date_trunc