Обсуждение: Speed of different procedural language
I have a few small functions which I need to write. They will be hopefully quick running but will happen on almost every delete, insert and update on my database (for audit purposes). I know I should be writing these in C but that's a bit beyond me. I was going to try PL/Python or PL/Perl or even PL/Ruby. Has anyone any idea which language is fastest, or is the data access going to swamp the overhead of small functions? Thanks, Ben
On Sun, Dec 18, 2005 at 01:10:21AM -0000, Ben Trewern wrote: > I know I should be writing these in C but that's a bit beyond me. I was > going to try PL/Python or PL/Perl or even PL/Ruby. Has anyone any idea > which language is fastest, or is the data access going to swamp the overhead > of small functions? I'm not sure if it's what you ask for, but there _is_ a clear difference between the procedural languages -- I've had a 10x speed increase from rewriting PL/PgSQL stuff into PL/Perl, for instance. I'm not sure which ones would be faster, though -- I believe Ruby is slower than Perl or Python generally, but I don't know how it all works out in a PL/* setting. /* Steinar */ -- Homepage: http://www.sesse.net/
> On Sun, Dec 18, 2005 at 01:10:21AM -0000, Ben Trewern wrote: > > I know I should be writing these in C but that's a bit beyond me. I was > > going to try PL/Python or PL/Perl or even PL/Ruby. Has anyone any idea > > which language is fastest, or is the data access going to swamp the > overhead > > of small functions? > > I'm not sure if it's what you ask for, but there _is_ a clear difference > between the procedural languages -- I've had a 10x speed increase from > rewriting PL/PgSQL stuff into PL/Perl, for instance. I'm not sure which > ones > would be faster, though -- I believe Ruby is slower than Perl or Python > generally, but I don't know how it all works out in a PL/* setting. So far, I use plpgsql for everything...queries being first class and all...I don't have any performance problems with it. I have cut the occasional C routine, but for flexibility not for speed. PL/Perl routines cannot directly execute each other, meaning you can't pass high level objects between them like refcursors. YMMV Since most database apps are bound by the server one way or another I would imagine you should be choosing a language on reasons other than performance. Maybe Ben you could provide an example of what you are trying to do that is not fast enough? Merlin
On Wed, Dec 21, 2005 at 12:06:47PM +0100, Steinar H. Gunderson wrote: > On Sun, Dec 18, 2005 at 01:10:21AM -0000, Ben Trewern wrote: > > I know I should be writing these in C but that's a bit beyond me. I was > > going to try PL/Python or PL/Perl or even PL/Ruby. Has anyone any idea > > which language is fastest, or is the data access going to swamp the overhead > > of small functions? > > I'm not sure if it's what you ask for, but there _is_ a clear difference > between the procedural languages -- I've had a 10x speed increase from > rewriting PL/PgSQL stuff into PL/Perl, for instance. The difference is clear only in specific cases; just because you saw a 10x increase in some cases doesn't mean you can expect that kind of increase, or indeed any increase, in others. I've seen PL/pgSQL beat all other PL/* challengers handily many times, especially when the function does a lot of querying and looping through large result sets. I tend to use PL/pgSQL except in cases where PL/pgSQL can't do what I want or the job would be much easier in another language (e.g., string manipulation, for which I'd use PL/Perl or PL/Ruby). Even then I might use the other language only to write small functions that a PL/pgSQL function could call. As Merlin suggested, maybe Ben could tell us what he wants to do that he thinks should be written in C or a language other than PL/pgSQL. Without knowing what problem is to be solved it's near impossible to recommend an appropriate tool. -- Michael Fuhr
On Wed, Dec 21, 2005 at 02:24:42PM -0700, Michael Fuhr wrote: > The difference is clear only in specific cases; just because you > saw a 10x increase in some cases doesn't mean you can expect that > kind of increase, or indeed any increase, in others. I've seen > PL/pgSQL beat all other PL/* challengers handily many times, > especially when the function does a lot of querying and looping > through large result sets. That's funny, my biggest problems with PL/PgSQL have been (among others) exactly with large result sets... Anyhow, the general idea is: It _does_ matter which one you use, so you'd better test if it matters to you :-) /* Steinar */ -- Homepage: http://www.sesse.net/
On Wed, Dec 21, 2005 at 10:38:10PM +0100, Steinar H. Gunderson wrote: > On Wed, Dec 21, 2005 at 02:24:42PM -0700, Michael Fuhr wrote: > > The difference is clear only in specific cases; just because you > > saw a 10x increase in some cases doesn't mean you can expect that > > kind of increase, or indeed any increase, in others. I've seen > > PL/pgSQL beat all other PL/* challengers handily many times, > > especially when the function does a lot of querying and looping > > through large result sets. > > That's funny, my biggest problems with PL/PgSQL have been (among others) > exactly with large result sets... Out of curiosity, do you have a simple test case? I'd be interested in seeing what you're doing in PL/pgSQL that's contradicting what I'm seeing. -- Michael Fuhr
On Wed, Dec 21, 2005 at 03:10:28PM -0700, Michael Fuhr wrote: >> That's funny, my biggest problems with PL/PgSQL have been (among others) >> exactly with large result sets... > Out of curiosity, do you have a simple test case? I'd be interested > in seeing what you're doing in PL/pgSQL that's contradicting what > I'm seeing. I'm not sure if I have the code anymore (it was under 7.4 or 8.0), but it was largely scanning through ~2 million rows once, noting differences from the previous rows as it went. In that case, I didn't benchmark against any of the other PL/* languages, but it was pretty clear that even on a pretty speedy Opteron, it was CPU bound, which it really shouldn't have been. /* Steinar */ -- Homepage: http://www.sesse.net/
On Thu, Dec 22, 2005 at 02:08:23AM +0100, Steinar H. Gunderson wrote: > On Wed, Dec 21, 2005 at 03:10:28PM -0700, Michael Fuhr wrote: > >> That's funny, my biggest problems with PL/PgSQL have been (among others) > >> exactly with large result sets... > > Out of curiosity, do you have a simple test case? I'd be interested > > in seeing what you're doing in PL/pgSQL that's contradicting what > > I'm seeing. > > I'm not sure if I have the code anymore (it was under 7.4 or 8.0), but it was > largely scanning through ~2 million rows once, noting differences from the > previous rows as it went. > > In that case, I didn't benchmark against any of the other PL/* languages, but > it was pretty clear that even on a pretty speedy Opteron, it was CPU bound, > which it really shouldn't have been. Try looping through two million rows with PL/Perl or PL/Tcl and you'll probably see significantly worse performance than with PL/pgSQL -- so much worse that I'd be surprised to see those languages make up the difference with whatever processing they'd be doing for each row unless it was something they're particularly good at and PL/pgSQL is particularly bad at. In 8.1 PL/Perl has a couple of ways to fetch query results: spi_exec_query to fetch all the rows at once into a single data structure, and spi_query/spi_fetchrow to fetch the rows one at a time. In my tests with one million rows, spi_exec_query was around 8 times slower than a loop in PL/pgSQL, not to mention requiring a lot of memory. spi_query/spi_fetchrow was about 25 times slower but didn't require the amount of memory that spi_exec_query did. A PL/Tcl function that used spi_exec was about 10 times slower than PL/pgSQL, or only slightly slower than PL/Perl and spi_exec_query. If you didn't benchmark the two million row query, do you have an example that you did benchmark? I don't doubt that PL/Perl and other langauges can do some things faster than PL/pgSQL, but looping through large result sets doesn't seem to be one of them. -- Michael Fuhr
Michael Fuhr <mike@fuhr.org> writes:
> Try looping through two million rows with PL/Perl or PL/Tcl and
> you'll probably see significantly worse performance than with
> PL/pgSQL -- so much worse that I'd be surprised to see those languages
> make up the difference with whatever processing they'd be doing for
> each row unless it was something they're particularly good at and
> PL/pgSQL is particularly bad at.
I'd expect plpgsql to suck at purely computational tasks, compared to
the other PLs, but to win at tasks involving database access. These
are two sides of the same coin really --- plpgsql is tightly tied to the
PG query execution engine, to the extent of using it even for simply
adding 2 and 2, but that also gives it relatively low overhead for
invoking a database query. Perl, Tcl, et al have their own
computational engines and can easily beat the PG SQL engine for simple
arithmetic and string-pushing. But they pay a high overhead for
calling back into the database engine.
regards, tom lane
Tom Lane wrote: > I'd expect plpgsql to suck at purely computational tasks, compared to > the other PLs, but to win at tasks involving database access. These There you go...pl/pgsql is pretty much required learning (it's not hard). For classic data processing tasks, it is without peer. I would generalize that a large majority of tasks fall under this category. pl/pgsql is quick, has a low memory profile, and you can cut sql directly in code instead of through a proxy object...I could go on and on about how useful and important that is. merlin