From: Jan Wieck <janwieck@yahoo.com>
To: Josh Berkus <josh@agliodbs.com>
Cc: Ludwig Lim <lud_nowhere_man@yahoo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Rules and Triggers
Date: Fri, 17 May 2002 14:19:31 -0400 (EDT)
Message-ID: <200205171819.g4HIJVa07333@saturn.janwieck.net> (raw)
In-Reply-To: <web-1461719@davinci.ethosmedia.com>
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 all the 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 #
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: janwieck@yahoo.com, josh@agliodbs.com, lud_nowhere_man@yahoo.com
Subject: Re: Rules and Triggers
In-Reply-To: <200205171819.g4HIJVa07333@saturn.janwieck.net>
* 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