SEO

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/

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Thanks. That's kind of what I expected. I'll try using Perl or Python and
do a few benchmarks if I get the chance.

Regards,

Ben

""Steinar H. Gunderson"" <sgunderson@bigfoot.com> wrote in message
news:20051221110647.GA17360@uio.no...
> 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/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>

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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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/

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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/

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster