On 07/06/2012 07:38 PM, Daniel Farina wrote:
On Fri, Jul 6, 2012 at 4:29 AM, Craig Ringer <ringerc@xxxxxxxxxxxxx> wrote:
1) Truncate each table. It is too slow, I think, especially for empty
tables.
Really?!? TRUNCATE should be extremely fast, especially on empty tables.
You're aware that you can TRUNCATE many tables in one run, right?
TRUNCATE TABLE a, b, c, d, e, f, g;
I have seen in "trivial" cases -- in terms of data size -- where
TRUNCATE is much slower than a full-table DELETE. The most common use
case for that is rapid setup/teardown of tests, where it can add up
quite quickly and in a very big way. This is probably an artifact the
speed of one's file system to truncate and/or unlink everything.
That makes some sense, actually. DELETEing from a table that has no
foreign keys, triggers, etc while nothing else is accessing the table is
fairly cheap and doesn't take much (any?) cleanup work afterwards. For
tiny deletes I can easily see it being better than forcing the OS to
journal a metadata change or two and a couple of fsync()s for a truncate.
I suspect truncating many tables at once will prove a win over
iteratively DELETEing from many tables at once. I'd benchmark it except
that it's optimizing something I don't care about at all, and the
results would be massively dependent on the file system (ext3, ext4,
xfs) and its journal configuration.
--
Craig Ringer
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
