pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedsingle transaction vs multiple transactions
7+ messages / 4 participants
[nested] [flat]
* single transaction vs multiple transactions
@ 2006-12-05 15:26 Sven Geisler <sgeisler@aeccom.com>
2006-12-05 15:30 ` Re: single transaction vs multiple transactions Heikki Linnakangas <heikki@enterprisedb.com>
2006-12-05 15:45 ` Re: single transaction vs multiple transactions Jens Schipkowski <jens.schipkowski@apus.co.at>
0 siblings, 2 replies; 7+ messages in thread
From: Sven Geisler @ 2006-12-05 15:26 UTC (permalink / raw)
To: pgsql-performance
Hi,
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=...'.
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
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)'.
Cheers
Sven
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: single transaction vs multiple transactions
2006-12-05 15:26 single transaction vs multiple transactions Sven Geisler <sgeisler@aeccom.com>
@ 2006-12-05 15:30 ` Heikki Linnakangas <heikki@enterprisedb.com>
2006-12-05 15:35 ` Re: single transaction vs multiple transactions Sven Geisler <sgeisler@aeccom.com>
2006-12-05 16:54 ` Re: single transaction vs multiple transactions Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 2 replies; 7+ messages in thread
From: Heikki Linnakangas @ 2006-12-05 15:30 UTC (permalink / raw)
To: Sven Geisler <sgeisler@aeccom.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: single transaction vs multiple transactions
2006-12-05 15:26 single transaction vs multiple transactions Sven Geisler <sgeisler@aeccom.com>
2006-12-05 15:30 ` Re: single transaction vs multiple transactions Heikki Linnakangas <heikki@enterprisedb.com>
@ 2006-12-05 15:35 ` Sven Geisler <sgeisler@aeccom.com>
2006-12-05 15:42 ` Re: single transaction vs multiple transactions Heikki Linnakangas <heikki@enterprisedb.com>
1 sibling, 1 reply; 7+ messages in thread
From: Sven Geisler @ 2006-12-05 15:35 UTC (permalink / raw)
To: Heikki Linnakangas <heikki@enterprisedb.com>; +Cc: pgsql-performance
Hi Heikki
Heikki Linnakangas schrieb:
> 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.
[...]
>>
>> 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.
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?
Sven.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: single transaction vs multiple transactions
2006-12-05 15:26 single transaction vs multiple transactions Sven Geisler <sgeisler@aeccom.com>
2006-12-05 15:30 ` Re: single transaction vs multiple transactions Heikki Linnakangas <heikki@enterprisedb.com>
2006-12-05 15:35 ` Re: single transaction vs multiple transactions Sven Geisler <sgeisler@aeccom.com>
@ 2006-12-05 15:42 ` Heikki Linnakangas <heikki@enterprisedb.com>
2006-12-05 15:58 ` Re: single transaction vs multiple transactions Sven Geisler <sgeisler@aeccom.com>
0 siblings, 1 reply; 7+ messages in thread
From: Heikki Linnakangas @ 2006-12-05 15:42 UTC (permalink / raw)
To: Sven Geisler <sgeisler@aeccom.com>; +Cc: pgsql-performance
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.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: single transaction vs multiple transactions
2006-12-05 15:26 single transaction vs multiple transactions Sven Geisler <sgeisler@aeccom.com>
2006-12-05 15:30 ` Re: single transaction vs multiple transactions Heikki Linnakangas <heikki@enterprisedb.com>
2006-12-05 15:35 ` Re: single transaction vs multiple transactions Sven Geisler <sgeisler@aeccom.com>
2006-12-05 15:42 ` Re: single transaction vs multiple transactions Heikki Linnakangas <heikki@enterprisedb.com>
@ 2006-12-05 15:58 ` Sven Geisler <sgeisler@aeccom.com>
0 siblings, 0 replies; 7+ messages in thread
From: Sven Geisler @ 2006-12-05 15:58 UTC (permalink / raw)
To: Heikki Linnakangas <heikki@enterprisedb.com>; +Cc: pgsql-performance
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.
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: single transaction vs multiple transactions
2006-12-05 15:26 single transaction vs multiple transactions Sven Geisler <sgeisler@aeccom.com>
2006-12-05 15:30 ` Re: single transaction vs multiple transactions Heikki Linnakangas <heikki@enterprisedb.com>
@ 2006-12-05 16:54 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 7+ messages in thread
From: Tom Lane @ 2006-12-05 16:54 UTC (permalink / raw)
To: Heikki Linnakangas <heikki@enterprisedb.com>; +Cc: Sven Geisler <sgeisler@aeccom.com>; pgsql-performance
"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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: single transaction vs multiple transactions
2006-12-05 15:26 single transaction vs multiple transactions Sven Geisler <sgeisler@aeccom.com>
@ 2006-12-05 15:45 ` Jens Schipkowski <jens.schipkowski@apus.co.at>
1 sibling, 0 replies; 7+ messages in thread
From: Jens Schipkowski @ 2006-12-05 15:45 UTC (permalink / raw)
To: Sven Geisler <sgeisler@aeccom.com>; pgsql-performance
Hi,
PostgreSQL offers some proprietary SQL parameters and commands which
easily solves such problems.
If you are sure PostgreSQL is the DB for your app forever ;) , you could
use this parameters and commands.
Here a possible resolution for your problem.
DELETE FROM x USING tmp WHERE x.y=tmp.z;
Please read the extensive documentation of PostgreSQL first, before
posting.
CU,
Jens
On Tue, 05 Dec 2006 16:26:55 +0100, Sven Geisler <sgeisler@aeccom.com>
wrote:
> Hi,
>
> 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=...'.
>
> 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
>
> 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)'.
>
> Cheers
> Sven
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
--
**
Dipl.-Designer Jens Schipkowski
APUS Software GmbH
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2006-12-05 16:54 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-12-05 15:26 single transaction vs multiple transactions Sven Geisler <sgeisler@aeccom.com>
2006-12-05 15:30 ` Heikki Linnakangas <heikki@enterprisedb.com>
2006-12-05 15:35 ` Sven Geisler <sgeisler@aeccom.com>
2006-12-05 15:42 ` Heikki Linnakangas <heikki@enterprisedb.com>
2006-12-05 15:58 ` Sven Geisler <sgeisler@aeccom.com>
2006-12-05 16:54 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-12-05 15:45 ` Jens Schipkowski <jens.schipkowski@apus.co.at>
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