Rules and Triggers
От | Ludwig Lim |
---|---|
Тема | Rules and Triggers |
Дата | |
Msg-id | 20020516045940.41862.qmail@web20402.mail.yahoo.com обсуждение исходный текст |
Ответы |
Re: Rules and Triggers
|
Список | pgsql-sql |
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
В списке pgsql-sql по дате отправления: