On Sat, 2006-11-11 at 19:15, novnov wrote: > I agree with what you say. And I'd still be interesting in hearing of first > hand experience with the speed of the two databases from someone who is > 'good' at both. The article commentor was obviously not a pgsql expert. I've > heard recently that pgsql is as fast as mysql, so it seems odd that ms sql > would be faster than pgsql. The actual test, what was use to benchmark, > would make a difference of course. > > I'm hoping someone with a good handle on both databases has direct > experience can chime in here.
My experience with comparing the two was several years (and versions) ago. We had a quad Xeon- 550MHz with 4 gig ram machine running Win-2K and SQL Server and a Dual P-III 750MHz with 1.5 gig ram running PostgreSQL 7.2.x on RH 7.2. The windows machine had a large RAID array with many drives, the pgsql machine had two Ultra-320 SCSI drives in a linux sw mirror. Like I said, it was several years ago. The SQL Server was maintained by a Microsoft certified professional of some type. I maintained the pgsql box. Both machines were tested during off hours. We had an internal client who was taking data from a lotus notes "database" (I use the term loosely) and creating insert statements to put them into SQL Server. He was having to truncate at 4k (or is it 8k) because of the size limit of a single row back then in sql server. The time to extract and then insert the 40,000 or so records from Notes to SQL Server was almost 40 minutes exactly. Ran the same test on postgresql, with only a few changes. Some of these articles were 64k to 256k of data, and were being truncated to 4k/8k for MSSQL. So, we were inserting noticeable more data into postgresql. Total time to extract / insert? about 12 minutes. My pgsql machine still showed itself as being pretty much idle, both in CPU and I/O usage, so I had him just created a batch sql file, and ran it on the bare pgsql server directly, no network overhead, no lotus overhead. Time to insert: 4.0 minutes. Changed it to copy from stdin format, time to insert: 2.5 minutes. We also tested several queries against this database, including many that joined >5 tables, and in every test, the pgsql machine handily beat the mssql machine, usually by a factor of 2 or more every time. I'm not saying MSSQL is always slower than pgsql. but I am saying that given competitive hardware and tuning, pgsql can hold its own. ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend