pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Martial Elise KIBA <mkiba@delgi.gov.bf>
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> (raw)

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




view thread (2+ messages)  latest in thread

Message-ID: <20070427113657.M51911@mailer.gov.bf>
Permalink:  ../20070427113657.M51911@mailer.gov.bf/
Also on:    postgresql.org/message-id/20070427113657.M51911@mailer.gov.bf

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-admin@postgresql.org
  Cc: mkiba@delgi.gov.bf
  Subject: Re: PostgreSQL8.2.3 Performance
  In-Reply-To: <20070427113657.M51911@mailer.gov.bf>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox