Received: from web20207.mail.yahoo.com (web20207.mail.yahoo.com [216.136.226.62]) by postgresql.org (8.11.3/8.11.4) with SMTP id fBE85nm44198 for ; Fri, 14 Dec 2001 03:05:49 -0500 (EST) (envelope-from mindterm@yahoo.com) Message-ID: <20011214080549.38940.qmail@web20207.mail.yahoo.com> Received: from [203.85.100.3] by web20207.mail.yahoo.com via HTTP; Fri, 14 Dec 2001 00:05:49 PST Date: Fri, 14 Dec 2001 00:05:49 -0800 (PST) From: MindTerm Subject: Re: performance tuning in large function / transaction To: Stephan Szabo Cc: pgsql-sql@postgresql.org In-Reply-To: <20011213224101.U8121-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Archive-Number: 200112/141 X-Sequence-Number: 5964 Hi all, senario: cursor 1 loop ( e.g. find student id ) cursor 2 loop ( e.g. find courses of this student ) cursor 3 loop ( e.g. update course information ) delele course detail ... or delete course detail ... ( same primary key ) insert course detail ... ( same primary key ) end loop 3 end loop 2 end loop 1 It did 75 delete actions 140 update actions ( delete and insert ). The process time was about 5-6 minutes while oracle was 10 seconds to 20 seconds . postgresql.conf : ================= shared_buffers = 3200 wal_buffers = 80 running on linux 7.1 , 512M ram. M.T. --- Stephan Szabo wrote: > On Thu, 13 Dec 2001, MindTerm wrote: > > > Hi all, > > > > I will try to assign more memory to shared > memory, > > say 50M ~ 100M . > > > > I am runing something like that .. > > > > cursor loop 1 > > cursor loop 2 > > cursor loop 3 > > tmp = primary key > > delete tmp .... > > insert tmp .... > > end loop 3 > > end loop 2 > > end loop 3 > > > > will delete and insert record with same primary > key > > within a transaction reduce the performance ? > > Hmm, that might, is the tmp different for each > combination of 1,2 and 3? I'm not really sure. > You might also want to watch the backend's memory > usage during the call to see how big it gets just > to make sure there's not something bad happening. > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org __________________________________________________ Do You Yahoo!? Check out Yahoo! Shopping and Yahoo! Auctions for all of your unique holiday gifts! Buy at http://shopping.yahoo.com or bid at http://auctions.yahoo.com