plpgsql vs. SQL performance

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

Responses

Browse pgsql-general by date

  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