Every time a row is removed from pwd_name, the ON DELETE CASCADE trigger will look in pwd_name_rev if there is a row to delete... Does it have an index on pwd_name_rev( rev_of ) ? If not you'll get a full table scan for every row deleted in pwd_name...
On Thu, 03 Mar 2005 22:44:58 +0100, Thomas Mueller <[EMAIL PROTECTED]> wrote:
Hi there,
I have a simple database:
CREATE TABLE pwd_description ( id SERIAL NOT NULL UNIQUE PRIMARY KEY, name varchar(50) NOT NULL );
CREATE TABLE pwd_name ( id SERIAL NOT NULL UNIQUE PRIMARY KEY, description integer NOT NULL REFERENCES pwd_description(id), name varchar(50) NOT NULL, added timestamp DEFAULT now() );
CREATE TABLE pwd_name_rev (
id SERIAL NOT NULL UNIQUE PRIMARY KEY,
description integer NOT NULL REFERENCES pwd_description(id),
rev_of integer NOT NULL REFERENCES pwd_name(id) ON DELETE CASCADE,
name varchar(50) NOT NULL
);
The indexes shouldn't matter I think.
pwd_name_rev is filled by a stored procedure and a trigger (ON INSERT) when something is inserted to pwd_name. Both tables contain about 4.500.000 emtries each.
I stopped 'delete from pwd_name where description=1' after about 8 hours (!). The query should delete about 500.000 records.
Then I tried 'delete from pwd_name_rev where description=1' - this took 23 seconds (!).
Then I retried the delete on pwd_name but it's running for 6 hours now.
I use PostgreSQL 7.4.7 on Linux 2.6.10. The machine is a Celeron 2 GHz with 512 MB RAM.
PostgreSQL should do a full table scan I think, get all records with description=1 and remove them - I don't understand what's happening for >8 hours.
Any help is appreciated.
Thomas
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
