Обсуждение: Performance: sql functions v. plpgsql v. plperl

Last night, I was doing some amateurish benchmarking and found that,
contrary to my (admittedly uninformed) expectation, sql functions seem
*slower* than plsql functions. Even for very simple things, like

'SELECT CASE WHEN $1 RETURN $1 ELSE $2 END'

were slower than the plsql

begin
  if $1 then
    return $1;
  else
    return $2;
  end if;
end;

by about 15%.

However, my benchmarking was the type usually dreaded: a single person,
playing around in a scripting language, running the test one a time
seqeuentially, and just timing the results.

Is there any real data on this?



I also tried plperl v plpgsql, and found that, probably not surprisingly,
there was about a 15% advantage to plpgsql. Of course, many things can be
written much simpler in perl (such as string handling functions). Even so,
though, a find-the-first-letter-of-all-significant-words function written
about 1.5 years ago in plpgsql (a pretty awful, nested, letter-by-letter
parser) was only about twice as slow as the perl
split() replacement. Looks like our little plpgsql is quite a speed demon.

(as always, IANAPB [ I am not a professional benchmarker ], YMMV)


--
Joel Burton   <jburton@scw.org>
Director of Information Systems, Support Center of Washington

Joel Burton <jburton@scw.org> writes:
> Last night, I was doing some amateurish benchmarking and found that,
> contrary to my (admittedly uninformed) expectation, sql functions seem
> *slower* than plsql functions.

IIRC, sql functions are re-parsed/planned on each execution, whereas
plpgsql functions cache their parse trees and execution plans.
Depending on exactly what you were doing, that might explain the
difference.

            regards, tom lane

On Wed, 25 Apr 2001, Tom Lane wrote:

> Joel Burton <jburton@scw.org> writes:
> > Last night, I was doing some amateurish benchmarking and found that,
> > contrary to my (admittedly uninformed) expectation, sql functions seem
> > *slower* than plsql functions.
>
> IIRC, sql functions are re-parsed/planned on each execution, whereas
> plpgsql functions cache their parse trees and execution plans.
> Depending on exactly what you were doing, that might explain the
> difference.

Why is this?

I'm just delving into the source code in earnest for the first time, so,
forgive any awful errors, but it seems like we have the plumbing for this
in views/rules... couldn't the parse tree be cached from this for each
backend?

Or are SQL functions mildly deprecated since they could always be replaced
by the plpgsql function

begin
  return ...
end;

?


--
Joel Burton   <jburton@scw.org>
Director of Information Systems, Support Center of Washington

Joel Burton <jburton@scw.org> writes:
> couldn't the parse tree be cached from this for each backend?

Yes, if someone wanted to work on it ...

            regards, tom lane

On Wed, Apr 25, 2001 at 05:03:51PM -0400, Tom Lane wrote:
> Joel Burton <jburton@scw.org> writes:
> > couldn't the parse tree be cached from this for each backend?
>
> Yes, if someone wanted to work on it ...

 It needs global query plan cache and integrate it to SQL function
handler. The usable cache is available at my ftp, but I haven't time
to continue on this. Volunteers? :-)

            Karel