Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows

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