Thread: Rule vs Trigger
Suppose I want to update a statistics table S after an insertion into an activity table A. In order to update S automatically, I can either create a rule on A or create an after trigger on A. What's the performance implication on using rule vs trigger? Thanks in advance. Alfred
--- Alfred Zhao <rongkai.zhao@gmail.com> wrote: > Suppose I want to update a statistics table S after an insertion into an > activity table A. In order to update S automatically, I can either create a > rule on A or create an after trigger on A. What's the performance > implication on using rule vs trigger? Thanks in advance. My understanding is that Triggers offer better performance than rules do. However, one important advantage of triggers over rules is that rules are not able to correctly handle DML statements that affects more than one record in a single statement. Regards, Richard Broersma Jr.
Richard Broersma Jr wrote: > --- Alfred Zhao <rongkai.zhao@gmail.com> wrote: >> Suppose I want to update a statistics table S after an >> insertion into an activity table A. In order to update S >> automatically, I can either create a rule on A or create >> an after trigger on A. What's the performance implication >> on using rule vs trigger? Thanks in advance. > > My understanding is that Triggers offer better performance > than rules do. A trigger FOR EACH STATEMENT will execute the trigger function for each row affacted by the statement. A rule would only execute one additional statement. So if you can do it with a rule conveniently, the rule will probably be faster. Yours, Laurenz Albe
"Albe Laurenz" <all@adv.magwien.gv.at> writes:
> Richard Broersma Jr wrote:
>> My understanding is that Triggers offer better performance
>> than rules do.
> A trigger FOR EACH STATEMENT will execute the trigger function
> for each row affacted by the statement.
Huh? That would be true for a FOR EACH ROW trigger, but a STATEMENT
trigger fires once per statement.
> A rule would only
> execute one additional statement. So if you can do it with a rule
> conveniently, the rule will probably be faster.
I find this unlikely. The overhead involved in setting up a rule
is probably larger than that involved in calling a trigger.
The real question is whether you need access to the modified data
or not --- a statement-level trigger doesn't currently get that.
regards, tom lane
>> A trigger FOR EACH STATEMENT will execute the trigger function >> for each row affacted by the statement. > > Huh? That would be true for a FOR EACH ROW trigger, but a STATEMENT > trigger fires once per statement. Argh. I intended to write FOR EACH ROW. Thanks for the correction. >> A rule would only >> execute one additional statement. So if you can do it with a rule >> conveniently, the rule will probably be faster. > > I find this unlikely. The overhead involved in setting up a rule > is probably larger than that involved in calling a trigger. > The real question is whether you need access to the modified data > or not --- a statement-level trigger doesn't currently get that. Yes, I meant to write about row level triggers, maybe what I wrote makes more sense then... What I said was inspired by chapter 35.6. of the documentation. Yours, Laurenz Albe
Tom Lane wrote: > "Albe Laurenz" <all@adv.magwien.gv.at> writes: >> Richard Broersma Jr wrote: >> A rule would only >> execute one additional statement. So if you can do it with a rule >> conveniently, the rule will probably be faster. > > I find this unlikely. The overhead involved in setting up a rule > is probably larger than that involved in calling a trigger. > The real question is whether you need access to the modified data > or not --- a statement-level trigger doesn't currently get that. Not that Tom ever needs any backing up, but when we started testing partitioning, we first tested with rules and then tested with triggers. Triggers are quite a bit faster than rules. Joshua D. Drake > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org/ > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/