Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id 641CB475C8A for ; Fri, 17 May 2002 14:51:45 -0400 (EDT) Received: from smtp014.mail.yahoo.com (smtp014.mail.yahoo.com [216.136.173.58]) by postgresql.org (Postfix) with SMTP id 798BA475B6E for ; Fri, 17 May 2002 14:21:49 -0400 (EDT) Received: from unknown (HELO saturn.janwieck.net) (janwieck@192.233.92.205 with login) by smtp.mail.vip.sc5.yahoo.com with SMTP; 17 May 2002 18:21:47 -0000 Received: (from wieck@localhost) by saturn.janwieck.net (8.11.2/8.11.2) id g4HIJVa07333; Fri, 17 May 2002 14:19:31 -0400 From: Jan Wieck Message-Id: <200205171819.g4HIJVa07333@saturn.janwieck.net> Subject: Re: Rules and Triggers In-Reply-To: from Josh Berkus at "May 16, 2002 08:12:26 am" To: Josh Berkus Date: Fri, 17 May 2002 14:19:31 -0400 (EDT) Cc: Ludwig Lim , pgsql-sql@postgresql.org X-Mailer: ELM [version 2.4ME+ PL68 (25)] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Archive-Number: 200205/186 X-Sequence-Number: 7821 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 #