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:30:43 +0000
Message-ID: <457590A3.2090006@enterprisedb.com> (raw)
In-Reply-To: <45758FBF.2030404@aeccom.com>
References: <45758FBF.2030404@aeccom.com>
Sven Geisler wrote:
> I have to refactoring a 'DELETE FROM x WHERE y IN (...)' because IN got
> to much parameters. => 'stack depth limit exceeded'
> I don't want to increase just the parameter for max_stack_depth. It is
> better to refactoring because the number of arguments to IN may increase
> in the future.
>
> My approach is to do multiple 'DELETE FROM x WHERE y=...'.
You could also do something in between, issuing the deletes in batches
of say 100 deletes each. But using a temporary table is much better.
> My question is now, what is better for PostgreSQL from a performance
> perspective?
> 1. all multiple deletes in one transaction
> 2. each delete in its own transaction
All in one transaction is definitely faster.
> The number of arguments is around 10,000.
>
> BTW: The arguments are generate in the application tier. I would have to
> create a temporary table which I can use in 'DELETE FROM x WHERE y IN
> (SELECT z FROM tmp)'.
I think that's exactly what you should do.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
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: <457590A3.2090006@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