stable-vs-immutable.sql

psql (9.6.3, server 10.1)
WARNING: psql major version 9.6, server major version 10.
         Some psql features might not work.
Type "help" for help.

postgres=# CREATE FUNCTION date_round(base_date timestamptz, round_interval INTERVAL) RETURNS timestamptz AS $BODY$
postgres$# SELECT TO_TIMESTAMP((EXTRACT(epoch FROM $1)::INTEGER + EXTRACT(epoch FROM $2)::INTEGER / 2)
postgres$#                 / EXTRACT(epoch FROM $2)::INTEGER * EXTRACT(epoch FROM $2)::INTEGER)
postgres$# $BODY$ LANGUAGE SQL STABLE;
CREATE FUNCTION
postgres=# EXPLAIN ANALYZE
postgres-# SELECT date_round(g, '5 minutes')
postgres-# FROM generate_series(now(), now()+'1 year', '5 minutes') g;
                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Function Scan on generate_series g  (cost=0.01..27.51 rows=1000 width=8) (actual time=12.720..363.389 rows=105121 loops=1)
 Planning time: 0.176 ms
 Execution time: 682.337 ms
(3 rows)

postgres=# ALTER FUNCTION date_round(base_date timestamptz, round_interval INTERVAL) IMMUTABLE;
ALTER FUNCTION
postgres=# EXPLAIN ANALYZE
postgres-# SELECT date_round(g, '5 minutes')
postgres-# FROM generate_series(now(), now()+'1 year', '5 minutes') g;
                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Function Scan on generate_series g  (cost=0.01..260.01 rows=1000 width=8) (actual time=13.236..759.049 rows=105121 loops=1)
 Planning time: 0.314 ms
 Execution time: 1085.569 ms
(3 rows)

postgres=#