pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jonah H. Harris <jonah.harris@gmail.com>
To: Martial Elise KIBA <mkiba@delgi.gov.bf>
Cc: pgsql-admin@postgresql.org
Subject: Re: PostgreSQL8.2.3 Performance
Date: Fri, 27 Apr 2007 09:35:26 -0400
Message-ID: <36e682920704270635k436f22bbx2868524d40b468ea@mail.gmail.com> (raw)
In-Reply-To: <20070427113657.M51911@mailer.gov.bf>
References: <20070427113657.M51911@mailer.gov.bf>

Is there any reason you can't do something like this instead?

UPDATE produitscommandes
   SET prixvente = p.prixvente
  FROM produits p
 WHERE produit = p.numero;

On 4/27/07, Martial Elise KIBA <mkiba@delgi.gov.bf> 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, 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
>
>
> ---------------------------(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
>


-- 
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/



view thread (2+ messages)

Message-ID: <36e682920704270635k436f22bbx2868524d40b468ea@mail.gmail.com>
Permalink:  ../36e682920704270635k436f22bbx2868524d40b468ea@mail.gmail.com/
Also on:    postgresql.org/message-id/36e682920704270635k436f22bbx2868524d40b468ea@mail.gmail.com

 · 

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: jonah.harris@gmail.com, mkiba@delgi.gov.bf
  Subject: Re: PostgreSQL8.2.3 Performance
  In-Reply-To: <36e682920704270635k436f22bbx2868524d40b468ea@mail.gmail.com>

* 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