From: Tom Lane <tgl@sss.pgh.pa.us>
To: Heikki Linnakangas <heikki@enterprisedb.com>
Cc: Sven Geisler <sgeisler@aeccom.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: single transaction vs multiple transactions
Date: Tue, 05 Dec 2006 11:54:39 -0500
Message-ID: <5928.1165337679@sss.pgh.pa.us> (raw)
In-Reply-To: <457590A3.2090006@enterprisedb.com>
References: <45758FBF.2030404@aeccom.com>
<457590A3.2090006@enterprisedb.com>
"Heikki Linnakangas" <heikki@enterprisedb.com> writes:
> Sven Geisler wrote:
>> I have to refactoring a 'DELETE FROM x WHERE y IN (...)' because IN got
>> to much parameters. => 'stack depth limit exceeded'
>> 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.
Also, if you're planning to update to 8.2 soon, the tradeoffs will
change completely. 8.2 should avoid the stack depth problem, and you
can get something closely approximating the plan you'd get for a join
against a temp table using VALUES:
regression=# explain select * from tenk1 where unique2 in (1,2,3,4,6,8);
QUERY PLAN
-----------------------------------------------------------------------------
Bitmap Heap Scan on tenk1 (cost=24.01..45.79 rows=6 width=244)
Recheck Cond: (unique2 = ANY ('{1,2,3,4,6,8}'::integer[]))
-> Bitmap Index Scan on tenk1_unique2 (cost=0.00..24.01 rows=6 width=0)
Index Cond: (unique2 = ANY ('{1,2,3,4,6,8}'::integer[]))
(4 rows)
regression=# explain select * from tenk1 where unique2 in (values(1),(2),(3),(4),(6),(8));
QUERY PLAN
----------------------------------------------------------------------------------
Nested Loop (cost=4.10..48.34 rows=6 width=244)
-> HashAggregate (cost=0.09..0.15 rows=6 width=4)
-> Values Scan on "*VALUES*" (cost=0.00..0.08 rows=6 width=4)
-> Bitmap Heap Scan on tenk1 (cost=4.01..8.02 rows=1 width=244)
Recheck Cond: (tenk1.unique2 = "*VALUES*".column1)
-> Bitmap Index Scan on tenk1_unique2 (cost=0.00..4.01 rows=1 width=0)
Index Cond: (tenk1.unique2 = "*VALUES*".column1)
(7 rows)
regards, tom lane
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: tgl@sss.pgh.pa.us, heikki@enterprisedb.com, sgeisler@aeccom.com
Subject: Re: single transaction vs multiple transactions
In-Reply-To: <5928.1165337679@sss.pgh.pa.us>
* 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