On Mon, 15 Mar 2010, Tom Lane wrote:
For an example like this one, you have to keep in mind that the toast-table rows for the large bytea value have to be marked deleted, too. Also, since I/O happens in units of pages, the I/O volume to delete a tuple is just as much as the I/O to create it. (The WAL entry for deletion might be smaller, but that's all.) So it is entirely unsurprising that "DELETE FROM foo" is about as expensive as filling the table initially. If deleting a whole table is significant for you performance-wise, you might look into using TRUNCATE instead.
What are the implications of using TRUNCATE on a table that has TOASTed data? Is TOAST all stored in one single table, or is it split up by owner table/column name? Might you still end up with a normal delete operation on the TOAST table when performing a TRUNCATE on the owner table?
Matthew -- sed -e '/^[when][coders]/!d;/^...[discover].$/d;/^..[real].[code]$/!d ' <`locate dict/words` -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
