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
