What is the benefit of marking function as IMMUTABLE?

Question

I was looking through some of the plpgsql functions in our codebase and realized that most of them cannot be set IMMUTABLE only because they contain reference to now(), e.g.

CREATE OR REPLACE FUNCTION find_nearest_time (
  target_times time without time zone[],
  target_dows int[],
  timezone_name text
)
RETURNS timestamp with time zone
AS $$
DECLARE
  target_time time without time zone;
  nearest_time timestamp with time zone;
  next_time timestamp with time zone;
BEGIN
  IF (
    array_length(target_times, 1) = 0 OR
    array_length(target_dows, 1) = 0
  ) THEN
    RETURN null;
  END IF;
FOR add_day IN 0..6
  LOOP
    FOREACH target_time IN ARRAY find_nearest_time.target_times
    LOOP
      next_time := (date_trunc('day', (now() at time zone find_nearest_time.timezone_name) - target_time::interval) + interval '1 day' + target_time::interval) at time zone find_nearest_time.timezone_name;
      next_time := next_time + interval '1 day' * add_day;
  IF (
    extract(isodow from next_time) = ANY(target_dows) AND
    (
      nearest_time IS NULL OR
      nearest_time &gt; next_time
    )
  ) THEN
    nearest_time := next_time;
  END IF;
END LOOP;

IF (nearest_time IS NOT NULL) THEN
  RETURN nearest_time;
END IF;

END LOOP;
RETURN next_time;
END
$$
LANGUAGE plpgsql;

However, we can easily rewrite the above function to where now() is passed as a parameter, e.g.

CREATE OR REPLACE FUNCTION find_nearest_time (
  seed_time timestamp with time zone,
  target_times time without time zone[],
  target_dows int[],
  timezone_name text
)
RETURNS timestamp with time zone
AS $$
DECLARE
  target_time time without time zone;
  nearest_time timestamp with time zone;
  next_time timestamp with time zone;
BEGIN
  IF (
    array_length(target_times, 1) = 0 OR
    array_length(target_dows, 1) = 0
  ) THEN
    RETURN null;
  END IF;
FOR add_day IN 0..6
  LOOP
    FOREACH target_time IN ARRAY find_nearest_time.target_times
    LOOP
      next_time := (date_trunc('day', (find_nearest_time.seed_time at time zone find_nearest_time.timezone_name) - target_time::interval) + interval '1 day' + target_time::interval) at time zone find_nearest_time.timezone_name;
      next_time := next_time + interval '1 day' * add_day;
  IF (
    extract(isodow from next_time) = ANY(target_dows) AND
    (
      nearest_time IS NULL OR
      nearest_time &gt; next_time
    )
  ) THEN
    nearest_time := next_time;
  END IF;
END LOOP;

IF (nearest_time IS NOT NULL) THEN
  RETURN nearest_time;
END IF;

END LOOP;
RETURN next_time;
END
$$
LANGUAGE plpgsql
IMMUTABLE;

This way the function can be marked as IMMUTABLE.

What is the benefit of marking function as IMMUTABLE in this case?

score 0 · Answer 1 · answered Feb 12 '21 at 00:39

The PostgreSQL documentation 37.7. Function Volatility Categories states the following on using the IMMUTABLE keyword with functions:

An IMMUTABLE function cannot modify the database and is guaranteed to return the same results given the same arguments forever. This category allows the optimizer to pre-evaluate the function when a query calls it with constant arguments. For example, a query like SELECT ... WHERE x = 2 + 2 can be simplified on sight to SELECT ... WHERE x = 4, because the function underlying the integer addition operator is marked IMMUTABLE.

For best optimization results, you should label your functions with the strictest volatility category that is valid for them.

In short, the stricter volatility keyword you can use in your function definitions, the more optimized they'll perform in your queries. I suspect this is especially true when using the IMMUTABLE keyword because of it guaranteeing the function is determinsitic as stated here:

...guaranteed to return the same results given the same arguments forever.

What is the benefit of marking function as IMMUTABLE?

1 Answers1