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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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.

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq