Hi Tom,
Thanks for the feedback. I've moved the temp tables to internal function, increased max_locks_per_transaction and dropped the tables instead of deleting them.
The performance drop is till there, but it is much, much less then the previous case. Previously the whole execution took 04:36:14 and when I use the drop table approach it takes 00:01:44
less then 2 minutes compared to 4 and a half hours...
I will try TRUNCATE as well and see what happens then. Oh, btw, I realised I've used perform in the version of the code I've included in my initial message, it was simply to eliminate the main temp table in the outer query as a suspect.
Regards
Seref
On Wed, May 21, 2014 at 4:52 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
Seref Arikan <serefarikan@xxxxxxxxxxxxxxxxxxxxx> writes:I suspect you suspect correctly. Autovacuum does not touch temp tables,
> 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.
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.
regards, tom lane
