stable and immutable functions in GROUP BY clauses.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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





[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux