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
