Search code examples
postgresqlquery-optimizationquery-planner

Why plainning time is doubled when I call IMMUTABLE function with exatly same arguments second time?

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)
Solution

  • 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);