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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
