Re: Benchmark shows very slow bulk delete

Thom Brown <thombrown@gmail.com> wrote:

> Had a quick look at a benchmark someone put together of MySQL vs
> PostgreSQL, and while PostgreSQL is generally faster, I noticed
> the bulk delete was very slow:
> http://www.randombugs.com/linux/mysql-postgresql-benchmarks.html
>
> Is this normal?

It is if you don't have an index on the table which has a foreign
key defined which references the table in which you're doing
deletes.  The author of the benchmark apparently didn't realize that
MySQL automatically adds such an index to the dependent table, while
PostgreSQL leaves it to you to decide whether to add such an index.
For "insert-only" tables, it isn't always worth the cost of
maintaining it.

Also, I see that the database was small enough to be fully cached,
yet the costs weren't adjusted to the recommended values for such an
environment, so PostgreSQL should *really* have beaten MySQL by more
than it did.

-Kevin