Re: Strange behavior of function date_trunc

David, On 06.05.2021 17:28, David G. Johnston wrote: On Thu, May 6, 2021 at 6:44 AM Tom Lane > wrote: This case is the reason we invented the "stable" attribute to begin with.  People have since misinterpreted it as authorizing caching of function results

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 sequential

Re: Strange behavior of function date_trunc

Pavel Luzanov writes: > 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: The scalar subquery prevents the planner from seeing the actual comparison value, so it falls back to a default sel

Re: Strange behavior of function date_trunc

Hello, On 06.05.2021 16:44, Tom Lane wrote: Pavel Luzanov 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 th

Re: Strange behavior of function date_trunc

On Thu, May 6, 2021 at 6:44 AM Tom Lane wrote: > 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. > > This is a good paragraph...if something like

Re: Strange behavior of function date_trunc

Pavel Luzanov 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

Re: Strange behavior of function date_trunc

Hello, On 05.05.2021 16:55, Tomas Vondra wrote: Well, it'd not like date_trunc is executed for each row while now() is executed only once. The functions are executed for each row in both cases... A couple more experiments. Since I can't to track usage of system functions, I decided to play wit

Re: Strange behavior of function date_trunc

On 05.05.2021 17:11, Tom Lane wrote: Tomas Vondra writes: On 5/5/21 3:23 PM, Pavel Luzanov wrote: It is very likely that the date_trunc function in the following example is executed for each line of the query. Although it marked as a STABLE and could only be called once. It could, but that's

Re: Strange behavior of function date_trunc

Hello, On 05.05.2021 16:55, Tomas Vondra wrote: Well, it'd not like date_trunc is executed for each row while now() is executed only once. The functions are executed for each row in both cases, but now() is simply much cheaper - it just returns a value that is already calculated, while date_tr

Re: Strange behavior of function date_trunc

Tomas Vondra writes: > On 5/5/21 3:23 PM, Pavel Luzanov wrote: >> It is very likely that the date_trunc function in the following example >> is executed for each line of the query. Although it marked as a STABLE >> and could only be called once. > It could, but that's just an option - the datab

Re: Strange behavior of function date_trunc

On 5/5/21 3:23 PM, Pavel Luzanov wrote: Hello, It is very likely that the date_trunc function in the following example is executed for each line of the query. Although it marked as a STABLE and could only be called once. It could, but that's just an option - the database may do that, bu

Strange behavior of function date_trunc

Hello, It is very likely that the date_trunc function in the following example is executed for each line of the query. Although it marked as a STABLE and could only be called once. EXPLAIN (ANALYZE) SELECT * FROM generate_series('2021-01-01', '2021-06-01', '1 s'::interval) AS g(x) WHERE g.x