Re: using EXPLAIN in postgresql RULES?

On Fri, Jan 11, 2002 at 06:49:26PM -0500, Tom Lane wrote:
> will trillich <will(at)serensoft(dot)com> writes:
> > for any singular update that this rule intercepts, OLD.id is for
> > all practical purposes a constant, right?
>
> Uh, no, far from it. What you actually get is a query that is rewritten
> to include the source tables and WHERE clauses of whatever query
> triggered the rule, in such a way that its WHERE will succeed for every
> row that's about to be updated by the triggering query. Then the
> planner goes off and tries to find a reasonable plan for the whole mess
> (with varying degrees of success, of course).
>
> If you'd like the query to be fired separately for each updated row,
> with OLD.id actually a constant each time, then put it in a trigger
> instead of using a rule. When you're using a rule, you get something
> that's more like a join, with all the rule effects implied by all the
> updates done by a given query executed "in parallel" in a single query.
>
> The performance tradeoffs between using triggers and using rules are
> more than I want to try to wrap my brain around at seven PM on a Friday.
> It would depend a lot on both the rule and the queries it gets applied
> to. You might be best advised to try it both ways and see what wins.
>
> Oh, if you want to see the plan generated for a rule query: EXPLAIN
> a query that fires the rule. You'll see one plan for each rule step
> plus one for the triggering query.

that explains a lot of that explain output. :)

> > and is there any trick to inserting the OLD.id into the subquery
> > in a rule such as this?
>
> That ought to work, but since you're complaining I suppose it doesn't :-(
> It's too late to worry about this for 7.2 but I'll put it on my TODO for
> 7.3.

it may be moot if i can get triggers to sink in under my scalp.
grateful for the pointers! thanks, tom...

--
DEBIAN NEWBIE TIP #61 from Hamma Scott <scott_hamma(at)yahoo(dot)com>
:
Ever have troubles with EITHER X OR CONSOLE LOCKUP? If your
session is hung you can type <CTRL><ALT>F2-F6 to get to another
login session. This way, you can shut your machine down
properly, or kill whichever process is causing trouble (use "ps
axf" to see them all).

Also see http://newbieDoc.sourceForge.net/ ...