Thread: Cannot Delete
Hi, hi have a table with 2.5 million records which i try do delete. i have several constraints on it too. i tried to delete the records using delete but it does not seem to work. the delete runs forever. hrs... i cannot truncate it as it complains about foreign keys. What is the problem ? Thanks Alex
Not sure about 2.5 million records but try running "VACUUM ANALYSE" before the delete and during (every now and then). Had the same problem with 100,000 records and it did the trick nicely. > Hi, > hi have a table with 2.5 million records which i try do delete. i have > several constraints on it too. > i tried to delete the records using delete but it does not seem to > work. the delete runs forever. hrs... > i cannot truncate it as it complains about foreign keys. > > What is the problem ? > > Thanks > Alex > > > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend > >
Alex <alex@meerkatsoft.com> writes: > hi have a table with 2.5 million records which i try do delete. i have > several constraints on it too. > i tried to delete the records using delete but it does not seem to work. > the delete runs forever. hrs... > i cannot truncate it as it complains about foreign keys. It's a good bet that you need to create indexes on the columns that reference this table via foreign keys. Without such indexes, updates and deletes on the referenced table will be really slow. regards, tom lane
I still have this problem... my table currently only has 60k records. it has two foreign keys . one to a table with 15 records the other to a table with 250 records both with a primary key as suggested. the table itself is referenced by another table. although I have about 10mio records in other tables , this one is rather small... still i manage to delete 2-3 records / second. I tried vacuum, the db and tables and did not gain any performance. I experience this problem on different machines and yet, when i reload the date it did speed up things considerably. Any suggestions what could be wrong? Maybe a configuration issue ? Thanks Alex Tom Lane wrote: >Alex <alex@meerkatsoft.com> writes: > >>hi have a table with 2.5 million records which i try do delete. i have >>several constraints on it too. >>i tried to delete the records using delete but it does not seem to work. >>the delete runs forever. hrs... >>i cannot truncate it as it complains about foreign keys. >> > >It's a good bet that you need to create indexes on the columns that >reference this table via foreign keys. Without such indexes, updates >and deletes on the referenced table will be really slow. > > regards, tom lane > > >
Alex wrote: > I still have this problem... > my table currently only has 60k records. it has two foreign keys . one > to a table with > 15 records the other to a table with 250 records both with a primary > key as suggested. > the table itself is referenced by another table. > > although I have about 10mio records in other tables , this one is > rather small... still i manage to delete 2-3 records / second. > I tried vacuum, the db and tables and did not gain any performance. > > I experience this problem on different machines and yet, when i reload > the date it did speed up things considerably. > > Any suggestions what could be wrong? Maybe a configuration issue ? HOW is this table referenced in another table? By both of the foreign keys, a separate integer substitute key in this table? BTW, what kind of keys are you using, string, integer, float,what? Please (re)post the table defintions to the 4 tables. -- "You are behaving like a man", is an insult from some women, a compliment from an good woman.
Alex wrote: > I tried vacuum, the db and tables and did not gain any performance. Did you try VACUUM only or VACUUM ANALYZE (or even just ANALYZE)? > I experience this problem on different machines and yet, when i reload > the date it did speed up things considerably. It does sound like you haven't ANALYZE'd your data. Try that. -- Linux homer 2.4.18-14 #1 Wed Sep 4 13:35:50 EDT 2002 i686 i686 i386 GNU/Linux 2:30pm up 291 days, 6:04, 9 users, load average: 6.08, 6.02, 6.01