In response to Robert Bernabe <robert_bernabe@xxxxxxxxx>: > 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@xxxxxxxxxxxxxxxxxxxxxxx Phone: 412-422-3463x4023 ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
