From: Sven Geisler <sgeisler@aeccom.com>
To: Heikki Linnakangas <heikki@enterprisedb.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: single transaction vs multiple transactions
Date: Tue, 05 Dec 2006 16:58:30 +0100
Message-ID: <45759726.9060004@aeccom.com> (raw)
In-Reply-To: <45759381.40909@enterprisedb.com>
References: <45758FBF.2030404@aeccom.com>
<457590A3.2090006@enterprisedb.com>
<457591B7.80408@aeccom.com>
<45759381.40909@enterprisedb.com>
Hi,
Many thanks for the fast response.
I will use temporary table with copy.
Other issue I have is the connection between app and db.
I guess, the approach with the copy will also hold down the network I/O
between app and db. Keeping in mind that I produce 10,000+ statements.
Thx
Sven
Heikki Linnakangas schrieb:
> 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.
>
>
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: sgeisler@aeccom.com, heikki@enterprisedb.com
Subject: Re: single transaction vs multiple transactions
In-Reply-To: <45759726.9060004@aeccom.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