Re: High end server and storage for a PostgreSQL OLTP system

> By now, our system has never used "stored procedures" approach,
> due to the fact that we're staying on the minimum common SQL features
> that are supported by most db engines.
> I realize though that it would provide an heavy performance boost.

I feel your pain. Well, sometimes you have to bite the bullet and do a
couple of implementation specific hacks in especially time sensitive
components.

> > You also have the parse/bind interface
>
> This is something I have already engineered in our core classes
> (that use DBI + DBD::Pg), so that switching to 8.0 should
> automatically enable the "single-prepare, multiple-execute" behavior,
> saving a lot of query planner processing, if I understand correctly.

Yes. You save the planning step (which adds up, even for trivial plans).
The 'ExexPrepared' variant of prepared statement execution also provides
substantial savings (on server cpu load and execution time) because the
statement does not have to be parsed. Oh, and network traffic is reduced
correspondingly.

I know that the perl people were pushing for certain features into the
libpq library (describing prepared statements, IIRC). I think this
stuff made it into 8.0...have no clue about DBD::pg.

If everything is working the way it's supposed to, 8.0 should be faster
than 7.1 (like, twice faster) for what you are probably trying to do.
If it isn't, something else is wrong and it's very likely a solvable
problem.

In short, in pg 8.0, statement by statement query execution is highly
optimizeable at the driver level, much more so than 7.1. Law of
Unintended Consequences aside, this will translate into direct benefits
into your app if it uses this application programming model.

Merlin