| Lists: | pgsql-performance |
|---|
| From: | "Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com> |
|---|---|
| To: | "Cosimo Streppone" <cosimo(at)streppone(dot)it> |
| Cc: | "Postgresql Performance list" <pgsql-performance(at)postgresql(dot)org> |
| Subject: | Re: High end server and storage for a PostgreSQL OLTP system |
| Date: | 2005-02-01 15:30:59 |
| Message-ID: | 6EE64EF3AB31D5448D0007DD34EEB3412A75FE@Herge.rcsinc.local |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-performance |
> Hi all,
> 1) What kind of performance gain can I expect switching from
> 7.1 to 7.4 (or 8.0)? Obviously I'm doing my own testing,
> but I'm not very impressed by 8.0 speed, may be I'm doing
> testing on a low end server...
8.0 gives you savepoints. While this may not seem like a big deal at
first, the ability to handle exceptions inside pl/pgsql functions gives
you much more flexibility to move code into the server. Also, recent
versions of pl/pgsql give you more flexibility with cursors, incuding
returning them outside of the function.
Corollary: use pl/pgsql. It can be 10 times or more faster than query
by query editing.
You also have the parse/bind interface. This may not be so easily to
implement in your app, but if you are machine gunning your server with
queries, use parameterized prepared queries and reap 50% + performance,
meaning lower load and quicker transaction turnaround time.
Merlin
| From: | Cosimo Streppone <cosimo(at)streppone(dot)it> |
|---|---|
| To: | Merlin Moncure <merlin(dot)moncure(at)rcsonline(dot)com> |
| Cc: | Postgresql Performance list <pgsql-performance(at)postgresql(dot)org> |
| Subject: | Re: High end server and storage for a PostgreSQL OLTP system |
| Date: | 2005-02-01 21:00:50 |
| Message-ID: | 41FFEE02.3080605@streppone.it |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-performance |
Merlin Moncure wrote:
> Corollary: use pl/pgsql. It can be 10 times or more faster than query
> by query editing.
Merlin, thanks for your good suggestions.
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.
> 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.
--
Cosimo