On Fri, Jul 6, 2012 at 4:38 PM, Craig Ringer <ringerc@xxxxxxxxxxxxx> wrote: > 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. Question: Is there a possibility in PostgreSQL to do DELETE on many tables massively, like TRUNCATE allows. Like DELETE table1, table2, ...? -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
