Guy Rouillier <guyr-ml1@burntmail.com> writes:
> Unfortunately, I think the stored procedure implementation in PG itself
> introduces significant overhead. See thread "Writing most code in
> Stored Procedures" from August 2007. I converted an application from
> that BigDBMS we are not allowed to mention to PG. Code is Java, stored
> procs were written in PL/Java. On the exact same hardware, I couldn't
> get any where near the throughput I was getting in BigDBMS. The procs
> are trivial - just wrappers for insert statements. After I exhausted
> all alternatives, I replaced the stored proc invocation in the code with
> inserts. Then, PG was able to achieve the same throughput as BigDBMS.
I doubt that what you were measuring there was either procedure call
overhead or java computational speed; more likely it was the cost of
calling back out of java, through pl/java's JDBC emulation, down through
SPI, to re-execute the same INSERT that you then decided to execute
directly. In particular, if pl/java's JDBC doesn't know anything about
caching query plans, performance for simple inserts could be expected to
go into the tank just because of that. (Whether it actually does or
not, I have no idea --- but I would expect it to be a lot less mature
than the mainstream JDBC driver for PG, and that took years to get
smart about prepared queries ...)
Without knowing where the bottleneck actually is, it's unreasonable to
assume that it would hurt a different use-case.
regards, tom lane