plpgsql vs. SQL performance
| От | google@newtopia.com (Michael Pohl) |
|---|---|
| Тема | plpgsql vs. SQL performance |
| Дата | |
| Msg-id | da4ea47.0305181831.2ff55bc8@posting.google.com обсуждение |
| Ответы |
Re: plpgsql vs. SQL performance
|
| Список | 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@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@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
В списке pgsql-general по дате отправления: