Postgres Pro
Downloads
Home   >   mailing lists

Re: Function performance drops during execution of loop - Mailing list pgsql-general

From Seref Arikan
Subject Re: Function performance drops during execution of loop
Date
Msg-id CA+4ThdrFDjXpKmMV5+Gp0pr_rE16y_fE-T31RbwX-=GvUpBw=g@mail.gmail.com
Whole thread Raw
In response to Re: Function performance drops during execution of loop  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-general
Tree view
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@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.

                        regards, tom lane

pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: Function performance drops during execution of loop
Next
From: Seref Arikan
Date:
Subject: Re: Function performance drops during execution of loop