Hello 2011/12/19 Daniel Migowski <dmigowski@xxxxxxxxxxx>: > Hi, > > > > I face the following problem: I have a large table with 12 million > addresses, referenced by 20 other tables (some containing about one million > entries). There are indexes on the foreign keys. > > > > Now I wanted to delete about 10 million addresses (that are not referenced > anymore from anywhere), and have a statement like: > you can disable check per session if you need ALTER TABLE ... DISABLE TRIGGER ALL; Regards Pavel Stehule > > > DELETE FROM address > > WHERE id NOT IN (SELECT address_id FROM bank where address_id IS NOT NULL) > > AND id NOT IN (SELECT poboxaddress_id FROM bank where poboxaddress_id IS > NOT NULL) > > AND id NOT IN (SELECT address_id FROM bankconnection where address_id IS > NOT NULL) > > ...lots more... > > > > This takes more than 10 hours here (I had to cancel the statement). > > > > I have two suggestions: > > > > 1. Currently for each row to be deleted, a SELECT is done in each > column referencing the deleted entry. This takes really a lot of time. It is > possible to check in an elegant way if an entry can be deleted, like in the > above query. I know it is not easy to autocreate such a statement, but this > would make deletions much faster. > > 2. I would have loved a special option “UNREREFENCED” given to the > delete statement, so all rows referenced from anywhere would automagically > be excluded from my delete statement. When this keyword is given, no FK > checks have to be done, because FK referenciality cannot be violated anyway. > > > > DELETE UNREFERENCED FROM address WHERE …; > > > > Thanks for your time and this great database product. > > > > Regards, > > Daniel Migowski -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
