PostgreSQL v MySQL 5.1

[Steve Sims]

On 22 Dec 2005, at 00:37, Aaron Crane wrote:
> <snip>
> As for speed, I've seen little evidence that either MySQL+InnoDB or
> PostgreSQL is significantly faster than the other.  PostgreSQL 8.1  
> seems
> much faster on some complex queries than MySQL 4.0 with MyISAM, but I
> haven't done any comparisons against newer MySQL releases.

We've been running our web site on a MySQL 4.0 database with MyISAM  
tables, and are currently in the process of migrating to PostgreSQL  
8.1 purely on the basis that it's faster.

We have two queries that our site uses that, under MySQL 4.0, take  
over 20 seconds to execute on a dual 2GHz G5 XServe.  Some  
experimentation showed that under MySQL 5.0 these same queries would  
execute in about 6 seconds.  PostgreSQL on a single 1.25GHz G4  
machine would execute the same query in just 4 seconds.

This is not all that complex a query - it only involves 4 tables.

Where PostgreSQL really shines above MySQL is when multiple copies of  
this query are being handled at the same time.  If there's 3 of these  
queries running concurrently then Postgres will deliver its results  
in about 12 seconds, i.e. 3 times the amount of time.  Three of these  
queries under MySQL however results in them taking about 3 minutes to  
complete.  The performance of MySQL just continues to degrade if you  
add on more queries - run about 8 of them together and you'll be  
waiting over an hour for the results.  PostgreSQL just scales up as  
you would expect - 8 queries will complete in 32 seconds.

Steve