Re: [GENERAL] Speed of postgres compared to ms sql, is this article/comment
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. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Speed of postgres compared to ms sql, is this article/comment
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. signature.asc Description: Digital signature
Re: [GENERAL] Speed of postgres compared to ms sql, is this
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 [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Speed of postgres compared to ms sql, is this
2006/12/6, Markus Schiltknecht [EMAIL PROTECTED]: 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. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Speed of postgres compared to ms sql, is this
2006/12/4, Ian Harding [EMAIL PROTECTED]: On 11/13/06, Scott Marlowe [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Speed of postgres compared to ms sql, is this
Tomi N/A [EMAIL PROTECTED] writes: 2006/12/4, Ian Harding [EMAIL PROTECTED]: 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
Re: [GENERAL] Speed of postgres compared to ms sql, is this
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 [EMAIL PROTECTED] writes: 2006/12/4, Ian Harding [EMAIL PROTECTED]: 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) ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Speed of postgres compared to ms sql, is this
2006/12/5, Tom Lane [EMAIL PROTECTED]: 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. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Speed of postgres compared to ms sql, is this
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. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Speed of postgres compared to ms sql, is this
2006/12/5, Scott Marlowe [EMAIL PROTECTED]: 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. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Speed of postgres compared to ms sql, is this
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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Speed of postgres compared to ms sql, is this
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: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Speed of postgres compared to ms sql, is this
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:[EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Speed of postgres compared to ms sql, is this
On 11/13/06, Scott Marlowe [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Speed of postgres compared to ms sql, is this
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
Re: [GENERAL] Speed of postgres compared to ms sql, is this
On 11/11/06, novnov [EMAIL PROTECTED] 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
Re: [GENERAL] Speed of postgres compared to ms sql, is this
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 [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Speed of postgres compared to ms sql, is this
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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] http://www.linuxlouis.net ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Speed of postgres compared to ms sql, is this
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. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] 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. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Speed of postgres compared to ms sql, is this article/comment off?
novnov [EMAIL PROTECTED] 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
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. Tom Lane-2 wrote: novnov [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Speed of postgres compared to ms sql, is this
From: novnov [mailto:[EMAIL PROTECTED]To: pgsql-general@postgresql.orgSent: Sat, 11 Nov 2006 20:15:08 -0500Subject: 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 gigabytesper day in data insertions, updates, and retirements with almostzero 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 andmaddening. The MSDE version also limits connections, throttles performancefor simultaneous queries, etc. And there's no way you'd ever pick it formultiplatform use or portability.
