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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
