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
