Re: [SQL] Simple delete takes hours
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" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Hi there, > > I have a simple database: > > CREATE TABLE pwd_description ( > id SERIALNOT NULL UNIQUE PRIMARY KEY, > name varchar(50) NOT NULL > ); > > CREATE TABLE pwd_name ( > id SERIALNOT 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 SERIALNOT 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 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Simple delete takes hours
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 Scan on 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: 3.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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Simple delete takes hours
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 SERIALNOT NULL UNIQUE PRIMARY KEY, name varchar(50) NOT NULL ); CREATE TABLE pwd_name ( id SERIALNOT 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 SERIALNOT 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]
Re: [SQL] Simple delete takes hours
On Thu, 3 Mar 2005, Thomas Mueller wrote: > Hi there, > > I have a simple database: > > CREATE TABLE pwd_description ( >id SERIALNOT NULL UNIQUE PRIMARY KEY, >name varchar(50) NOT NULL > ); > > CREATE TABLE pwd_name ( >id SERIALNOT 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 SERIALNOT 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. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Simple delete takes hours
Hi there, I have a simple database: CREATE TABLE pwd_description ( id SERIALNOT NULL UNIQUE PRIMARY KEY, name varchar(50) NOT NULL ); CREATE TABLE pwd_name ( id SERIALNOT 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 SERIALNOT 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
