pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgreSQL8.2.3 Performance
2+ messages / 2 participants
[nested] [flat]
* PostgreSQL8.2.3 Performance
@ 2007-04-27 11:50 Martial Elise KIBA <mkiba@delgi.gov.bf>
2007-04-27 13:35 ` Re: PostgreSQL8.2.3 Performance Jonah H. Harris <jonah.harris@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Martial Elise KIBA @ 2007-04-27 11:50 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: PostgreSQL8.2.3 Performance
2007-04-27 11:50 PostgreSQL8.2.3 Performance Martial Elise KIBA <mkiba@delgi.gov.bf>
@ 2007-04-27 13:35 ` Jonah H. Harris <jonah.harris@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Jonah H. Harris @ 2007-04-27 13:35 UTC (permalink / raw)
To: Martial Elise KIBA <mkiba@delgi.gov.bf>; +Cc: pgsql-admin
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/
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2007-04-27 13:35 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-04-27 11:50 PostgreSQL8.2.3 Performance Martial Elise KIBA <mkiba@delgi.gov.bf>
2007-04-27 13:35 ` Jonah H. Harris <jonah.harris@gmail.com>
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