Thread: Simple delete takes hours
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 REFERENCESpwd_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 REFERENCESpwd_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
On Thu, 3 Mar 2005, Thomas Mueller 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. It's going to remove rows in pwd_name_rev based on the rev_of not description (and you really should make sure to have an index on rev_of). Without being able to see triggers and rules on the tables, I can't tell if it's even legal to remove the rows with description=1 from pwd_name_rev, but it isn't with just the constraints defined above.
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 <news-exp-jul05@tmueller.com> 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 >
On 05.03.2005 00:24 PFC wrote: > 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... Yes that's it, thanks a lot! pwdcheck=# explain analyze delete from pwd_name where description=1; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------Seq Scanon pwd_name (cost=0.00..116571.15 rows=1774250 width=6) (actual time=9526.671..21957.920 rows=543348 loops=1) Filter: (description = 1)Total runtime: 35555.749 ms (3 rows) Is it possible to get fired triggers/called stored procedures and things like that in an 'explain' ? To find out why the delete is that slow I did: pwdcheck=# explain analyze delete from pwd_name where id in pwdcheck-# (select id from pwd_name where description=1 limit 10); There was no hint that every deleted row leads to a full table scan. Thomas
Another way to speed it up is to use bind variables. It sped my deletes up by a factor of 280/1. -- Lynwood "Thomas Mueller" <news-exp-jul05@tmueller.com> wrote in message news:d0807h$vuu$1@sea.gmane.org... > 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 >