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_trunc has to parse and truncate the
> value, etc.
>

Thanks for the explanation.

> You can use CTE to execute it just once, I think:
>
>   with x as (select date_trunc('day', '2021-04-01'::timestamptz) as x)
>   select * from t where a > (select x from x);

I think it could be even easier with scalar subquery:

EXPLAIN (ANALYZE)
SELECT * FROM generate_series('2021-01-01', '2021-06-01', '1
s'::interval) AS g(x)
WHERE g.x >= (SELECT date_trunc('day', '2021-05-05'::timestamptz));
                                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
 Function Scan on generate_series g  (cost=0.02..12.51 rows=333
width=8) (actual time=1615.436..1815.724 rows=2332801 loops=1)
   Filter: (x >= $0)
   Rows Removed by Filter: 10713600
   InitPlan 1 (returns $0)
     ->  Result  (cost=0.00..0.01 rows=1 width=8) (actual
time=0.005..0.005 rows=1 loops=1)
 Planning Time: 0.051 ms
 Execution Time: 1889.434 ms

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