Thread: Speed of postgres compared to ms sql, is this article/comment off?
In an interview "An interview with Adam Machanic" at 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. I've read a fair number of posts recently debunking the usual "mysql is faster than pgsql" drone, but a comparison with ms sql is less often heard. Can someone who has first hand experience with both databases comment? Article is Aug 2006. I don't care if pgsql is somewhat slower than sql server 2005, but I do care if it's a lot slower, particularly running queries with complex joins. Here is the relavant part >>>> PostgreSQL! Although I have a certain fondness for it and very much hope it will eventually succeed, we must be realistic. Species have evolved in the time it takes to execute a decent bit of SQL. It runs sometimes at a tenth of the speed of SQLite. Have a look at some of the public comparative benchmarks. As an exercise, I once created a reasonably simple customer database containing a million customer records along with all the usual NAD data. I installed it on SQL Server and PostgreSQL. (thanks to the EMS tools, bless them). They were both on the same Windows 2000 box. The SQL Server system, on average, took a twentieth of the time to produce results from SQL, and the more joins, the more astonishing the difference. I'd assumed that I'd made some ghastly mistake in the installation of PostgreSQL so I got a PostgreSQL expert to check my installation. No mistake. He said that maybe it would run faster on Linux. I tried that, but failed to be excited. -- View this message in context: http://www.nabble.com/Speed-of-postgres-compared-to-ms-sql%2C-is-this-article-comment-off--tf2614575.html#a7297298 Sent from the PostgreSQL - general mailing list archive at Nabble.com.
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.
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.
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
2006/12/6, Markus Schiltknecht <markus@bluegap.ch>: > 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. Thats good to know. I'm looking forward to taking it for a spin...as soon as I make sure my data is safe. t.n.a.
There is another blog article comparing postgresql with mysql, where the postgres is slow thing is repeated and the benchmark tends to confirm. The blogger is new to postgres and does not represent the test as definative. http://wskills.blogspot.com/2007/01/postgresql-vs-mysql-benchmark.html First, I'm not an expert with either database and don't have anything useful to say on the topic. Second, quite a number of the folks here think postgres holds it own re performance very well, and I trust this crew. Third, anyone that cares and has meaningful comments to offer on the subject might consider posting a comment on that blog. Fourth, am I being a pita by posting this kind of message here? I do it because while I know it's an old and probably annoying topic for many of you, I don't like myths like postgres/slow mysql/fast being perpetuated. But if you all tell me to hang it up I will...I figured that informing the community re these articles might be a minor contribution. novnov wrote: > > In an interview "An interview with Adam Machanic" at > > 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. I've read a fair number of posts > recently debunking the usual "mysql is faster than pgsql" drone, but a > comparison with ms sql is less often heard. Can someone who has first hand > experience with both databases comment? Article is Aug 2006. > > I don't care if pgsql is somewhat slower than sql server 2005, but I do > care if it's a lot slower, particularly running queries with complex > joins. > > Here is the relavant part >>>>> > PostgreSQL! Although I have a certain fondness for it and very much hope > it will eventually succeed, we must be realistic. Species have evolved in > the time it takes to execute a decent bit of SQL. It runs sometimes at a > tenth of the speed of SQLite. Have a look at some of the public > comparative benchmarks. > > As an exercise, I once created a reasonably simple customer database > containing a million customer records along with all the usual NAD data. I > installed it on SQL Server and PostgreSQL. (thanks to the EMS tools, bless > them). They were both on the same Windows 2000 box. > > The SQL Server system, on average, took a twentieth of the time to produce > results from SQL, and the more joins, the more astonishing the difference. > I'd assumed that I'd made some ghastly mistake in the installation of > PostgreSQL so I got a PostgreSQL expert to check my installation. No > mistake. He said that maybe it would run faster on Linux. I tried that, > but failed to be excited. > -- View this message in context: http://www.nabble.com/Speed-of-postgres-compared-to-ms-sql%2C-is-this-article-comment-off--tf2614575.html#a8415104 Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Wed, Jan 17, 2007 at 09:41:03AM -0800, novnov wrote: > > There is another blog article comparing postgresql with mysql, where the > postgres is slow thing is repeated and the benchmark tends to confirm. The > blogger is new to postgres and does not represent the test as definative. > > http://wskills.blogspot.com/2007/01/postgresql-vs-mysql-benchmark.html Database is 80MB only, tiny. And his machine isn't very good either. > Fourth, am I being a pita by posting this kind of message here? I do it > because while I know it's an old and probably annoying topic for many of > you, I don't like myths like postgres/slow mysql/fast being perpetuated. But While interesting, people just throwing it on a machine and testing a database much smaller than the amount of RAM they have, just isn't meaningful. Proper tests where they actually print what they tested, now that's useful. But much more rare. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.