Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 9502C9FA432 for ; Tue, 5 Dec 2006 12:55:09 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 36842-05-2 for ; Tue, 5 Dec 2006 12:54:44 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 476CA9FA37A for ; Tue, 5 Dec 2006 12:54:44 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id kB5Gsd60005929; Tue, 5 Dec 2006 11:54:39 -0500 (EST) To: "Heikki Linnakangas" cc: "Sven Geisler" , pgsql-performance@postgresql.org Subject: Re: single transaction vs multiple transactions In-reply-to: <457590A3.2090006@enterprisedb.com> References: <45758FBF.2030404@aeccom.com> <457590A3.2090006@enterprisedb.com> Comments: In-reply-to "Heikki Linnakangas" message dated "Tue, 05 Dec 2006 15:30:43 +0000" Date: Tue, 05 Dec 2006 11:54:39 -0500 Message-ID: <5928.1165337679@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/82 X-Sequence-Number: 22138 "Heikki Linnakangas" 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