Re: single transaction vs multiple transactions

Sven Geisler wrote:
> I have to insert my arguments to a temporary table first, because the
> arguments are only known in the application tier.
> Is a multiple insert to a temporary table and a final 'DELETE FROM x
> WHERE y IN (SELECT z FROM tmp)' faster than multiple deletes?

If the number of records is high, it most likely is faster. You should
try it with your data to be sure, but in general doing all the deletes
in one batch is faster when the number of records is high because it
allows using efficient merge joins or sequential scans.

Populating the temporary table with no indexes should be quite
inexpensive if you make sure you don't do it one record at a time. Use
the COPY command or batched inserts instead.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com