How does Postgres optimise IMMUTABLE C-lang aggregate functions?
I have an aggregate whose implementation is defined in C. As an example, the corresponding sql schema for the state function looks something like:
CREATE OR REPLACE FUNCTION "my_aggregate_sfunc"(
"internal_state" INTERNAL,
"next_data_value" integer
) RETURNS INTERNAL
IMMUTABLE PARALLEL SAFE
LANGUAGE c
AS 'MODULE_PATHNAME', 'my_aggregate_sfunc';
I understand the idea behind IMMUTABLE
: it tells postgres that the return value is derived solely from the parameters, and that the function has no side-effects.
I also understand that the optimiser can perform certain optimisations based on the function being IMMUTABLE
.
I would like to understand what this means in practice for aggregates implemented in C. Do these optimisation opportunities actually exist? How different would the performance be if the function was defined as VOLATILE
?
The volatility of the state change function in an aggregate has no performance impact at all.
Function volatility makes a difference in queries like
SELECT ... FROM tab WHERE col = func(42);
With a STABLE
or IMMUTABLE
function, the function would only be executed once, whereas a VOLATILE
function has to be executed for every table row.