Re: Advantages of PostgreSQL over MySQL 5.0

Chis Browne wrote:
> PostgreSQL is likely to be way slower if you submit streams of little
> queries, each an independent transaction...

When I get around to it I plan on debunking this ;). I recently did
extensive internal benchmarking of mysql 5.0 vs. postgresql 8.1 and
it's victories across the board with only a couple of exceptions, and
I have benchmarks to prove it. Im summary:

1. mysql is faster when it's query cache hit ratio is high
2. mysql opens connections much faster than pg, which is why we use pgpool
3. mysql sometimes wins where mvcc delete + insert can be kind of a
pain (*much* rarer than commonly thought)

While 'out of the box' postgresql is slower at select a,b,c from t
where k type queries wrt mysql, the performance advantage is
completely negated if you run those queries via prepared statements.

In postgresql, queries executed over the parameterized/prepared C api
are particularly fast...as much as a 70% speed reduction over vanilla
PQexec. Now, the lower level API and prepared statements are not
available for all applications, but when used they provide extremely
low-latency access.

Also,
1. pg can read off large result sets (50k records +) from the cache
much faster than mysql
2. pg has a generally better query optimizer, altough here and there
mysql scores a win
3. many other advantages you are already quite familiar with, mvcc, etc.

Basically, I am saying that the proverbial bread and butter queries
are not necessarily faster on mysql, just easier to get running fast,
if that makes sense. Now, I'm not trying to bash mysql (I was in
fact, quite impressed with 5.0) generally, but I really think the
claim that it is faster for a broad array of tasks is highly dubious.
pg just requires a little bit more specialized knowledge to get
running up to its level in some cases.

Merlin