Thread: using EXPLAIN in postgresql RULES?
in a previous episode (see 'caching subtotals' thread) i figured
out what i was doing wrong in my 'calc subtotals for a table on
update to its view' rule. tom patiently rolled his eyes enough
that i finally caught on to my goofs...
now i'm wondering how to use EXPLAIN to optimize rules that rely
heavily on NEW.* and OLD.* items.
CREATE RULE acct_edit AS
ON UPDATE TO acct
DO INSTEAD (
UPDATE _acct SET
code = NEW.code,
charge = p.charge,
cost = p.cost
FROM (
SELECT
sum(charge) AS charge,
sum(cost ) AS cost,
acct_id
FROM
_prop
-- WHERE
-- acct_id = OLD.id -- can't see *OLD* record here
GROUP BY
acct_id
) p
WHERE
id = OLD.id
AND
p.acct_id = OLD.id;
);
for any singular update that this rule intercepts, OLD.id is for
all practical purposes a constant, right? is that the best way
to filter sql code through EXPLAIN -- as if NEW.* and OLD.* are
constants?
and is there any trick to inserting the OLD.id into the subquery
in a rule such as this? it sure helps, according to EXPLAIN:
EXPLAIN
UPDATE _acct SET
charge = p.charge,
cost = p.cost
FROM (
SELECT
sum(charge) AS charge,
sum(cost ) AS cost,
acct_id
FROM
_prop
GROUP BY
acct_id
) p
WHERE
id = p.acct_id;
Nested Loop (cost=1.17..9.48 rows=10 width=50)
-> Subquery Scan ppp (cost=1.17..1.22 rows=1 width=28)
-> Aggregate (cost=1.17..1.22 rows=1 width=28)
-> Group (cost=1.17..1.19 rows=7 width=28)
-> Sort (cost=1.17..1.17 rows=7 width=28)
-> Seq Scan on _prop (cost=0.00..1.07 rows=7 width=28)
-> Index Scan using _acct_pkey on _acct (cost=0.00..8.14 rows=10 width=22)
now, with constants added, to simulate the OLD.* fields:
EXPLAIN
UPDATE _acct SET
charge = p.charge,
cost = p.cost
FROM (
SELECT
sum(charge) AS charge,
sum(cost ) AS cost,
acct_id
FROM
_prop
where acct_id = 3 -- *******
GROUP BY
acct_id
) p
WHERE
id = 3 and -- *******
id = p.acct_id;
Nested Loop (cost=1.10..3.14 rows=1 width=50)
-> Subquery Scan p (cost=1.10..1.10 rows=1 width=28)
-> Aggregate (cost=1.10..1.10 rows=1 width=28)
-> Group (cost=1.10..1.10 rows=1 width=28)
-> Sort (cost=1.10..1.10 rows=1 width=28)
-> Seq Scan on _prop (cost=0.00..1.09 rows=1 width=28)
-> Index Scan using _acct_pkey on _acct (cost=0.00..2.02 rows=1 width=22)
is this the recommended paradigm for tweaking rules with OLD.* fields?
(and i've got an index on _prop.acct_id, so why's it doing a seq scan?
maybe i need more data in my sample...)
--
DEBIAN NEWBIE TIP #80 from USM Bish <bish@nde.vsnl.net.in>
:
Some common abbreviations used in lists:
IMHO = In My Humble Opinion IMO = In My Opinion
BTW = By The Way AFAIK = As Far As I Know
RTFM = Read The #$%&@! Manual IOW = In Other Words
HAND = Have A Nice Day YMMV = Your Mileage May Vary
My Bad = Sorry, my mistake HTH = Hope This Helps
Also see http://newbieDoc.sourceForge.net/ ...
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
On Fri, Jan 11, 2002 at 06:49:26PM -0500, Tom Lane wrote: > 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. 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@yahoo.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/ ...