Date: Wed, 5 Dec 2007 13:25:44 -0500
From: Bill Moran <wmoran@collaborativefusion.com>
To: Robert Bernabe <robert_bernabe@yahoo.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Evaluation of PG performance vs MSDE/MSSQL 2000 (not
 2005)
Message-Id: <20071205132544.70151535.wmoran@collaborativefusion.com>
In-Reply-To: <37470.23740.qm@web51601.mail.re2.yahoo.com>
References: <37470.23740.qm@web51601.mail.re2.yahoo.com>
Organization: Collaborative Fusion
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

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.