From: | Ludwig Lim <lud_nowhere_man(at)yahoo(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Rules and Triggers |
Date: | 2002-05-16 04:59:40 |
Message-ID: | 20020516045940.41862.qmail@web20402.mail.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | juerg.rietmann | 2002-05-16 12:38:02 | how to build this list ? |
Previous Message | Josh Berkus | 2002-05-15 15:19:46 | Re: Help on indexes |