Re: plpgsql vs. SQL performance

google(at)newtopia(dot)com (Michael Pohl) writes:
> I am occasionally seeing plpgsql functions significantly underperform
> their straight SQL equivalents.

Almost certainly, a different query plan is getting chosen in the
plpgsql case.

One common cause of this problem is sloppiness about datatypes. You
have declared $1 and $2 of the plpgsql function to be integer; are
the columns they're being compared to also integer? If not, that's
likely preventing indexscans from being used.

Another common cause of this sort of thing is that the planner makes
conservative choices because it doesn't have exact runtime values for
the constants in the query. What you are really comparing here is

plpgsql:
select ... where user_id = $1 and status_id = $2

SQL:
select ... where user_id = 1 and status_id = 2

In the latter case the planner can consult pg_statistic to get a pretty
good idea about how many rows will be selected, whereas in the former
case its guess is much more approximate. (I'd still expect an indexscan
to get picked though, unless you have *very* skewed data statistics for
these columns. Usually it's inequalities that push the planner to use
a seqscan in these cases.)

regards, tom lane