Problem with a rule on upgrade to v7.1.1

Hello all-

Yesterday I upgraded my database from Pg v7.1RC1 to v7.1.1. Since this
upgrade, I have been having unbelievable performance problems with updates
to a particular table, and I've tracked the problem down to a rule within
that table.

I've enclosed a simple case study at the end of this email (the real
example is basically the same, except that there are many more fields in
the tables). I will send the real table definitions if anyone thinks it
would be useful.

The problem is that in Pg v7.1RC1 (and previously with Pg v7.0.3) a simple
update to the child table, changing the boolean active='t' to active='f'
would be basically instantaneous. Now, it takes about an hour. The real
database has ~10000 records in total between the "child" and "parent"
tables.

Basically, the rule "r_inactivate_child" below is the problem. If I drop
that rule, everything runs fast again.

The idea of this rule is to set active='f' in the parent table whenever
all of the children (things in the child table) are inactive.

Any suggestions would be *greatly* appreciated! Thanks!

PS: Most likely the problem is in the design of the rule (I'm sure it
could be done better), but I would remind you that these same updates were
very, very fast in the older versions of Pg.

PSS: I'm running linux, kernel v2.4.4, RH7.1, homerolled PG.

-----------------------------------
Tables and rules:

CREATE TABLE parent (
parentid int4 PRIMARY KEY,
active boolean
);
CREATE TABLE child (
childid int4 PRIMARY KEY,
parentid int4 references parent(parentid),
active boolean
);

CREATE RULE r_inactivate_child
AS ON UPDATE TO child
WHERE NEW.active='f' AND OLD.active='t'
DO UPDATE parent SET active='f'
WHERE parentid=NEW.parentid

AND (SELECT count(*) FROM child
WHERE parentid=NEW.parentid AND
childid<>NEW.childid AND active='t') = 0;

CREATE RULE r_activate_child
AS ON UPDATE TO child
WHERE NEW.active='t' AND OLD.active='f'
DO UPDATE parent SET active='t'
WHERE parentid=NEW.parentid AND active='f';

-----------------------------------
Populate with data:
INSERT INTO parent (parentid, active) VALUES (1, 't');
INSERT INTO child (childid, parentid, active) VALUES (1, 1, 't');
INSERT INTO child (childid, parentid, active) VALUES (2, 1, 't');
INSERT INTO child (childid, parentid, active) VALUES (3, 1, 't');

(note, you will need *a lot* more data like this to see the slow
updates... but you get the idea, I hope).

-----------------------------------
Perform an update:
UPDATE child SET active='f' WHERE childid=2;

(this would take an hour on a ~8000 record child, ~3000 record parent
database)

-----------------------------------
Explain:

test=# explain update child set active='t' where childid=2;
NOTICE: QUERY PLAN:

Result (cost=0.00..30020.00 rows=1000000 width=10)
-> Nested Loop (cost=0.00..30020.00 rows=1000000 width=10)
-> Seq Scan on parent (cost=0.00..20.00 rows=1000 width=10)
-> Seq Scan on child (cost=0.00..20.00 rows=1000 width=0)

NOTICE: QUERY PLAN:

Nested Loop (cost=0.00..49.28 rows=25 width=14)
-> Index Scan using child_pkey on child (cost=0.00..8.16 rows=5
width=4)
-> Index Scan using parent_pkey on parent (cost=0.00..8.16 rows=5
width=10)

NOTICE: QUERY PLAN:

Index Scan using child_pkey on child (cost=0.00..8.14 rows=10 width=14)

--

-**-*-*---*-*---*-*---*-----*-*-----*---*-*---*-----*-----*-*-----*---
Jon Lapham
Extracta Moléculas Naturais, Rio de Janeiro, Brasil
email: lapham(at)extracta(dot)com(dot)br web: http://www.extracta.com.br/
***-*--*----*-------*------------*--------------------*---------------