On Fri, Jul 6, 2012 at 4:29 AM, Craig Ringer <ringerc@ringerc.id.au> 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.
I haven't tried a multi-truncate though. Still, I don't know a
mechanism besides slow file system truncation time that would explain
why DELETE would be significantly faster.
--
fdr