Thanks a lot for the hint Tom! I've replaced deletes with TRUNCATE and it gave a performance of 50.950 sec which is twice as fast as the drop temp table method, with the added benefit of not having to raise the max_locks_per_transaction.
I also think I can't see the performance decrease pattern anymore, or the operation is completing before that happens, will generate more data and try again.
Regards
Seref
On Wed, May 21, 2014 at 4:52 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Seref Arikan <serefarikan@kurumsalteknoloji.com> writes: > What may be building up here? I suspect deleting all rows from the temp > tables is not really deleting them since this is all happening in a > transaction, but it is my uneducated guess only.
I suspect you suspect correctly. Autovacuum does not touch temp tables, so it won't help you deal with deleted tuples. Given the usage pattern you're describing, I think that using a TRUNCATE rather than delete-all-the-rows would help ... but if you're already doing that, we need more info.