Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 4ECAE9FBB02 for ; Fri, 27 Apr 2007 07:44:38 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 95367-04 for ; Fri, 27 Apr 2007 07:44:31 -0300 (ADT) X-Greylist: delayed 00:18:54.676757 by SQLgrey-1.7.4 Received: from mailer.gov.bf (unknown [212.52.129.92]) by postgresql.org (Postfix) with ESMTP id 985009FBA24 for ; Fri, 27 Apr 2007 07:44:34 -0300 (ADT) Received: from mailer.gov.bf (mailer.gov.bf [212.52.129.92]) by mailer.gov.bf (Postfix) with ESMTP id 82F1D1E710B for ; Fri, 27 Apr 2007 07:50:57 -0400 (EDT) From: "Martial Elise KIBA" To: pgsql-admin@postgresql.org Subject: PostgreSQL8.2.3 Performance Date: Fri, 27 Apr 2007 11:50:57 +0000 Message-Id: <20070427113657.M51911@mailer.gov.bf> X-Mailer: Open WebMail 2.01 20030611 X-OriginatingIP: 212.52.128.7 (mkiba) MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 X-MailScanner-Information: Please contact the ISP for more information X-MailScanner: Found to be clean X-MailScanner-From: mkiba@delgi.gov.bf X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=2.235 tagged_above=0 required=5 tests=BAYES_50=0.001, RCVD_IN_WHOIS_INVALID=2.234 X-Spam-Level: ** X-Archive-Number: 200704/249 X-Sequence-Number: 25313 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, specially 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=v_prixvente WHERE produit=v_produit; EXIT WHEN NOT FOUND; -- Sortie de la boucle END LOOP; CLOSE v_cur; RETURN 'mise à jour effectuée'; 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énieur de Conception en Informatiques Option Génie-Logiciel Tél: (+226) 70 15 44 93 Mail: mkiba@delgi.gov.bf / mkiba01@yahoo.fr