2013/10/27 Robert James <srobertja...@gmail.com> > On 10/27/13, Thomas Kellerer <spam_ea...@gmx.net> wrote: > > Robert James wrote on 27.10.2013 20:47: > >> I'm using Postgres for data analysis (interactive and batch). I need > >> to focus the analysis on a subset of one table, and, for both > >> performance and simplicity, have a function which loads that subset > >> into another table (DELETE FROM another_table; INSERT INTO > >> another_table SELECT ...). > >> > >> Oddly enough, although the SELECT itself is very quick (< 1 s), the > >> DELETE and INSERT can take over a minute! I can't figure out why. > >> another_table is simple: it has only 7 fields. Two of those fields > >> are indexed, using a simple one field standard index. There are no > >> triggers on it. > >> > >> What is the cause of this behavior? What should I do to make this > >> faster? Is there a recommended work around? > >> > >> (I'm hesitant to drop another_table and recreate it each time, since > >> many views depend on it.) > > > > DELETE can be a quite lengthy thing to do - especially with a large > number > > of rows. > > > > If you use TRUNCATE instead, this will be *much* quicker with the > additional > > benefit, > > that if you INSERT the rows in the same transaction, the INSERT will > require > > much less > > I/O because it's not logged. > > > > Changing DELETE to TRUNCATE and putting it all in a transaction > brought the time down to 40 seconds. But this is still awfully slow, > when the SELECT is under a second. >
How many rows are being inserted? > > Is there another problem here? Perhaps something to do with > triggerring autovacuum? > > Or should I be using a different type of table for work tables? (RAM only > table) > > You could use a TEMP or UNLOGGED table depending on how long you need it to stay around (for these types of tables data won't be forced to disk before returning from the COMMIT).
