From: | Jean-Christian Imbeault <jc(at)mega-bucks(dot)co(dot)jp> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | vacuum, functions, and triggers |
Date: | 2002-11-22 03:50:51 |
Message-ID: | 3DDDA99B.3010102@mega-bucks.co.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
First off, I'm new to writing functions and triggers so please keep that
in mind. Secondly and criticism of my functions/triggers is highly
appreciated.
The question I have is that I noticed a 4x increase in the execution
speed of an update statement that triggers a trigger after doing a
vacuum full analyse ... However this is a rather small test DB with few
inserts/updates, and it was vacuumed last night, so I doubt more than a
few hundred updates/inserts/delete happened since the last vacuum.
How could vacuuming have such a dramatic effect?
Here is the output showing the difference in speed:
$ time psql JC -c "update invoices set cancelled=true"
UPDATE 10
real 0m0.482s <-- slow!
$ psql JC -c "vacuum full analyze"
VACUUM
$ time psql JC -c "update invoices set cancelled=true"
UPDATE 10
real 0m0.110s <-- fast!
For completeness sake here is some more info and the functions:
1- when cancelled=true the real number of rows affected is 10 + 80 (from
the invoice_li table) compared to 10 when cancelled=false
The triggers and functions:
-- FUNCTION that will update the total price of an invoice if the new
price of an invoice_li is different from the old price
create or replace function update_invoice_price() returns opaque as '
begin
if
new.price = old.price then return new;
end if;
update invoices set total_price=(select sum(price) from invoice_li
where invoice_id=new.invoice_id) where id=new.invoice_id;
return null;
end;
' language 'plpgsql' with (iscachable);
-- FUNCTION that will cancell all the invoice_li of an invoice if the
invoice is cancelled
create or replace function cancell_all_li() returns opaque as '
begin
if new.cancelled = true then
update invoice_li set cancelled=true where invoice_id=new.id;
end if;
return new;
end;
' language 'plpgsql' with (iscachable);
create trigger update_invoice_li_price after update
on invoice_li for each row
execute procedure update_invoice_price();
create trigger insert_invoices after update
on invoices for each row
execute procedure cancell_all_li();
Thanks!
Jc
From | Date | Subject | |
---|---|---|---|
Next Message | Brian Minton | 2002-11-22 04:41:04 | Re: Dumb Newbie Question - Mandrake 9.0 / PGSQL 7.2 |
Previous Message | Egyud Csaba | 2002-11-22 02:25:39 | Re: Welcom & a problem |