Problem with a rule on upgrade to v7.1.1 - Mailing list pgsql-hackers

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_childAS 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 LaphamExtracta Moléculas Naturais, Rio de
Janeiro,Brasilemail: lapham@extracta.com.br      web: http://www.extracta.com.br/
 
***-*--*----*-------*------------*--------------------*---------------