From: | google(at)newtopia(dot)com (Michael Pohl) |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | plpgsql vs. SQL performance |
Date: | 2003-05-19 02:31:53 |
Message-ID: | da4ea47.0305181831.2ff55bc8@posting.google.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I am occasionally seeing plpgsql functions significantly underperform
their straight SQL equivalents. A simple example I ran into this
evening:
create or replace function pl_get_user_item_count(int, int)
returns int as '
declare
input_user_id alias for $1;
input_status_id alias for $2;
item_count int;
begin
select
into item_count count(id)
from
"item"
where
user_id = input_user_id and
status_id = input_status_id;
return item_count;
end;
' language 'plpgsql';
This function is taking around 2.11 seconds to execute, vs. 0.09 for
the identical SQL:
[michael(at)server1 ~]$ time psql -c "select pl_get_user_item_count(1,2)"
swap_dev pl_get_user_item_count
------------------------
9
(1 row)
0.000u 0.010s 0:02.11 0.4% 0+0k 0+0io 229pf+0w
[michael(at)server1 ~]$ time psql -c "select count(id) from item where
user_id = 1 and status_id = 2" swap_dev
count
-------
9
(1 row)
0.000u 0.000s 0:00.09 0.0% 0+0k 0+0io 229pf+0w
I can provide table schema and 'explain' output if that would help.
My general question is: Should I expect certain SQL to execute
significantly more slowly when wrapped in a plpgsql function? My db
experience is mainly with Sybase, and I'm used to performance boosts
with Transact-SQL stored procedures. It seems strange to see any
penalty at all for using a stored procedure, much less a harsh one as
in the example above.
Input appreciated.
thanks,
michael
From | Date | Subject | |
---|---|---|---|
Next Message | Joel Rees | 2003-05-19 06:34:22 | Re: FW: See that security pack from the Microsoft |
Previous Message | James Gregory | 2003-05-19 02:15:37 | Re: table inheritance and foreign keys |