Message-ID: <4CFE9777.50406@vmsinfo.com>
Date: Tue, 07 Dec 2010 15:22:15 -0500
From: Mladen Gogala <mladen.gogala@vmsinfo.com>
Organization: VMS
User-Agent: Thunderbird 2.0.0.24 (X11/20101027)
MIME-Version: 1.0
To: Tom Polak <tom@rockfordarearealtors.org>
CC: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows
References: <378e24d1e3af88a49fbbb7c4d26f441a@mail.gmail.com>
In-Reply-To: <378e24d1e3af88a49fbbb7c4d26f441a@mail.gmail.com>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 8bit

Tom Polak wrote:
>
> We are in the process of deciding on how to proceed on a database 
> upgrade.  We currently have MS SQL 2000 running on Windows 2003 (on my 
> test server).  I was shocked at the cost for MS SQL 2008 R2 for a new 
> server (2 CPU license).  I started comparing DB’s and came across 
> postgresql.  It seemed to be exactly what I was after.  All of our 
> programming is in ASP.net.  Since I am running MSSQL 2000 I have no 
> benefit for .Net integration, so it is not a concern.
>
>  
>
> I ran a head to head test of MS SQL 2000 and Postgresql 9.0.  Both are 
> running on Windows 2003.  What I found was quite surprising and I am 
> wondering if anyone can point out what is going on here. 
> Here is the test I ran. 
> I created 2 tables, the main table had 5 fields with a serial ID 
> field.  The second table linked to table 1 for a state field.
>
> I had ASP.net via MSSQL create 1,000 records in the main table. Took 
> 9.85 seconds to complete.
> Next I had ASP.net via Postgresql create 1,000 records.  Took .65625 
> seconds.
> Postgresql smoked MS SQL server on that test.
>
>  
>
> Next test is to use ASP.net and join all 1,000 rows with table 2 and 
> then display the text out.
>
> MS SQL took 0.76 seconds to display
> select name,address,city,state,statename,stateid,other from pgtemp1 
> left join pgtemp2 on state=stateid
>
>  
>
> Then I did the same test via Postgresql and it took 8.85 seconds!  I 
> tried it again as I thought I did something wrong.  I did a few tweaks 
> such as increasing the shared buffers.  Still the best I could get it 
> to was 7.5 seconds.  This is insanely slow compared to MSSQL 2000.  
> What am I missing.  Here is my SQL statement for postgresql:
> select name,address,city,state,statename,stateid,other from pgtemp1 
> left join pgtemp2 on state=stateid
>
>  
>
> Any ideas on why the Postgres server is soooo much slower on the 
> joins?   I am trying to understand what is going on here so please 
> don’t flame me.  Any advice is appreciated. 
>
>  
>
>  
>
>
Are all structures the same? Are all indexes the same? What does 
"explain analyze" tell you?


-- 
 
Mladen Gogala 
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com 
The Leader in Integrated Media Intelligence Solutions