In response to Robert Bernabe <robert_bernabe@yahoo.com>:
> Hi All,
> I've been tasked to evaluate PG as a possible replacement of our
> MS SQL 2000 solution. Our solution is 100% stored procedure/function
> centric.
I've trimmed 99% of your email out, because it's not relevant to my
answer.
Fact is, it's pretty much impossible for anyone to give specific help
because you've obviously got a large, complex operation going on here,
and have not provided any real details. The reality is that we'd
probably have to see your code to give any specific help.
However, I can help you with an approach to fixing it. Based on your
description of the problem, I would guess that there are some differences
in best practices between MSSQL and PG that are what's hurting your
application once it's ported to PG. Basically, you just need to isolate
them and adjust.
I recommend enabling full query logging with timing on the PG server.
In the postgresql.conf file, set the following:
log_min_duration_statement = 0
Note that this will result in a LOT of log information being written,
which will invariably make the application run even slower on PG, but
for tuning purposes it's invaluable as it will log every SQL statement
issued with the time it took to run.
From there, look for the low-hanging fruit. I recommend running your
tests a few times, then running the logs through pgFouine:
http://pgfouine.projects.postgresql.org/
Once you've identified the queries that are taking the most time, start
adjusting the queries and/or the DB schema to improve the timing. In
my experience, you'll usually find 2 or 3 queries that are slowing the
thing down, and the performance will come up to spec once they're
rewritten (or appropriate indexes added, or whatever) EXPLAIN can
be your friend once you've found problematic queries.
Another piece of broadly useful advice is to install the pgbuffercache
addon and monitor shared_buffer usage to see if you've got enough. Also
useful is monitoring the various statistics in the pg_stat_database
table.
--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/
wmoran@collaborativefusion.com
Phone: 412-422-3463x4023
