Strange behavior of function date_trunc
От | Pavel Luzanov |
---|---|
Тема | Strange behavior of function date_trunc |
Дата | |
Msg-id | 6b4f0c89-3c15-d5bd-7909-62cc33b1a106@postgrespro.ru обсуждение исходный текст |
Ответы |
Re: Strange behavior of function date_trunc
|
Список | 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
В списке pgsql-general по дате отправления: