Speed difference between Drop table and Truncate table in Postgres I currently have a program that inserts into a database by creating temp tables, filling the tables, then merging that data into the main tables. Then dropping the tables and doing it all again.
I'm wondering what the speed difference is if instead of drop and create, if I just truncate.
DROP &amp; CREATE is slightly more expensive, as it actually deletes rows from some system tables (pg_class, pg_attribute, ...) in addition to removing the physical table files - and later has to parse etc. the new CREATE TABLE command, while TRUNCATE only removes the physical files for the table and starts new ones, keeping the catalog entries. But the difference is negligible for simple tables, especially for temp tables. And it gets smaller, yet if you factor in an additional ANALYZE that might be needed after TRUNCATE. Then again, you might need that in any case. See:


Are regular VACUUM ANALYZE still recommended under 9.1?


By "filling the tables" you mean COPY, I suppose? A much more costly difference would be to CREATE or TRUNCATE a plain table in a separate transaction before writing to it, as in this case you accrue the additional (substantial) cost of writing WAL (Write Ahead Log) entries. The manual:


  COPY is fastest when used within the same transaction as an earlier
  CREATE TABLE or TRUNCATE command. In such cases no WAL needs to be
  written, because in case of an error, the files containing the newly
  loaded data will be removed anyway. However, this consideration only
  applies when wal_level is minimal for non-partitioned tables as all
  commands must write WAL otherwise.


Bold emphasis mine. (minimal is the default for wal_level.) Does not affect temp tables, which do not write WAL at all.

You might be interested in CREATE TEMP TABLE ...ON COMMIT DELETE ROWS. The manual:


  All rows in the temporary table will be deleted at the end of each
  transaction block. Essentially, an automatic TRUNCATE is done at each
  commit. When used on a partitioned table, this is not cascaded to its partitions.


Should be fastest. But the difference still typically small.