Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id A22769FBEBE for ; Fri, 27 Apr 2007 10:35:32 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 52716-03 for ; Fri, 27 Apr 2007 10:35:23 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from nz-out-0506.google.com (nz-out-0506.google.com [64.233.162.230]) by postgresql.org (Postfix) with ESMTP id 9DDDA9FBDD1 for ; Fri, 27 Apr 2007 10:35:28 -0300 (ADT) Received: by nz-out-0506.google.com with SMTP id s1so436739nze for ; Fri, 27 Apr 2007 06:35:27 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=V9GPYPkyvMzpznvpYcvSynMV/x9eQyU6N4yu5VVzf5LSiauNDB/PwSYCVAyXkyM0p0G1YdrtoS/6lk69MAl5IgiXWR3SxhDBiRSr2hSzab+EUl2Z8I5j5Toeqx8svF41Q3pm8jkfb0gacp2DrlOB7fR1+LnYV5DY6LnbtmHxMqw= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=WsblBX5k1/LLMoGMAi5ZmF87PKC035oHeueG/Wv5qJ18pYf7jsgcRUf66dw2hAnompg4YpLvqRCT4gWXnVbhujtr5PDBCwAOfFDIXCpcCV2gVsdwb1AhFpfZN+m4TPrHyvTubMhM5b4ZnBaA7C+Lb0eaZ+cy8oZkao6l5zNL378= Received: by 10.115.94.1 with SMTP id w1mr1001469wal.1177680926847; Fri, 27 Apr 2007 06:35:26 -0700 (PDT) Received: by 10.114.47.12 with HTTP; Fri, 27 Apr 2007 06:35:26 -0700 (PDT) Message-ID: <36e682920704270635k436f22bbx2868524d40b468ea@mail.gmail.com> Date: Fri, 27 Apr 2007 09:35:26 -0400 From: "Jonah H. Harris" To: "Martial Elise KIBA" Subject: Re: PostgreSQL8.2.3 Performance Cc: pgsql-admin@postgresql.org In-Reply-To: <20070427113657.M51911@mailer.gov.bf> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <20070427113657.M51911@mailer.gov.bf> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/250 X-Sequence-Number: 25314 Is there any reason you can't do something like this instead? UPDATE produitscommandes SET prixvente =3D p.prixvente FROM produits p WHERE produit =3D p.numero; On 4/27/07, Martial Elise KIBA wrote: > Hi all, > > > I have a database running on POstgreSQL 8.2.3. The plpgsql functions were > running well on my previous release. > > When i migrated to 8.2.3, I noticed some performance degradation, special= ly > whith one of my function which makes an update to a table. > > Here is the code of the function > > CREATE OR REPLACE FUNCTION update1() > RETURNS varchar(50) > AS > $BODY$ > DECLARE > v_cur CURSOR FOR SELECT numero, prixvente FROM produits; > v_prixvente produitscommandes.prixvente%TYPE; > v_produit produits.numero%TYPE; > BEGIN > OPEN v_cur; > LOOP > FETCH v_cur INTO v_produit, v_prixvente; > UPDATE produitscommandes SET prixvente=3Dv_prixvente WHER= E > produit=3Dv_produit; > EXIT WHEN NOT FOUND; -- Sortie de la boucle > END LOOP; > CLOSE v_cur; > RETURN 'mise =E0 jour effectu=E9e'; > END; > $BODY$ > LANGUAGE 'plpgsql' VOLATILE; > > ''######################################"" > > produitscommandes has a primary key on commande and produit > produitscommandes has 2 indexes on commande and produit > produits has a primary key on numero > > When i call the function it takes a lot (it can take 30 minutes for > approximatively 5 000 rows in produitscommandes and 3 000 in produits) > > thanks all for your help. > > PS: tried vaccum and analyse on table produitscommandes > > Martial E. W. KIBA > Ing=E9nieur de Conception en Informatiques > Option G=E9nie-Logiciel > T=E9l: (+226) 70 15 44 93 > Mail: mkiba@delgi.gov.bf / mkiba01@yahoo.fr > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > --=20 Jonah H. Harris, Software Architect | phone: 732.331.1324 EnterpriseDB Corporation | fax: 732.331.1301 33 Wood Ave S, 3rd Floor | jharris@enterprisedb.com Iselin, New Jersey 08830 | http://www.enterprisedb.com/