thread-index: AccYglc5h4iEy6ttQIW/WMkc29qxtg==
Message-ID: <457590A3.2090006@enterprisedb.com>
Content-Class: urn:content-classes:message
Importance: normal
Date: Tue, 05 Dec 2006 15:30:43 +0000
From: "Heikki Linnakangas" <heikki@enterprisedb.com>
User-Agent: Thunderbird 1.5.0.7 (X11/20060927)
MIME-Version: 1.0
To: "Sven Geisler" <sgeisler@aeccom.com>
Cc: <pgsql-performance@postgresql.org>
Subject: Re: single transaction vs multiple transactions
References: <45758FBF.2030404@aeccom.com>
In-Reply-To: <45758FBF.2030404@aeccom.com>
Content-Type: text/plain;
	format=flowed;
	charset="ISO-8859-15"
Content-Transfer-Encoding: 7bit

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