Re: Benchmark shows very slow bulk delete

On Wednesday 27 January 2010 15:49:06 Matthew Wakeling wrote:
> On Wed, 27 Jan 2010, Thom Brown 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?
>
> On the contrary, TRUNCATE TABLE is really rather fast.
>
> Seriously, the Postgres developers, when designing the system, decided on
> a database layout that was optimised for the most common cases. Bulk
> deletion of data is not really that common an operation, unless you are
> deleting whole categories of data, where setting up partitioning and
> deleting whole partitions would be sensible.
>
> Other complications are that the server has to maintain concurrent
> integrity - that is, another transaction must be able to see either none
> of the changes or all of them. As a consequence of this, Postgres needs to
> do a sequential scan through the table and mark the rows for deletion in
> the transaction, before flipping the transaction committed status and
> cleaning up afterwards.
>
> I'd be interested in how mysql manages to delete a whole load of rows in
> 0.02 seconds. How many rows is that?
Afair mysql detects that case and converts it into some truncate equivalent.

Andres