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+4Thdq+TD-r4wYwZudsTOHDQU71QRhwiYQR0AdMkDhkErAqdg@mail.gmail.com
Whole thread Raw
In response to Re: Function performance drops during execution of loop  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Function performance drops during execution of loop
List pgsql-general
Tree view
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.

                        regards, tom lane

pgsql-general by date:

Previous
From: Seref Arikan
Date:
Subject: Re: Function performance drops during execution of loop
Next
From: Khangelani Gama
Date:
Subject: Re: Need help on triggers - postgres 9.1.2