using EXPLAIN in postgresql RULES? - Mailing list 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@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/ ...