pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Heikki Linnakangas <heikki@enterprisedb.com>
To: Sven Geisler <sgeisler@aeccom.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: single transaction vs multiple transactions
Date: Tue, 05 Dec 2006 15:42:57 +0000
Message-ID: <45759381.40909@enterprisedb.com> (raw)
In-Reply-To: <457591B7.80408@aeccom.com>
References: <45758FBF.2030404@aeccom.com>
	<457590A3.2090006@enterprisedb.com>
	<457591B7.80408@aeccom.com>

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



view thread (7+ messages)  latest in thread

Message-ID: <45759381.40909@enterprisedb.com>
Permalink:  ../45759381.40909@enterprisedb.com/
Also on:    postgresql.org/message-id/45759381.40909@enterprisedb.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: heikki@enterprisedb.com, sgeisler@aeccom.com
  Subject: Re: single transaction vs multiple transactions
  In-Reply-To: <45759381.40909@enterprisedb.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox