From: | will trillich <will(at)serensoft(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | using EXPLAIN in postgresql RULES? |
Date: | 2002-01-11 21:47:46 |
Message-ID: | 20020111154746.B31598@serensoft.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)nde(dot)vsnl(dot)net(dot)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/ ...
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2002-01-11 21:57:25 | Re: retrieving a function? |
Previous Message | will trillich | 2002-01-11 21:30:13 | Re: caching subtotals: update vs sum -- aaugh! |