On Mon, 2005-06-06 at 12:00 -0400, Amit V Shah wrote: > Hi all, > > Thanks for your replies. > > I ran a very prelimnary test, and found following results. I feel they are > wierd and I dont know what I am doing wrong !!! > > I made a schema with 5 tables. I have a master data table with foreign keys > pointing to other 4 tables. Master data table has around 4 million records. > When I run a select joining it with the baby tables, > > postgres -> returns results in 2.8 seconds > mysql -> takes around 16 seconds !!!! (This is with myisam ... with innodb > it takes 220 seconds)
We said MySQL was faster for simple selects and non-transaction inserts on a limited number of connections. Assuming you rebuilt statistics in MySQL (myisamchk -a), I would presume that PostgreSQLs more mature optimizer has come into play in the above 5 table join test by finding a better (faster) way of executing the query. If you post EXPLAIN ANALYZE output for the queries, we might be able to tell you what they did differently. > I am all for postgres at this point, however just want to know why I am > getting opposite results !!! Both DBs are on the same machine If possible, it would be wise to run a performance test with the expected load you will receive. If you expect to have 10 clients perform operation X at a time, then benchmark that specific scenario. Both PostgreSQL and MySQL will perform differently in a typical real load situation than with a single user, single query situation. > -----Original Message----- > From: Jeffrey Tenny [mailto:[EMAIL PROTECTED] > Sent: Monday, June 06, 2005 11:51 AM > To: pgsql-performance@postgresql.org > Subject: Re: [PERFORM] Need help to decide Mysql vs Postgres > > > Re: your JDBC wishes: Consider IBM Cloudscape (now Apache Derby) too, > which has an apache license. It's all pure java and it's easy to get going. > > > As to MySql vs Postgres: license issues aside, if you have > transactionally complex needs (multi-table updates, etc), PostgreSQL > wins hands down in my experience. There are a bunch of things about > MySQL that just suck for high end SQL needs. (I like my subqueries, > and I absolutely demand transactional integrity). > > There are some pitfalls to pgsql though, especially for existing SQL > code using MAX and some other things which can really be blindsided > (performance-wise) by pgsql if you don't use the workarounds. > > > MySQL is nice for what I call "raw read speed" applications. But that > license is an issue for me, as it is for you apparently. > > > Some cloudscape info: > http://www-306.ibm.com/software/data/cloudscape/ > > Some info on pitfalls of MySQL and PostgreSQL, an interesting contrast: > http://sql-info.de/postgresql/postgres-gotchas.html > http://sql-info.de/mysql/gotchas.html > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match > -- ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
