2

Compare:

enter image description here

At first case I call same find_period with same arguments. Because the function is IMMUTABLE I suppose that plan for it will be reused (same function, same arguments, same plan), but seems it is not reused Why?

Source function:

CREATE OR REPLACE FUNCTION "find_period" (in _start timestamptz, in _interval interval, in _target timestamptz)
 RETURNS tstzrange
 LANGUAGE sql
 IMMUTABLE RETURNS NULL ON NULL INPUT
 AS $$
    SELECT CASE
    WHEN _interval = INTERVAL '00:00:00' THEN
      tstzrange( _start, _start, '[]' )
    ELSE (
      SELECT CASE WHEN max( date ) = _target
        THEN tstzrange( max( date ) -_interval, max( date ) )
        ELSE tstzrange( max( date ), max( date ) +_interval )
      END
      FROM generate_series( _start, _target, _interval ) t (date )
    ) END
    WHERE _start < _target  OR  _interval = INTERVAL '00:00:00'
$$

And query:

EXPLAIN ANALYZE SELECT find_period( 
   '2020-04-03',  
   INTERVAL '1day', 
   '9999-01-01' 
)

UPD
With EXPLAIN ( ANALYZE, buffers, timing ) buffers for first call is 12808 VS 6404 for second:

db=> select version();
                                                     version                              
------------------------------------------------------------------------------------------
 PostgreSQL 13.1 (Debian 13.1-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian
(1 row)

db=> EXPLAIN ( ANALYZE, buffers, timing ) SELECT find_period( 
   '2020-04-03',  
   INTERVAL '1day', 
   '9999-01-01' 
), find_period( 
db(>    '2020-04-03',  
db(>    INTERVAL '1day', 
db(>    '9999-01-01' 
db(> );
                                     QUERY PLAN                                      
-------------------------------------------------------------------------------------
 Result  (cost=0.00..0.01 rows=1 width=64) (actual time=0.003..0.003 rows=1 loops=1)
 Planning:
   Buffers: temp read=12808 written=12808
 Planning Time: 3215.465 ms
 Execution Time: 0.023 ms
(5 rows)

db=> EXPLAIN ( ANALYZE, buffers, timing ) SELECT find_period( 
db(>    '2020-04-03',  
db(>    INTERVAL '1day', 
db(>    '9999-01-01' 
db(> );
                                     QUERY PLAN                                      
-------------------------------------------------------------------------------------
 Result  (cost=0.00..0.01 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=1)
 Planning:
   Buffers: shared hit=97, temp read=6404 written=6404
 Planning Time: 1583.670 ms
 Execution Time: 0.017 ms
(5 rows)
Eugen Konkov
  • 22,193
  • 17
  • 108
  • 158

1 Answers1

2

Different from what you expect, PostgreSQL calls the function twice, because it does not expend extra planning effort to check if you call the same function with the same constants twice.

Do the reasonable thing:

SELECT x, x
FROM find_period('2020-04-03', INTERVAL '1day', '9999-01-01' ) AS f(x);
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • If I understand correct: `extra planning effort` does not worth (despite it takes extra 1.5s) because I write 'ill' query. Would it be to benefit if implemented `EXPLAIN (ANALYZE HINT)` which will report hints about such mistakes and probably hints about missed index or so? – Eugen Konkov Apr 23 '21 at 10:27
  • 1
    The extra effort will make planning for many queries slower, while only a few queries would benefit. That would be a net loss. Besides, it is easy to work around the problem. Your `EXPLAIN (ANALYZE HINT)` sounds nice, but I personally believe that artificial intelligence is not there yet. – Laurenz Albe Apr 23 '21 at 10:33