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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
