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 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 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
