From: | Marc Mamin <M(dot)Mamin(at)intershop(dot)de> |
---|---|
To: | "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | stable and immutable functions in GROUP BY clauses. |
Date: | 2013-08-26 11:03:55 |
Message-ID: | B6F6FD62F2624C4C9916AC0175D56D880CE16355@jenmbs01.ad.intershop.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Rafael Martinez | 2013-08-26 12:33:46 | SQL statement over 500% slower with 9.2 compared with 9.1 |
Previous Message | girish subbaramu | 2013-08-26 07:23:32 | Re: PostgreSQL 9.2.4 very slow on laptop with windows 8 |