vacuum, functions, and triggers

Поиск
Список
Период
Сортировка
От Jean-Christian Imbeault
Тема vacuum, functions, and triggers
Дата
Msg-id 3DDDA99B.3010102@mega-bucks.co.jp
обсуждение исходный текст
Список 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


В списке pgsql-general по дате отправления:

Предыдущее
От: Medi Montaseri
Дата:
Сообщение: Re: Request assistance connecting with Pg::connectdb
Следующее
От: Brian Minton
Дата:
Сообщение: Re: Dumb Newbie Question - Mandrake 9.0 / PGSQL 7.2