vacuum, functions, and triggers

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

Browse pgsql-general by date

  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