Search Postgresql Archives

Re: Function performance drops during execution of loop

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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:
> 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.

                        regards, tom lane


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux