update trigger performance

From: Josué Maldonado <josue(at)lamundial(dot)hn>
To: pgsql-general(at)postgresql(dot)org
Subject: update trigger performance
Date: 2004-08-30 21:02:56
Message-ID: 41339600.4090703@lamundial.hn
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello list,

I'm having a performance issue with a trigger before update, a single
row update take this

explain analyze update detpp set dpe_estado='C' where dpe_pk=25541

Index Scan using ix_detpp_pk on detpp (cost=0.00..6.01 rows=2
width=323) (actual time=0.063..0.070 rows=1 loops=1)
Index Cond: (dpe_pk = 25541)
Total runtime: 271.038 ms
(3 rows)

The code for the trigger is this, dunno if something wrong or if is
there another "better" way to get it done

DECLARE
lnRows integer;
totdet numeric(12,4);
oldtot numeric(12,4);
foo numeric(12,4);
hped record;
rprod record;
FACTORCOSTO CONSTANT FLOAT := 0.04;
BEGIN
new.dpe_stamp = 'now';
select into hped ped_estado,ped_tipo, ped_pk,
ped_factorad, ped_mercadoneg, ped_refno from ped_pro
where ped_pk = new.dpe_pedfk for update;
if not found then
raise exception 'EXCEPTION: NO existe encabezado par este detalle
de pedido';
end if;
select into rprod pro_derecho/100 as derecho,
pro_decreto/100 as decreto, pro_costnw, pro_qtyonhand,
pro_costprom, pro_code from product
where pro_pk = new.dpe_productfk for update;
if not found then
raise exception 'EXCEPTION: NO existe articulo par este detalle de
pedido';
end if;
new.dpe_procode = rprod.pro_code;
totdet := 0;
oldtot := 0;

-- Si es pedido local o extranjenro
if old.dpe_pedtype = 1 then
oldtot := old.dpe_qty * old.dpe_costol;
else
oldtot := old.dpe_qty * old.dpe_costod;
end if;

if new.dpe_pedtype = 1 then
totdet := new.dpe_qty * new.dpe_costol;
else
totdet := new.dpe_qty * new.dpe_costod;
new.dpe_costol := hped.ped_mercadoneg +
hped.ped_factorad * (rprod.derecho
+ rprod.decreto + FACTORCOSTO ) ;
new.dpe_costol := round(new.dpe_costol * new.dpe_costod,4) ;
end if;
if old.dpe_estado<>new.dpe_estado then
if new.dpe_estado='F' then
UPDATE ped_pro
SET ped_qfaxed = coalesce(ped_qfaxed,0) + 1,
ped_dfaxed = coalesce(ped_dfaxed,0) + totdet
WHERE ped_pk = new.dpe_pedfk;
elsif
new.dpe_estado='C' then
UPDATE ped_pro
SET ped_itemsconf = coalesce(ped_itemsconf,0) + 1,
ped_mnconf = coalesce(ped_mnconf,0) + totdet
WHERE ped_pk = new.dpe_pedfk;
elsif
new.dpe_estado='U' then
UPDATE ped_pro
SET ped_qtfact = coalesce(ped_qtfact,0) + 1,
ped_mnfact = coalesce(ped_mnfact,0) + totdet
WHERE ped_pk = new.dpe_pedfk;
end if;
end if;
UPDATE ped_pro
SET ped_mntotal = ped_mntotal - oldtot,
ped_itemstotal = ped_itemstotal - 1
WHERE ped_pk = old.dpe_pedfk;

UPDATE ped_pro
SET ped_mntotal = ped_mntotal + totdet,
ped_itemstotal = ped_itemstotal + 1
WHERE ped_pk = new.dpe_pedfk;

update product
set pro_qtypro = pro_qtypro - old.dpe_qty
where pro_pk = old.dpe_productfk;

update product
set pro_costproc = new.dpe_costol,
pro_qtypro = coalesce(pro_qtypro,0) + new.dpe_qty,
pro_ultqtyproc = new.dpe_qty,
pro_costprocd = new.dpe_costod
where pro_pk = new.dpe_productfk;


RETURN new;
END;

Any idea or suggestion is welcome.

--
Sinceramente,
Josué Maldonado.

"Cuando mi marido se retrasa para la cena, se que o tiene una amante o
está tirado, muerto en plena calle. Siempre espero que sea lo de la
calle." Jessica Tandy. Actriz Inglesa.

Browse pgsql-general by date

  From Date Subject
Next Message Tim Penhey 2004-08-30 21:12:12 Re: Single Row Table?
Previous Message Tom Lane 2004-08-30 20:54:46 Re: aggregates with complex type as state and init condition