From: Ludwig Lim <lud_nowhere_man@yahoo.com>
To: pgsql-sql@postgresql.org
Subject: Rules and Triggers
Date: Wed, 15 May 2002 21:59:40 -0700 (PDT)
Message-ID: <20020516045940.41862.qmail@web20402.mail.yahoo.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: lud_nowhere_man@yahoo.com
Subject: Re: Rules and Triggers
In-Reply-To: <20020516045940.41862.qmail@web20402.mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox