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

Предыдущее
От: Tzvetan Tzankov
Дата:
Сообщение: Re: disk space usage enlarging despite vacuuming
Следующее
От: Martin Foster
Дата:
Сообщение: Re: PostgreSQL Performance on OpenBSD