X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 4A4EC11F7A7C for ; Tue, 18 Apr 2006 01:55:13 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: 2.404 X-Spam-Level: ** X-Spam-Status: No, score=2.404 required=5 tests=[AWL=0.072, FROM_LOCAL_NOVOWEL=2.331, HTML_MESSAGE=0.001] Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id xwrDI+hUuasQ for ; Tue, 18 Apr 2006 01:55:08 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nz-out-0102.google.com (nz-out-0102.google.com [64.233.162.201]) by postgresql.org (Postfix) with ESMTP id 11EED11F66FF for ; Tue, 18 Apr 2006 01:55:08 -0300 (ADT) Received: by nz-out-0102.google.com with SMTP id m7so646837nzf for ; Mon, 17 Apr 2006 21:55:08 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=GfzVZZAQTJabbmhLp+YmL7q9ZTZKir2xbzamFy1jJhVRLO9zXKtuDMcYO2s0xafzS2bHV2RNK1QtCkHuB6Lx2lhmkN3WKgG0IlXeaBxLe6KsqB2XexqjZ2YcTuykMyGTOpEHYj1ZD0B8a7wOr852GcYcqHYuyctC6/xNGRAjOq0= Received: by 10.36.80.5 with SMTP id d5mr2944367nzb; Mon, 17 Apr 2006 21:55:08 -0700 (PDT) Received: by 10.36.158.13 with HTTP; Mon, 17 Apr 2006 21:55:08 -0700 (PDT) Message-ID: Date: Tue, 18 Apr 2006 08:55:08 +0400 From: Luckys To: "Sriram Dandapani" Subject: Re: slow cursor Cc: "Pgsql-Performance (E-mail)" In-Reply-To: <6992E470F12A444BB787B5C937B9D4DF0406A546@ca-mail1.cis.local> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_481_2441224.1145336108522" References: <6992E470F12A444BB787B5C937B9D4DF0406A546@ca-mail1.cis.local> X-Archive-Number: 200604/363 X-Sequence-Number: 18447 ------=_Part_481_2441224.1145336108522 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline This is one thing that I always try to avoid, a single INSERT INTO...SELECT ...FROM or single UPDATE is always faster compared to looping the same within a cursor, unless its inevitable. regards, Luckys. On 4/17/06, Sriram Dandapani wrote: > > Hi > > > > I have a cursor that fetches 150K rows and updates or inserts a table wit= h > 150K rows. > > > > It takes several minutes for the process to complete (about 15 minutes). > The select by itself (without cursor) gets all rows in 15 seconds. > > > > Is there a way to optimize the cursor to fetch all records and speed up > the process. I still need to do the record by record processing > ------=_Part_481_2441224.1145336108522 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline

This is one thing that I always try to avoid, a single INSERT INTO...S= ELECT ...FROM or single UPDATE is always faster compared to looping the sam= e within a cursor, unless its inevitable.

regards,

Luckys.

On 4/17/06, = Sriram Dandapani <sdan= dapani@counterpane.com> wrote:

Hi

I have a cursor that fetches 150K rows and updates or inserts a= table with 150K rows.

It takes several minutes for the process to complete (about 15 = minutes). The select by itself (without cursor) gets all rows in 15 seconds= .

Is there a way to optimize the cursor to fetch all records and = speed up the process. I still need to do the record by record processing

------=_Part_481_2441224.1145336108522--