stable and immutable functions in GROUP BY clauses. - Mailing list pgsql-performance

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