Stanislaw Pankevich wrote: >>> ==== PostgreSQL db, 30 tables with number of rows < 100 (not huge) - the fastest way to clean each >>> non-empty table and reset unique identifier column of empty ones ==== >>> >>> I wonder, what is the fastest way to accomplish this kind of task in PostgreSQL. I am interested in >>> the fastest solutions ever possible. >>> I need the fastest cleaning strategy for such case working on PostgreSQL both 8 and 9. >>> >>> I see the following approaches: >>> >>> 1) Truncate each table. It is too slow, I think, especially for empty tables. >> Did you actually try it? That's the king's way to performance questions! >> Truncating a single table is done in a matter of microseconds, particularly >> if it is not big. >> Do you have tens of thousands of tables? > Actually, 10-100 tables. >> You could of course run a SELECT 1 FROM table LIMIT 1, but again I don't >> think that this will be considerably faster than just truncating the table. > > Exactly this query is much faster, believe me. You can see my latest > results on https://github.com/stanislaw/truncate-vs-count. Ok, I believe you. My quick tests showed that a sible truncate (including transaction and client-server roundtrip via UNIX sockets takes some 10 to 30 milliseconds. Multiply that with 100, and you end up with just a few seconds at most. Or what did you measure? I guess you run that deletion very often so that it is painful. Still I think that the biggest performance gain is to be had by using PostgreSQL's features (truncate several tables in one statement, ...). Try to bend your Ruby framework! Yours, Laurenz Albe -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
