Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows

What I was really after was a quick comparison between the two. I did not
create anything special, just the two tables. One table SQL generated the
records for me. I did not tweak anything after installing either system.
There was a primary key on the ID field of both tables, no indexes though
in either system. The second table had 1 record in it. The hardware it
is running on is fairly good, dual Xeon CPUs, 4 GB of RAM, Raid 5. Btw,
the cost for MS SQL 2008 R2 is ~$14,000 for 2 cpus,
http://www.cdw.com/shop/products/default.aspx?EDC=2167810 . That is why I
am pursuing this. :)

Here is the ASP.net code that I was running
Dim starttime As Date = Date.Now
Dim endtime As Date
Dim reader As NpgsqlDataReader
Dim output2 As String = ""

Dim oConn As New
NpgsqlConnection("Server=192.168.1.5;Port=5432;Userid=postgres;Password=12
345;Protocol=3;SSL=false;Pooling=true;MinPoolSize=1;MaxPoolSize=20;Encodin
g=UNICODE;Timeout=15;SslMode=Disable;Database=tomtemp")
oConn.Open()
Dim x As Integer = 0
'For x = 0 To 1000 'uncomment to insert records.
'Dim command As New NpgsqlCommand("insert into pgtemp1(name,
address, city, state) values ('Tom" & x & "','123" & x & " main
st','rockford',1) ", oConn) 'meant for loop to put in 1,000 records in
pgtemp1 table
'Dim command As New NpgsqlCommand("insert into pgtemp2(statename,
stateid, other) values ('Illinois',1,'This is a lot of fun') ", oConn)
'only sends 1 record into the table pgtemp2
'command.ExecuteNonQuery()
'Next

'join table and read 1000 rows.
Dim command As New NpgsqlCommand("select
name,address,city,state,statename,stateid,other from pgtemp1 left join
pgtemp2 on state=stateid", oConn)
reader = command.ExecuteReader()
While reader.read()
output2 += "<tr><td>" & reader("name") & "</td><td>" &
reader("address") & "</td><td>" & reader("city") & "</td><td>" &
reader("statename") & "</td><td>" & reader("other") & "</td></tr>"
End While
oConn.Close()
readeroutput.text =
"<table><tr><td>Name:</td><td>Address:</td><td>City:</td><td>State</td><td
>Other</td></tr>" & output2 & "</table>"

endtime = Date.Now
Dim runtime As String
runtime = endtime.Subtract(starttime).TotalSeconds
output.text = starttime.ToString & " " & runtime

The SQL is a straight convert from MS SQL code. I did not tweak either
system.

From EXPLAIN ANALYZE I can see the query ran much faster.
"Nested Loop Left Join (cost=0.00..138.04 rows=1001 width=1298) (actual
time=0.036..4.679 rows=1001 loops=1)"
" Join Filter: (pgtemp1.state = pgtemp2.stateid)"
" -> Seq Scan on pgtemp1 (cost=0.00..122.01 rows=1001 width=788)
(actual time=0.010..0.764 rows=1001 loops=1)"
" -> Materialize (cost=0.00..1.01 rows=1 width=510) (actual
time=0.000..0.001 rows=1 loops=1001)"
" -> Seq Scan on pgtemp2 (cost=0.00..1.01 rows=1 width=510)
(actual time=0.006..0.008 rows=1 loops=1)"
"Total runtime: 5.128 ms"

The general question comes down to, can I expect decent perfomance from
Postgresql compared to MSSQL. I was hoping that Postgresql 9.0 beat MSSQL
2000 since MS 2000 is over 10 years old.

Thanks,
Tom Polak
Rockford Area Association of Realtors
815-395-6776 x203

The information contained in this email message is intended only for the
use of the individual or entity named. If the reader of this email is not
the intended recipient or the employee or agent responsible for delivering
it to the intended recipient, you are hereby notified that any
dissemination, distribution or copying of this email is strictly
prohibited. If you have received this email in error, please immediately
notify us by telephone and reply email. Thank you.

Although this email and any attachments are believed to be free of any
viruses or other defects that might affect any computer system into which
it is received and opened, it is the responsibility of the recipient to
ensure that it is free of viruses, and the Rockford Area Association of
Realtors hereby disclaims any liability for any loss or damage that
results.

-----Original Message-----
From: pgsql-performance-owner(at)postgresql(dot)org
[mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of Andy Colson
Sent: Tuesday, December 07, 2010 2:23 PM
To: Kenneth Marshall
Cc: Richard Broersma; Justin Pitts; pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] Compared MS SQL 2000 to Postgresql 9.0 on Windows

On 12/7/2010 2:10 PM, Kenneth Marshall wrote:
> On Tue, Dec 07, 2010 at 11:56:51AM -0800, Richard Broersma wrote:
>> On Tue, Dec 7, 2010 at 11:43 AM, Andy Colson<andy(at)squeakycode(dot)net>
wrote:
>>
>>> In PG the first statement you fire off (like an "insert into" for
example)
>>> will start a transaction. ?If you dont commit before you disconnect
that
>>> transaction will be rolled back. ?Even worse, if your program does not
>>> commit, but keeps the connection to the db open, the transaction will
stay
>>> open too.
>>
>> Huh - is this new? I always thought that every statement was wrapped
>> in its own transaction unless you explicitly start your own. So you
>> shouldn't need to commit before closing a connection if you never
>> opened a transaction to begin with.
>>
>>
>> --
>> Regards,
>> Richard Broersma Jr.
>>
>
> The default of autocommit unless explicitly starting a transaction with
> BEGIN is the normal behavior that I have seen as well.
>
> Cheers,
> Ken

Crikey! You're right. I need to be more careful with my assumptions.

I maintain that people need to be more careful with pg transactions.
I've seen several posts about "idle in transaction". But its not as bad
as I made out. My confusion comes from the library I use to hit PG,
which fires off a "begin" for me, and if I dont explicitly commit, it
gets rolled back.

sorry, it was confused between framework and PG.

-Andy