Strange behavior of function date_trunc

From: Pavel Luzanov <p(dot)luzanov(at)postgrespro(dot)ru>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Strange behavior of function date_trunc
Date: 2021-05-05 13:23:09
Message-ID: 6b4f0c89-3c15-d5bd-7909-62cc33b1a106@postgrespro.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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 >= date_trunc('day',
'2021-05-05'::timestamptz);
                                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
 Function Scan on generate_series g  (cost=0.00..15.00 rows=333
width=8) (actual time=2801.884..3263.328 rows=2332801 loops=1)
   Filter: (x >= date_trunc('day'::text, '2021-05-05
00:00:00+03'::timestamp with time zone))
   Rows Removed by Filter: 10713600
 Planning Time: 0.040 ms
 Execution Time: 3336.657 ms

When replacing date_trunc with now, the query is much faster:

EXPLAIN (ANALYZE)
SELECT * FROM generate_series('2021-01-01', '2021-06-01', '1
s'::interval) AS g(x)
WHERE g.x >= now();
                                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
 Function Scan on generate_series g  (cost=0.00..15.00 rows=333
width=8) (actual time=1648.777..1845.430 rows=2275325 loops=1)
   Filter: (x >= now())
   Rows Removed by Filter: 10771076
 Planning Time: 0.039 ms
 Execution Time: 1918.767 ms

The variant with now works almost as fast as with the constant. This
suggests me that perhaps date_trunc is being executed for every line of
the query:

EXPLAIN (ANALYZE)
SELECT * FROM generate_series('2021-01-01', '2021-06-01', '1
s'::interval) AS g(x)
WHERE g.x >= '2021-05-05'::timestamptz;
                                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
 Function Scan on generate_series g  (cost=0.00..12.50 rows=333
width=8) (actual time=1628.743..1826.841 rows=2332801 loops=1)
   Filter: (x >= '2021-05-05 00:00:00+03'::timestamp with time zone)
   Rows Removed by Filter: 10713600
 Planning Time: 0.033 ms
 Execution Time: 1901.680 ms

In this regard, I have two questions:
1. How can I find out exactly how many times the date_trunc function has
been executed? So far, these are just my assumptions.
2. If date_trunc is indeed called multiple times, why is this happening?

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tomas Vondra 2021-05-05 13:55:04 Re: Strange behavior of function date_trunc
Previous Message Arne Henrik Segtnan 2021-05-05 11:26:43 Re: PostgreSQL upgrade from 10 to 12 fails with "ERROR: column r.relhasoids does not exist at character 1616"