From: | "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | "Daniel Caune" <daniel(dot)caune(at)ubisoft(dot)com> |
Cc: | "Gerardo Herzig" <gherzig(at)fmed(dot)uba(dot)ar>, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: SQL stored function inserting and returning data in a row. |
Date: | 2008-01-11 14:17:53 |
Message-ID: | 162867790801110617v3776adeat6c5466af5ed8dd8e@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin pgsql-sql |
Hello
> By the way, is there any performance difference between pure SQL and
> PL/pgSQL stored functions? If I remember correctly there was such a
> distinction between pure SQL statement and PL/PLSQL stored procedures
> (Oracle), in the sense that PL/PLSQL stored procedures are executed
> within the PL/PLSQL engine which sends pure SQL statements to the SQL
> engine for execution. There is a little overhead between PL/PLSQL and
> SQL engines.
>
create or replace function test1(integer)
returns integer as
$$select $1;$$
language sql immutable;
create
or replace function test2(integer)
returns integer as
$$begin return $1; end$$
language plpgsql immutable;
postgres=# select count(*) from (select test1(i) from
generate_series(1,100000) g(i)) f;
count
--------
100000
(1 row)
Time: 123,532 ms
postgres=# select count(*) from (select test2(i) from
generate_series(1,100000) g(i)) f;
count
--------
100000
(1 row)
Time: 123,877 ms
but if you forgot immutable
postgres=# create or replace function test3(integer)
returns integer as
$$begin return $1; end$$
language plpgsql;
CREATE FUNCTION
Time: 430,258 ms
postgres=# select count(*) from (select test3(i) from
generate_series(1,100000) g(i)) f;
count
--------
100000
(1 row)
Time: 472,150 ms
Regards
Pavel Stehule
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Sullivan | 2008-01-11 15:24:49 | Re: Need some info. |
Previous Message | Daniel Caune | 2008-01-11 13:55:02 | Re: SQL stored function inserting and returning data in a row. |
From | Date | Subject | |
---|---|---|---|
Next Message | Erik Jones | 2008-01-11 16:01:16 | Re: trigger for TRUNCATE? |
Previous Message | Daniel Caune | 2008-01-11 13:55:02 | Re: SQL stored function inserting and returning data in a row. |