Speed of postgres compared to ms sql, is this article/comment off?

novnov <novnovice@gmail.com> writes:

http://www.simple-talk.com/sql/sql-server-2005/sql-server,-postgressql-and-fish-curry/
at the bottom of the page is a post by Andrew Clarke that says pgsql is much
slower in comparison to ms sql.

With no details, it's hard to rate that as anything but content-free FUD.

I don't doubt that he saw a serious performance differential on whatever
his test case was, but without seeing the test case it's impossible to
know why, or whether it would be fixable. Nor do we know what PG
version he was testing. Given that he mentions Windows 2000, it's not
unlikely that he was testing a Cygwin port of PG 7.something, in which
case just moving to an 8.1 native port would help a lot.

Also, given that he mentions that complex joins were bad, it could be
that he forgot to analyze, or had a corner case where the row estimates
were bad anyway, or had an outer-join scenario where reordering of
the outer joins is really essential for performance. (We've fixed the
latter as of 8.2, I think, but I will agree it's a serious weak spot in
existing PG releases.) But this is all speculation...

regards, tom lane

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.

Tom Lane-2 wrote:

novnov <novnovice@gmail.com> writes:

http://www.simple-talk.com/sql/sql-server-2005/sql-server,-postgressql-and-fish-curry/
at the bottom of the page is a post by Andrew Clarke that says pgsql is
much
slower in comparison to ms sql.

With no details, it's hard to rate that as anything but content-free FUD.

I don't doubt that he saw a serious performance differential on whatever
his test case was, but without seeing the test case it's impossible to
know why, or whether it would be fixable. Nor do we know what PG
version he was testing. Given that he mentions Windows 2000, it's not
unlikely that he was testing a Cygwin port of PG 7.something, in which
case just moving to an 8.1 native port would help a lot.

Also, given that he mentions that complex joins were bad, it could be
that he forgot to analyze, or had a corner case where the row estimates
were bad anyway, or had an outer-join scenario where reordering of
the outer joins is really essential for performance. (We've fixed the
latter as of 8.2, I think, but I will agree it's a serious weak spot in
existing PG releases.) But this is all speculation...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

--
View this message in context: http://www.nabble.com/Speed-of-postgres-compared-to-ms-sql%2C-is-this-article-comment-off--tf2614575.html#a7298762
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

_____

From: novnov [mailto:novnovice@gmail.com]
To: pgsql-general@postgresql.org
Sent: Sat, 11 Nov 2006 20:15:08 -0500
Subject: Re: [GENERAL] Speed of postgres compared to ms sql, is this

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.We use both native PostgreSQL and SQL Server Desktop Edition (MSDE),
but not generally for exactly the same things.

We use PostgreSQL on a multi-platform (HP-UX, Solaris, Linux, Windows)
commercial application suite. Works great handles tens of gigabytes
per day in data insertions, updates, and retirements with almost
zero administration. Servers running the database stay up forever.
We chose PostgreSQL as the best value after analyzing competing commercial
and open source RDBMS systems in a formal DAR (Decision Analysis Resolution)
process.

We use MSDE on a government project where both the specific database version
and committee-designed database schema from hell were mandated.
There are plenty of instances when SQL Server is less than optimal and
maddening. The MSDE version also limits connections, throttles performance
for simultaneous queries, etc. And there's no way you'd ever pick it for
multiplatform use or portability.

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.

On 11/11/06, novnov <novnovice@gmail.com> 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 have a ton of experience with postgresql and mysql, and a fair
amount with ms sql. Database performance is hard to nail down, it
means different things to different people. However, I it is my
personal opinion (backed up with hands-on experience) that PostgreSQL
has been pulling away from its major competitors in performance since
about 7.4 release. Actually, I think ms sql is a decent database and
it's a good choice if you like integration with ms tools. However,
like many products I think it topped out in value around year 2000 and
am not so sure about the current direction. Note that I only know
about yukon by what I've read about it.

merlin

OK, thanks everyone, I gather from the responses that postgres performance
won't be an issue for me then. If MS SQL Server and Postgres are in the same
ballpark performance-wise, which seems to be the upshot of your comments, no
problem. I'd only have worried if there was something like the major
difference between the two with more complicated queries. I am puzzled by
the commentor's post to the article, it could be FUD of course but didn't
particularly sound like the commentor was anti pgsql.

Merlin Moncure-2 wrote:

On 11/11/06, novnov <novnovice@gmail.com> 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 have a ton of experience with postgresql and mysql, and a fair
amount with ms sql. Database performance is hard to nail down, it
means different things to different people. However, I it is my
personal opinion (backed up with hands-on experience) that PostgreSQL
has been pulling away from its major competitors in performance since
about 7.4 release. Actually, I think ms sql is a decent database and
it's a good choice if you like integration with ms tools. However,
like many products I think it topped out in value around year 2000 and
am not so sure about the current direction. Note that I only know
about yukon by what I've read about it.

merlin

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

--
View this message in context: http://www.nabble.com/Speed-of-postgres-compared-to-ms-sql%2C-is-this-article-comment-off--tf2614575.html#a7326226
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

PostgreSQL follows SQL compliance, as does MS SQL Server. 'complicated
queries' are left to the creativity of the SQL'er - squeler ;)

There are of course proprietary differences in 'enhancements.'
Some things that you can write in MS SQL, don't apply to Oracle/PostgreSQL/MySQL
and many permutations of the above comment amongst the varying RDBMS'.

On Mon, 13 Nov 2006, novnov wrote:

OK, thanks everyone, I gather from the responses that postgres performance
won't be an issue for me then. If MS SQL Server and Postgres are in the same
ballpark performance-wise, which seems to be the upshot of your comments, no
problem. I'd only have worried if there was something like the major
difference between the two with more complicated queries. I am puzzled by
the commentor's post to the article, it could be FUD of course but didn't
particularly sound like the commentor was anti pgsql.

Merlin Moncure-2 wrote:

On 11/11/06, novnov <novnovice@gmail.com> 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 have a ton of experience with postgresql and mysql, and a fair
amount with ms sql. Database performance is hard to nail down, it
means different things to different people. However, I it is my
personal opinion (backed up with hands-on experience) that PostgreSQL
has been pulling away from its major competitors in performance since
about 7.4 release. Actually, I think ms sql is a decent database and
it's a good choice if you like integration with ms tools. However,
like many products I think it topped out in value around year 2000 and
am not so sure about the current direction. Note that I only know
about yukon by what I've read about it.

merlin

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

--
Louis Gonzales
louis.gonzales@linuxlouis.net
http://www.linuxlouis.net

On Mon, 2006-11-13 at 15:36, novnov wrote:

OK, thanks everyone, I gather from the responses that postgres performance
won't be an issue for me then. If MS SQL Server and Postgres are in the same
ballpark performance-wise, which seems to be the upshot of your comments, no
problem. I'd only have worried if there was something like the major
difference between the two with more complicated queries. I am puzzled by
the commentor's post to the article, it could be FUD of course but didn't
particularly sound like the commentor was anti pgsql.

I will say this. Most other databases are more forgiving of bad
queries. Make a bad query and postgresql is more likely to punish you
for it. But I've seen production oracle servers make pretty bad query
plans too because someone used a non-selective sub-select that the
planner couldn't work around.

I love postgresql, and I think the query planner has made leaps and
bounds since I started working with it. But it is not designed to run
bad sql quickly.

I'm the OP, ran into this today and thought I'd give it some exposure

http://spyced.blogspot.com/2006/12/benchmark-postgresql-beats-stuffing.html

From the article

Benchmark: PostgreSQL beats the stuffing out of MySQL

This is interesting, because the conventional wisdom of idiots on slashdot
continues to be "use postgresql if you need advanced features, but use mysql
if all you care about is speed," despite all the head-to-head benchmarks
I've seen by third parties showing PostgreSQL to be faster under load.

Josh Rovero wrote:

_____

From: novnov [mailto:novnovice@gmail.com]
To: pgsql-general@postgresql.org
Sent: Sat, 11 Nov 2006 20:15:08 -0500
Subject: Re: [GENERAL] Speed of postgres compared to ms sql, is this

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.We use both native PostgreSQL and SQL
Server Desktop Edition (MSDE),
but not generally for exactly the same things.

We use PostgreSQL on a multi-platform (HP-UX, Solaris, Linux, Windows)
commercial application suite. Works great handles tens of gigabytes
per day in data insertions, updates, and retirements with almost
zero administration. Servers running the database stay up forever.
We chose PostgreSQL as the best value after analyzing competing commercial
and open source RDBMS systems in a formal DAR (Decision Analysis
Resolution)
process.

We use MSDE on a government project where both the specific database
version
and committee-designed database schema from hell were mandated.
There are plenty of instances when SQL Server is less than optimal and
maddening. The MSDE version also limits connections, throttles
performance
for simultaneous queries, etc. And there's no way you'd ever pick it for
multiplatform use or portability.

--
View this message in context: http://www.nabble.com/Speed-of-postgres-compared-to-ms-sql%2C-is-this-article-comment-off--tf2614575.html#a7682549
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 11/13/06, Scott Marlowe <smarlowe@g2switchworks.com> wrote:

On Mon, 2006-11-13 at 15:36, novnov wrote:

OK, thanks everyone, I gather from the responses that postgres performance
won't be an issue for me then. If MS SQL Server and Postgres are in the same
ballpark performance-wise, which seems to be the upshot of your comments, no
problem. I'd only have worried if there was something like the major
difference between the two with more complicated queries. I am puzzled by
the commentor's post to the article, it could be FUD of course but didn't
particularly sound like the commentor was anti pgsql.

I will say this. Most other databases are more forgiving of bad
queries. Make a bad query and postgresql is more likely to punish you
for it.

Amen. When I migrated from MSSQL to PostgreSQL (4 years ago), I found
out exactly how seriously MS SQL coddles you when it comes to its "Oh,
I know what you really meant" query planning. I committed some sins
MS SQL covered up nicely and PostgreSQL flat out crawled when
presented to it.

However, I suspect that if I tried those bad queries with a current
version of PostgreSQL they would run much better, given all the work
that has been put in over the last few years.

- Ian

2006/12/4, Ian Harding <harding.ian@gmail.com>:

On 11/13/06, Scott Marlowe <smarlowe@g2switchworks.com> wrote:

On Mon, 2006-11-13 at 15:36, novnov wrote:

OK, thanks everyone, I gather from the responses that postgres performance
won't be an issue for me then. If MS SQL Server and Postgres are in the same
ballpark performance-wise, which seems to be the upshot of your comments, no
problem. I'd only have worried if there was something like the major
difference between the two with more complicated queries. I am puzzled by
the commentor's post to the article, it could be FUD of course but didn't
particularly sound like the commentor was anti pgsql.

I will say this. Most other databases are more forgiving of bad
queries. Make a bad query and postgresql is more likely to punish you
for it.

Amen. When I migrated from MSSQL to PostgreSQL (4 years ago), I found
out exactly how seriously MS SQL coddles you when it comes to its "Oh,
I know what you really meant" query planning. I committed some sins
MS SQL covered up nicely and PostgreSQL flat out crawled when
presented to it.

However, I suspect that if I tried those bad queries with a current
version of PostgreSQL they would run much better, given all the work
that has been put in over the last few years.

I've seen the exact same behaviour last year with pg 8.1 vs. MS SQL 2k.
It was an unexpected shock, but it's really not that hard to make
pgsql run much faster.
There are simply things which pgsql executes painfully slow if you
don't write them the way the server expects you to. This hasn't
changed in 8.1, but then again, it's not nearly the biggest problem I
have with this specific RDBMS. ;)

t.n.a.

"Tomi N/A" <hefest@gmail.com> writes:

2006/12/4, Ian Harding <harding.ian@gmail.com>:

Amen. When I migrated from MSSQL to PostgreSQL (4 years ago), I found
out exactly how seriously MS SQL coddles you when it comes to its "Oh,
I know what you really meant" query planning. I committed some sins
MS SQL covered up nicely and PostgreSQL flat out crawled when
presented to it.

I've seen the exact same behaviour last year with pg 8.1 vs. MS SQL 2k.
It was an unexpected shock, but it's really not that hard to make
pgsql run much faster.
There are simply things which pgsql executes painfully slow if you
don't write them the way the server expects you to.

These sorts of reports would be far more helpful if they contained some
specifics. What queries does MSSQL do better than Postgres, exactly?

regards, tom lane

I suppose comparing postgres running on a single processor laptop to sql server running
on a dual processor machine wouldn't help you determine what sql server does better.

If it might let me know.

Aside from maybe having the planner reorder joins for you I would guess that it is sql
servers support for parallelism.

Mike

On Tue, 05 Dec 2006 14:34:31 -0500, Tom Lane wrote

"Tomi N/A" <hefest@gmail.com> writes:

2006/12/4, Ian Harding <harding.ian@gmail.com>:

Amen. When I migrated from MSSQL to PostgreSQL (4 years ago), I found
out exactly how seriously MS SQL coddles you when it comes to its "Oh,
I know what you really meant" query planning. I committed some sins
MS SQL covered up nicely and PostgreSQL flat out crawled when
presented to it.

I've seen the exact same behaviour last year with pg 8.1 vs. MS SQL 2k.
It was an unexpected shock, but it's really not that hard to make
pgsql run much faster.
There are simply things which pgsql executes painfully slow if you
don't write them the way the server expects you to.

These sorts of reports would be far more helpful if they contained some
specifics. What queries does MSSQL do better than Postgres, exactly?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

--
Open WebMail Project (http://openwebmail.org)

2006/12/5, Tom Lane <tgl@sss.pgh.pa.us>:

These sorts of reports would be far more helpful if they contained some
specifics. What queries does MSSQL do better than Postgres, exactly?

You are of course correct, Tom.
I'm sorry I'm not in a position to replay what I've been doing a year
ago...I wish I could.

Obviously, I never had to worry about the concepts of vacuuming and
analysis (not that it's very difficult with pgsql: it just doesn't
exist as a concept with MSSQL).
Anyone calling my comment completely subjective would be completely
correct because that's what it was.

One type of query does come to mind, now that I think about it.
pgsql has trouble handling queries like
SELECT * FROM t0 WHERE t0.id_t1 IN (SELECT t1.id FROM t1 WHERE...)

The performance is a bit better when there's only one result in the
subselect so you can do:
SELECT * FROM t0 WHERE t0.id_t1 = (SELECT t1.id FROM t1 WHERE...)

When the subselect returns a lot of results, pgsql really takes it's time.

The first query, however, can be executed much, much (at least an
order of magnitude) quicker like this:
SELECT * FROM t0
LEFT OUTER JOIN t1 ON t1.id = t0.id_t1
WHERE t1.id IS NOT NULL

I didn't notice this kind of sensitivity with MSSQL, but again, I
can't easily reproduce what I've been doing.

Sorry for the original FUD-like report. Cheers,
t.n.a.

On Tue, 2006-12-05 at 16:32, Tomi N/A wrote:

One type of query does come to mind, now that I think about it.
pgsql has trouble handling queries like
SELECT * FROM t0 WHERE t0.id_t1 IN (SELECT t1.id FROM t1 WHERE...)

When the subselect returns a lot of results, pgsql really takes it's time.

Just wondering what version of pgsql you were using, as the in()
performance has been greatly improved in the newer versions.

What I noticed was that PostgreSQL was better under parallel load than
MSSQL server was. Our pgsql 7.2 server would routinely outrun the MSSQL
server (This was like 3 years ago) when they were both moderately
loaded. Of course, we didn't run a lot of where in () queries on the
pgsql server, we re-worked them to favor postgresql's query planner of
the time.

2006/12/5, Scott Marlowe <smarlowe@g2switchworks.com>:

On Tue, 2006-12-05 at 16:32, Tomi N/A wrote:

One type of query does come to mind, now that I think about it.
pgsql has trouble handling queries like
SELECT * FROM t0 WHERE t0.id_t1 IN (SELECT t1.id FROM t1 WHERE...)

When the subselect returns a lot of results, pgsql really takes it's time.

Just wondering what version of pgsql you were using, as the in()
performance has been greatly improved in the newer versions.

8.1.something

What I noticed was that PostgreSQL was better under parallel load than
MSSQL server was. Our pgsql 7.2 server would routinely outrun the MSSQL
server (This was like 3 years ago) when they were both moderately
loaded. Of course, we didn't run a lot of where in () queries on the
pgsql server, we re-worked them to favor postgresql's query planner of
the time.

We frequently run into the same basic RDBMS benchmarking problem:
basically any database can be fastest in a given context, depending on
how the query is expressed and what it does.

Cheers,
t.n.a.

Hi,

Tomi N/A wrote:

When the subselect returns a lot of results, pgsql really takes it's

time.

8.1.something

PostgreSQL 8.2 improved a lot for IN clauses with lots of values. I
think it now performs as good as an equal join query.

Regards

Markus

These sorts of reports would be far more helpful if they contained some
specifics. What queries does MSSQL do better than Postgres, exactly?

Our OR-patch was inspired by our customer migrating from MS SQL to postgres.
Next, index support of IS NULL. And, there is a huge difference in performance
for queries like
select * from a,b where a.f = b.f or ( a.f is null and b.f is null)

NULL support is fast in MS SQL because MS SQL doesn't follow SQL standard: index
in MS SQL believes that (NULL = NULL) is true.

--
Teodor Sigaev E-mail: teodor@sigaev.ru
WWW: http://www.sigaev.ru/

I didn't notice this kind of sensitivity with MSSQL, but again, I
can't easily reproduce what I've been doing.

Funny, although it was better at the IN type of query, so the difference was
not as great, MSSQL also typically did better with the query expressed as a
join--older versions, I haven't used it in quite a while.

--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 722-0567 voice