Just did a sanity check. I dumped the DB schema, and there is indeed a foreign key reference into the table. Now interestingly, the table pointing in has no index on the column, but is a relatively small table with only entries near the end of the large table. So looks like I was getting CPU bound because of a sequental scan of the smaller table per delete. Dropped the constraint, and deletes are now much faster. Regards! Ed On Fri, 10 Jun 2005, David Gagnon wrote: > > >This table has 3 foreign keys, but that should not matter during deletes. > >In addition, the tables being referred to are small, and should be in > >cache. > > > > > I'm talking about FK that point this table... Not FK defined for this > table that point to other table. If Table A is referenced by 10 other > tables 10 referencial check are needed. > > > >There are no tables depending on it for references, so no dependent > >triggers should be running. > > > >Also, if this was a foreign key issue, I would expect I/O issues/bounds > >and not CPU. > > > > > Maybe... I'm honetly not sure. > > Like I said in my previous mail... I got a similar problem (maybe not > the same). It was taking 10 minutes to delete 10k line in a table. I > turn on some log info in postgresql.conf and I saw that for each row > deleted 4 selects were issued to check FK. I drop those FK and the > after the delete was taking less than a second. > > Hope it help > /David > > > > >Regards! > >Ed > > > > > >On Fri, 10 Jun 2005, Richard Huxton wrote: > > > > > > > >>Edmund Dengler wrote: > >> > >> > >>>Greetings! > >>> > >>>We have a table with more than 250 million rows. I am trying to delete the > >>>first 100,000 rows (based on a bigint primary key), and I had to cancel > >>>after 4 hours of the system not actually finishing the delete. I wrote a > >>>script to delete individual rows 10,000 at a time using transactions, and > >>>am finding each individual delete takes on the order of 0.1 seconds to 2-3 > >>>seconds. There are 4 indexes on the table, one of which is very "hashlike" > >>>(ie, distribution is throught the index for sequential rows). > >>> > >>> > >>I don't suppose it's off checking foreign-keys in a lot of tables is it? > >> > >>-- > >> Richard Huxton > >> Archonet Ltd > >> > >> > >> > > > >---------------------------(end of broadcast)--------------------------- > >TIP 3: if posting/reading through Usenet, please send an appropriate > > subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your > > message can get through to the mailing list cleanly > > > > > > > > ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
