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 по дате отправления: