From: | Matthew Wakeling <matthew(at)flymine(dot)org> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Greg Smith <greg(at)2ndquadrant(dot)com>, VJK <vjkmail(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Deleting bytea, autovacuum, and 8.2/8.4 differences |
Date: | 2010-03-15 15:18:07 |
Message-ID: | alpine.DEB.2.00.1003151516070.1887@aragorn.flymine.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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`
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2010-03-15 15:26:22 | Re: Deleting bytea, autovacuum, and 8.2/8.4 differences |
Previous Message | Tom Lane | 2010-03-15 14:53:22 | Re: Deleting bytea, autovacuum, and 8.2/8.4 differences |