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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
