Re: Strange behavior of function date_trunc

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