Thread: 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