Re: Evaluation of PG performance vs MSDE/MSSQL 2000 (not 2005)

On Dec 5, 2007 2:13 AM, Robert Bernabe <robert_bernabe(at)yahoo(dot)com> wrote:
>
> 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. It's
> a report generation system whose sole task is to produce text files filled
> with processed data that is post-processed by a secondary system. Basically
> options are selected via a web interface and all these parameters are passed
> unto the stored procedure and then the stored procedure would run and in the
> process call other stored procedures until eventually a single formatted
> text file is produced.
> I decided on Fedora Core 7 and the 8.3 Beta release of Enterprise DB
> PostgreSQL. I decided to port 1 stored procedure plus it's several support
> stored procedures into pl/pgsql from T-SQL and compare the performance by

Noble, but if you're a postgresql beginner, you might want to take a
pass on running beta code. You might be hitting a corner case,
performance wise, and never know it.

A few pointers.
1: Up your shared_buffers to 512M or so.
2: Up work_mem to 16M

Now, use the poor man's debugging tool for your stored procs, raise notice

create or replace function testfunc() returns int as $$
DECLARE
tm text;
cnt int;
BEGIN
select timeofday() into tm;
RAISE NOTICE 'Time is now %',tm;
select count(*) into cnt from accounts;
select timeofday() into tm;
RAISE NOTICE 'Time is now %',tm;
RETURN 0;
END;
$$ language plpgsql;

Once you've found what's running slow, narrow it down to a specific part.