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: [EMAIL PROTECTED] web: http://www.extracta.com.br/
***-*--*----*-------*------------*--------------------*---------------
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
