Re: using EXPLAIN in postgresql RULES?

will trillich <will@serensoft.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.

> 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.

            regards, tom lane