Re: Speed of different procedural language

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