Thread: stable and immutable functions in GROUP BY clauses.

Hello,

Stable and immutable functions do not improve performance when used within the GROUP BY clause.
Here, the function will be called for each row.

To avoid it, I can replace the funtion by its arguments within GROUP BY.

Maybe this hint is worth a note within the documentation on Function Volatility.

I have the situation where queries are generating by the application and it would be a pain to extend the "query
builder"
in order to avoid this performance issue.
So I wonder if it would be possible for the query planner to recognize such cases and optimize the query internally ?

best regards,
Marc Mamin


here an example to highlight possible performance loss:

create temp table ref ( i int, r int);
create temp table val ( i int, v int);

insert into ref select s,s%2 from generate_series(1,10000)s;
insert into val select s,s%2 from generate_series(1,10000)s;

create or replace function getv(int) returns int as
$$ select v+1 from val where i=$1; $$ language SQL stable;

explain analyze select  getv(r) from ref group by r;
Total runtime: 5.928 ms

explain analyze select  getv(r) from ref group by getv(r);
Total runtime: 3980.012 ms

-- and more reasonably with an index:

create unique index val_ux on val(i);

explain analyze select  getv(r) from ref group by r;
Total runtime: 4.278 ms

explain analyze select  getv(r) from ref group by getv(r);
Total runtime: 68.758 ms

>
> Hello,
>
> Stable and immutable functions do not improve performance when used within the GROUP BY clause.
> Here, the function will be called for each row.
>
> To avoid it, I can replace the funtion by its arguments within GROUP BY.

Shame on me !
This is of course bullsh...  It has nothing to do with immutability and can only applies to few cases

e.g: it's fine for select x+1 ... group by  x,
but not for        select x^2 ... group by  x

Marc Mamin