Many of the tables do not have indexes on the FK, though a couple of
the biggest ones do. It does seem worth the time to put an index on
each of these tables, considering the few hundred hours I'm already
spending on the DELETE.
I've started the EXPLAIN ANALYZE but it will take a while, no doubt.
In the meantime I'm going to play with the NOT EXISTS angle, its
something I hadn't considered.
On Thu, Feb 10, 2011 at 12:44 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Hmm ... do all of those referencing tables have indexes on the
> referencing columns? It seems plausible that the time is going into
> seqscan searches for referencing rows.
>
> You might try doing EXPLAIN ANALYZE of this same delete for a limited
> number of rows (maybe 1000 or so) so that you could see what plan you're
> getting and where the time really goes. I think 8.3 had the ability to
> break out time spent in triggers, so if the problem is the FK
> propagation, EXPLAIN ANALYZE would show it.
>
> Also, the NOT IN is probably going to suck performance-wise no matter
> what, for such large numbers of rows. Converting to NOT EXISTS might
> help some, though I don't remember right now how smart 8.3 is about
> either.
>
> regards, tom lane
>