Обсуждение: Rules and Triggers

Hi:
 Are there cases when triggers are faster than rules?
It appears that on the database that I used; rules
used sequential scan while triggers used index scan.   I had a table "sc_redeem". There is a rule which I
place on that table. The body of rule is "  CREATE RULE rule_update_redeem as  ON insert TO sc_redeem  DO     UPDATE
sc_customer_card    SET   redeemed = redeemed + new.points,           balance  = balance - new.points     WHERE
customer_id= new.customer_id AND           card_type_cd = new.card_type_cd;"
 

The index of sc_customer_card is on the field
"customer_id". I noticed when I use the SQL statement. EXPLAIN INSERT INTO sc_redeem(....) VALUES (....);
It reported that it performed a sequential scan on the
table sc_customer_card (around 100,000+ records w/
unique customer id for each record).
 I tried converting the rule to function & trigger
and it reported that it used an Index scan given the
same INSERT statement.
  I was wondering why the rule used sequentail scan
and the equivalent function trigger used index scan.

Thank you,
ludwig lim 
 

__________________________________________________
Do You Yahoo!?
LAUNCH - Your Yahoo! Music Experience
http://launch.yahoo.com

Ludwig,

>   I tried converting the rule to function & trigger
> and it reported that it used an Index scan given the
> same INSERT statement.
> 
>    I was wondering why the rule used sequentail scan
> and the equivalent function trigger used index scan.

This is one of the documented differences between rules and triggers
(at least, I've seen it in the documentation *somewhere*).  I can't
tell you "why", though ... hopefully Tom or Bruce will speak to that.

For myself, I use triggers for most things, and rules just for
updatable views.  Aside for index efficiency, rules are a Postgres-only
thing, whereas several RDBMS's support triggers.   Gives me marginally
better portability.

-Josh Berkus

Josh Berkus wrote:
> Ludwig,
>
> >   I tried converting the rule to function & trigger
> > and it reported that it used an Index scan given the
> > same INSERT statement.
> >
> >    I was wondering why the rule used sequentail scan
> > and the equivalent function trigger used index scan.
>
> This is one of the documented differences between rules and triggers
> (at least, I've seen it in the documentation *somewhere*).  I can't
> tell you "why", though ... hopefully Tom or Bruce will speak to that.
   Neither  is  *that*  one of the differences between rules and   triggers, nor *should*  something  like  that  be
documented  instead of fixed.
 
   There  might  be  a  difference in the selectivity when using   OLD/NEW  Var  nodes  vs.  $n  parameters,  and
therefore  a   different  estimate  by  the  optimizer.  But in general rule   generated queries are using indexes.
 
   Of course, that requires  up  to  date  statistics.  Are  the   tables ANALYZE'd properly?

> For myself, I use triggers for most things, and rules just for
> updatable views.  Aside for index efficiency, rules are a Postgres-only
> thing, whereas several RDBMS's support triggers.   Gives me marginally
> better portability.
   As long as your queries only affect very few rows, you'll not   notice much of  a  performance  difference.  Try
updating a   couple  thousand  rows with one UPDATE. A trigger will fire a   couple thousand times, where a rule does
allthe work in  one   additional query.
 


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #