From: Mladen Gogala <mladen.gogala@vmsinfo.com>
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
Date: Tue, 07 Dec 2010 15:22:15 -0500
Message-ID: <4CFE9777.50406@vmsinfo.com> (raw)
In-Reply-To: <378e24d1e3af88a49fbbb7c4d26f441a@mail.gmail.com>
References: <378e24d1e3af88a49fbbb7c4d26f441a@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mladen.gogala@vmsinfo.com, tom@rockfordarearealtors.org
Subject: Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows
In-Reply-To: <4CFE9777.50406@vmsinfo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox