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 по дате отправления: